Shulou(Shulou.com)05/31 Report--

This article mainly introduces "mysql how to add self-increasing attributes to fields". In daily operation, I believe many people have doubts about how mysql adds self-increasing attributes to fields. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts of "how mysql adds self-increasing attributes to fields". Next, please follow the editor to study!

In mysql, you can add a self-increment attribute to a field by adding a "AUTO_INCREMENT" attribute to the field, with the syntax "alter table table name add column field name data type AUTO_INCREMENT;".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Field self-growth is achieved by adding an AUTO_INCREMENT attribute to the field. The syntax format is as follows:

Field name data type AUTO_INCREMENT

By default, the initial value of AUTO_INCREMENT is 1, and the field value is automatically incremented by 1 for each new record.

Only one field in a table can use the AUTO_INCREMENT constraint, and the field must have a unique index to avoid duplicate ordinal numbers (that is, a primary key or part of a primary key).

The field of the AUTO_INCREMENT constraint must have the NOT NULL attribute.

Fields for AUTO_INCREMENT constraints can only be of integer types (TINYINT, SMALLINT, INT, BIGINT, and so on).

The maximum value of the AUTO_INCREMENT constraint field is constrained by the data type of the field, and if the upper limit is reached, the AUTO_INCREMENT will be invalidated.

Self-increasing fields are generally used in primary keys.

When the primary key is defined as self-growing, the value of the primary key no longer requires user input data, but is automatically assigned by the database system according to the definition. With each additional record, the primary key automatically grows in the same step.

Example:

For the existing mysql data table, you want to add a self-increasing field and set the initial value of the new data.

Actually, it's not complicated. It's just a memo.

Test table CREATE TABLE `tabc` (`name` varchar (20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8

Test data:

INSERT INTO `tabc` (`name`) VALUES ('mike'), (' tom'), ('jack'); add a self-increment field and set the starting value of the new data / * add a self-increment primary key field in two steps * / / * first add the self-increment field * / alter table t_abc add column id int auto_increment primary key / * after executing the above item, the field is increased, but the value is empty * / / * execute this one, it will automatically assign an initial value to the self-increment field of the existing data, starting from 1, and the subsequent new data will start at 10001. / alter table t_abc auto_increment=100; modifies the initial value of the existing data / * if we want all the data to start at 10001, we can do this * / alter table t_abc add column id int auto_increment primary key / * No value is specified here. After execution, only the initial value starting from 1 is assigned to the self-increment field. In fact, the implied setting of the current table self-increment field starts from 1 * / alter table t_abc auto_increment;/* increases all data by 100 in front of 10000*/update t_abc set id=id+10000;/*. We are arbitrarily specified. Now we should specify maxId+1 in the database as the starting value of the next data * / set @ maxId=1. Select max (id) into @ maxId from tweeabc; / * there are three pieces of data in the table, so maxId is now 10003*/select @ maxId+1 from dual; / * 10004 * / alter table t_abc auto_increment=10004; / * there is no direct reference to variables here, so manually move it over * / verify insert into t_abc (name) values ('Marry'); select * from t_abc order by id desc At this point, the study on "how mysql adds self-increasing attributes to fields" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.