Most developers use some form of auto-incrementing integer counter for a given database table, ensuring uniqueness among the rows. Several of the popular SQL dialects implement this facility. For instance, MySQL’s AUTO_INCREMENT attribute is used to provide a unique identity for a table row. What exactly is the behavior of AUTO_INCREMENT? Can you explicitly use a value of your choosing for it if you need to? How does it count? Continue reading and know the answers to these questions and more…
OS and DB used:
- Linux Mint 20 Ulyana
- MySQL 8.0.23
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
Credit Resources
I was inspired by and learned a great deal of information about AUTO_INCREMENT from the below YouTube video so be sure and check it out as well. My post here is supplemental for this solid resource.
AUTO_INCREMENT attribute: Starting value
For the examples in this post, we will use a simple ‘names’ table with 2 columns:
- An id of type
INTEGERhaving theAUTO_INCREMENTattribute. - A ‘first_name’ column of type
TEXT.
1 2 3 4 | mysql-sql [learning]> CREATE TABLE names (id INTEGER AUTO_INCREMENT, first_name TEXT, PRIMARY KEY(id)); Query OK, 0 rows affected (2.1297 sec) |
AUTO_INCREMENT initially starts with a value of 1 by default. However, you can control what starting value is used by setting it explicitly during table definition.)
I’ll INSERT a single row without specifying any value for the ‘id’ column:
1 2 3 | mysql-sql [learning]> INSERT INTO names (first_name) -> VALUES ('Josh'); Query OK, 1 row affected (0.1219 sec) |
Querying the table, we can see the ‘id’ column value is 1 for the row just INSERT‘ed:
1 2 3 4 5 6 7 | mysql-sql [learning]> SELECT * FROM names; +----+------------+ | id | first_name | +----+------------+ | 1 | Josh | +----+------------+ 1 row in set (0.0010 sec) |
Executing a similar query, but using a different ‘first_name’ value, we see that the AUTO_INCREMENT attribute does indeed provide an incrementing integer for this additional row:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql-sql [learning]> INSERT INTO names (first_name) VALUES ('Mary'); Query OK, 1 row affected (0.1647 sec) mysql-sql [learning]> SELECT * FROM names; +----+------------+ | id | first_name | +----+------------+ | 1 | Josh | | 2 | Mary | +----+------------+ 2 rows in set (0.0010 sec) |
AUTO_INCREMENT attribute: Specifying a value.
Can we provide a value in the INSERT statement for a column that has the AUTO_INCREMENT attribute? Are we allowed to? Or, will an error arise?
The next series of MySQL statements answer these questions…
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql-sql [learning]> INSERT INTO names(id, first_name) -> VALUES(10, 'Joseph'); Query OK, 1 row affected (0.1166 sec) mysql-sql [learning]> SELECT * FROM names; +----+------------+ | id | first_name | +----+------------+ | 1 | Josh | | 2 | Mary | | 10 | Joseph | +----+------------+ 3 rows in set (0.0010 sec) |
No error or warning was raised. And, we can see that the value of 10 provided for this row is in fact, present in the table. But, the question now is, what value will be used next if we forgo providing one ourselves?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql-sql [learning]> INSERT INTO names(first_name) -> VALUES('Whitney'); Query OK, 1 row affected (0.1747 sec) mysql-sql [learning]> SELECT * FROM names; +----+------------+ | id | first_name | +----+------------+ | 1 | Josh | | 2 | Mary | | 10 | Joseph | | 11 | Whitney | +----+------------+ 4 rows in set (0.0011 sec) |
Turns out, once you provide a value for the AUTO_INCREMENT column, the value henceforth continues incrementing from the user-supplied value.
AUTO_INCREMENT attribute: How does DELETE and TRUNCATE affect the value?
The following DELETE query clears all rows from the table:
1 2 | mysql-sql [learning]> DELETE FROM names; Query OK, 4 rows affected (0.1437 sec) |
INSERT a new row, without including a value for the ‘id’ column, allowing AUTO_INCREMENT to populate the column:
1 2 3 | mysql-sql [learning]> INSERT INTO names(first_name) -> VALUES('Robby'); Query OK, 1 row affected (0.0844 sec) |
1 2 3 4 5 6 7 | mysql-sql [learning]> SELECT * FROM names; +----+------------+ | id | first_name | +----+------------+ | 12 | Robby | +----+------------+ 1 row in set (0.0009 sec) |
Although we emptied the ‘names’ table with DELETE, AUTO_INCREMENT used the next available INTEGER value of 12 in the INSERT.
Executing TRUNCATE, we can then see any differences in what value AUTO_INCREMENT uses:
1 2 | mysql-sql [learning]> TRUNCATE TABLE names; Query OK, 0 rows affected (1.4996 sec) |
1 2 | mysql-sql [learning]> SELECT * FROM names; Empty set (0.0027 sec) |
Now that the ‘names’ table is empty, I’ll INSERT a new row, again omitting any value for the ‘id’ column:
1 2 3 | mysql-sql [learning]> INSERT INTO names(first_name) -> VALUES('Richard'); Query OK, 1 row affected (0.0948 sec) |
1 2 3 4 5 6 7 | mysql-sql [learning]> SELECT * FROM names; +----+------------+ | id | first_name | +----+------------+ | 1 | Richard | +----+------------+ 1 row in set (0.0010 sec) |
Based on the query results, we can see that TRUNCATE does reset the AUTO_INCREMENT attribute value, where DELETE does not.
See the official online AUTO_INCREMENT documentation for more information.
Recommended Reading
- MySQL Shell get_auto_increment_value() method – Python mode
- PHP PDO lastInsertId() method with examples in MySQL
- The AUTO_INCREMENT column attribute in MySQL – A beginning perspective.
My hope is that through this post and the linked YouTube video, you now have a better understanding of the AUTO_INCREMENT attribute. If you see any mistakes in the post or have questions about the code, leave a comment below, and thanks for reading!
Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to grow as a SQL and Back-end Web Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, walking, bass fishing, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
2 thoughts on “MySQL’s AUTO_INCREMENT attribute”