Bobcares

Auto Increment MariaDB | All About

by | Nov 25, 2022

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.

 

auto increment mariadbThere 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.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Speed issues driving customers away?
We’ve got your back!