Learning Mysql

mysql> INSERT INTO count VALUES (),(),(),(),(),();
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates 0 Warnings 0
mysql> SELECT * FROM count;
+---------+
| counter |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+---------+
6 rows inset sec)
Inserting several values works as expected. Now, let’s delete a few rows and then add six new rows:
mysql> DELETE FROM count WHERE counter > 4;
Query OK, 2 rows affected (0.00 sec)
mysql> INSERT INTO count VALUES (),(),(),(),(),();
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates 0 Warnings 0
mysql> SELECT * FROM count;
+---------+
| counter |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
+---------+
10 rows inset sec)
Here, we see that the counter is not reset, and continues from 7. If, however, we delete all the data in the table, the counter is reset to 1:
mysql> TRUNCATE TABLE count;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO count VALUES (),(),(),(),(),();
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates 0 Warnings 0
mysql> SELECT * FROM count;
Creating Tables | 211

+---------+
| counter |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+---------+
6 rows inset sec)
Instead of relying on MySQL to handle incrementing fields as you hope, you can manage the process in program code that you write to interact with the database. We don’t use an auto-incrementing field in the final music database specification, described fully in the next section. However, we douse one in our wedding gift registry in Chapter 15.

Download 4.24 Mb.

Share with your friends: