Auto Increment MariaDB | All About

Let’s have a look at the Auto Increment in MariaDB. At Bobcares, with our Server Management Services, we can handle your server issues.

Auto Increment In MariaDB

This feature provides new rows with a distinct identity. When we insert a new record into the table and the auto-increment field is NULL or DEFAULT, the value is automatically incremented. It is also applicable to 0. If we enable the

NO_AUTO_VALUE_ON_ZERO SQL_MODECopy Code

, then it will not get applied to 0. By default, the value for the attribute starts from 1.

 

There can only be one column for this attribute in each table. It must be specified as a key, not necessarily the PRIMARY or UNIQUE key. If the key consists of multiple columns, the

AUTO_INCREMENTCopy Code

column must be the first column in some storage engines. For e.g., InnoDB. However, storage engines like Aria, MERGE, Spider, BLACKHOLE, and Federated allow the column to be moved elsewhere.

When we want MariaDB to automatically assign a sequence of numbers to a field, we use this property. Each column must be declared as either NULL or NOT NULL. If this parameter is not specified, the database will use NULL as the default.

The syntax for

AUTO_INCREMENTCopy Code

in MariaDB is as follows:

CREATE TABLE table_name
( 
column1 datatype NOT NULL AUTO_INCREMENT,
column2 datatype [ NULL | NOT NULL ],
...
);

Auto Increment In MariaDB – Example

CREATE TABLE flowers (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO flowers (name) VALUES
('rose'),('jasmine'),('lilly'),
('sunflower'),('orchid'),('tulip');

SELECT * FROM flowers;
+----+---------+
| id | name |
+----+---------+
| 1 | rose |
| 2 | jasmine |
| 3 | lilly |
| 4 | sunflower |
| 5 | orchid |
| 6 | tulip |
+----+---------+

How to change the AUTO_INCREMENT value?

We can use the

ALTER TABLECopy Code

to include a new value to the

AUTO_INCREMENTCopy Code

table option. We can also do this by setting the

insert_idCopy Code

server system variable to change the next

AUTO_INCREMENTCopy Code

value inserted by the current session. For e.g.,

ALTER TABLE flowers AUTO_INCREMENT=9;

INSERT INTO flowers (name) VALUES ('lotus');

SELECT * FROM flowers;
+----+-----------+
| id | name |
+----+-----------+
| 1 | rose |
| 2 | jasmine |
| 3 | lilly |
| 4 | sunflower |
| 5 | orchid |
| 6 | tulip |
| 9 | lotus |
+----+-----------+

SET insert_id=12;

INSERT INTO flowers (name) VALUES ('daisy');

SELECT * FROM flowers;
+----+-----------+
| id | name |
+----+-----------+
| 1 | rose |
| 2 | jasmine |
| 3 | lilly |
| 4 | sunflower |
| 5 | orchid |
| 6 | tulip |
| 9 | lotus |
| 12 | daisy |
+----+-----------+

[Looking for a solution to another query? We are just a click away.]

Conclusion

The article provides a detailed description of auto_increment MariaDB. We also included examples from our Support team to understand the feature better.