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_MODE, 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_INCREMENT 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_INCREMENT 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 TABLE to include a new value to the AUTO_INCREMENT table option. We can also do this by setting the insert_id server system variable to change the next AUTO_INCREMENT 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.