Mysql
auto_increment 值需要設置為 S1
嗨,我需要將表的自動增量設置為 S1。我嘗試通過更改表並通過新表創建我知道如何設置 auto_increment 但是當我嘗試將自動增量設置為 S1 時我無法做到。因為我需要主鍵值應該是 S1,S2,S3 如下記錄。任何人都可以為此建議數據類型,
desc test_1; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | varchar(10) | NO | PRI | NULL | | | name | varchar(100) | NO | | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> ALTER TABLE test_1 AUTO_INCREMENT='S1'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''S1'' at line 1 mysql> create table tbl(ID varchar(100) not null primary key auto_increment,name varchar(100)) auto_increment='s1'; ERROR 1063 (42000): Incorrect column specifier for column 'ID' mysql> create table tbl(ID text not null primary key auto_increment,name varchar(100)) auto_increment='s1'; ERROR 1063 (42000): Incorrect column specifier for column 'ID' mysql> create table tbl(ID varchar(100) not null primary key auto_increment,name varchar(100)) auto_increment='s'; ERROR 1063 (42000): Incorrect column specifier for column 'ID'
自動增量值只能是數字(如果我沒記錯的話,整數和浮點類型)。
如果要儲存 S1, …., S1000,只需儲存一個整數,然後在選擇或客戶端返回額外的 S:
create table tbl(ID bigint not null primary key auto_increment,name varchar(100)) auto_increment=1; insert into tbl (name) values ('a'); ... select concat('S', id) as id, name from tbl; +----+------+ | id | name | +----+------+ | S1 | a | | S2 | a | | S3 | a | | S4 | a | ...