In this tutorial, we’ll learn about sequences and how we can create sequences in MySQL. In the end, we’ll see a few examples of MySQL sequences, from their creation to usage.
SQL sequences are ordered lists of numbers, typically in ascending order. One can create a sequence function to generate a sequence table or a sequence column. However in the MySQL database management system, the work is made easy, you can simply create a sequence by using AUTO_INCREMENT.
AUTO_INCREMENT can be used to generate numeric sequences as columns or a separate table altogether.
Syntax of AUTO_INCREMENT in MySQL:
//Using CREATE TABLE
CREATE TABLE tablename (
sequence_field INT NOT NULL AUTO_INCREMENT);
//When changing an existing column to AUTO_INCREMENT
ALTER TABLE tablename MODIFY fieldname INT AUTO_INCREMENT;Code language: SQL (Structured Query Language) (sql)Examples MySQL AUTO_INCREMENT
Let’s see how we can create MySQL AUTO_INCREMENT. Let’s create a simple table called names, we’ll be creating three fields in this table which are serial number, first name, and last name. The serial number field will be AUTO_INCREMENT and the other two will be varchar. Let’s have a look at the code for a better understanding:
CREATE TABLE names(
serial_number INT NOT NULL AUTO_INCREMENT,
fname VARCHAR(30),
lname VARCHAR(30)
PRIMARY KEY(serial_number)
);Code language: PHP (php)In the above example, you can see serial_number is also made a primary key. Generally, whenever we are using AUTO_INCREMENT we term it as a primary key, but it is not true for every table.
Let’s take another example, let’s create a simple table of only one column, named serial. But here, the only catch is that we want the series to start from 10 and not from 1. How can we do that? Let’s see with the help of code:
//First create the auto_inc table
CREATE TABLE auto_inc(
series INT NOT NULL AUTO_INCREMENT);
//By default AUTO_INCREMENT starts from 1
//ALTER TABLE is used to for setting the incremental value
ALTE TABLE auto_int AUTO_INCREMENT = 10;Code language: PHP (php)In the above example, we created an AUTO_INCREMENT column which will start from 10 and not 1. Remember, the default value of AUTO_INCREMENT is 1, therefore, we must alter the table after it’s created to change the initial value of AUTO_INCREMENT.
Now what if we want to drop the AUTO_INCREMENT property from a column, we can do that by using ALTER TABLE. Let’s see how we can do that, by going through the code:
ALTER TABLE auto_inc MODIFY series INT;Code language: SQL (Structured Query Language) (sql)In the above example, we wanted to drop or remove AUTO_INCREMENT from a table, we used the auto_inc table, where series is an AUTO_INCREMENT column. What we did in the example is that instead of using the DROP command we used ALTER TABLE and MODIFY command. We changed the series column datatype from INT AUTO_INCREMENT to INT only. This ensured, that now the column can only hold integer values and will not be able to increment the values automatically.
Summary
In this tutorial, we learned about MySQL sequence which in MySQL is nothing but AUTO_INCREMENT. Using AUTO_INCREMENT one can simply create a sequence (in ascending order) generating column.
We saw numerous types of examples, we first saw the creation of an AUTO_INCREMENT column and learned to incorporate it with other columns. The second example was to create an AUTO_INCREMENT column that starts from 10, and lastly, we saw the code to drop or remove AUTO_INCREMENT from a certain column.
This tutorial will definitely help you incorporate AUTO_INCREMENT into your tables, and help you get started with sequences.
See Also: The official documentation for an even detailed understanding of the subject.