主页 / dba / 问题 / 72902
Accepted
Karthick
Asked: 2014-08-01 23:37:23 +0800 CST

auto_increment 值需要设置为 S1

  • 772

嗨，我需要将表的自动增量设置为 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'
mysql mysql-5.5

1 个回答

  1. Best Answer

    自动增量值只能是数字（如果我没记错的话，整数和浮点类型）。

    如果要存储 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    |
...
    • 2

相关问题