Mysql

auto_increment 值需要設置為 S1

  • August 1, 2014

嗨,我需要將表的自動增量設置為 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    |
...

引用自:https://dba.stackexchange.com/questions/72902