您好,登錄后才能下訂單哦!
本篇內(nèi)容介紹了“MySQL表怎么創(chuàng)建自增字段”的有關知識,在實際案例的操作過程中,不少人都會遇到這樣的困境,接下來就讓小編帶領大家學習一下如何處理這些情況吧!希望大家仔細閱讀,能夠?qū)W有所成!
注:如果使用新的自增互斥方式,對于replication應該避免使用INSERT ... ON DUPLICATE KEY UPDATE語句。
設置新自增互斥方式:通過配置選項:innodb_autoinc_lock_mode:調(diào)整鎖策略:
innodb_autoinc_lock_mode = 0 (“traditional” lock mode:全部使用表鎖)
innodb_autoinc_lock_mode = 1 (默認)(“consecutive” lock mode:可預判行數(shù)時使用新方式,不可時使用表鎖)
innodb_autoinc_lock_mode = 2 (“interleaved” lock mode:全部使用新方式,不安全,不適合replication)
##創(chuàng)建自增字段
方法1、創(chuàng)建:
mysql> create table c(id int auto_increment,name varchar(20),primary key(id));
Query OK, 0 rows affected (0.52 sec)
mysql> desc c;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)
方法2、修改:
mysql> create table cc (id int,name varchar(20));
Query OK, 0 rows affected (0.42 sec)
mysql> alter table cc change id id int primary key auto_increment;
Query OK, 0 rows affected (1.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc cc;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.11 sec)
mysql> insert into cc(id,name) values(1,'a'),(NULL,'b'),(NULL,'c'),(5,'d');
mysql> select * from cc;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 5 | d |
+----+------+
4 rows in set (0.00 sec)
注:只有int類型且為primary key 才可以使用auto_increment.
##對存在記錄的表的列修改為自增列
mysql> create table ccc (id int,name varchar(20));
Query OK, 0 rows affected (0.27 sec)
mysql> insert into ccc(id,name) values(1,'a'),(NULL,'b'),(NULL,'c'),(5,'d');
Query OK, 4 rows affected (0.53 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from ccc;
+------+------+
| id | name |
+------+------+
| 1 | a |
| NULL | b |
| NULL | c |
| 5 | d |
+------+------+
4 rows in set (0.00 sec)
mysql> alter table ccc change id id int primary key auto_increment;
Query OK, 4 rows affected (1.04 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc ccc;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> insert into ccc(id,name) values(1,'a'),(NULL,'b'),(NULL,'c'),(5,'d');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into ccc(id,name) values(6,'aa'),(NULL,'ab'),(NULL,'ac'),(10,'ad')
;
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from ccc;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 5 | d |
| 6 | aa |
| 7 | ab |
| 8 | ac |
| 10 | ad |
+----+------+
8 rows in set (0.00 sec)
mysql>
“MySQL表怎么創(chuàng)建自增字段”的內(nèi)容就介紹到這里了,感謝大家的閱讀。如果想了解更多行業(yè)相關的知識可以關注億速云網(wǎng)站,小編將為大家輸出更多高質(zhì)量的實用文章!
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權內(nèi)容。