现在有一个SQL文
INSRT INTO A SELECT B.ID, B.Name, NULL FROM BA的结构是ID,Name,INDEX。我想让数据插入的时候,INDEX自动从1000开始往上自增。
例:
ID NAME INDEX
1 NAME1 1000
3 NAME2 1001有没有什么好办法呢?
INSRT INTO A SELECT B.ID, B.Name, NULL FROM BA的结构是ID,Name,INDEX。我想让数据插入的时候,INDEX自动从1000开始往上自增。
例:
ID NAME INDEX
1 NAME1 1000
3 NAME2 1001有没有什么好办法呢?
id int,
name varchar(40),
`index` int primary key auto_increment
) auto_increment=1000;
select id,name,(select count(*) from b where id<=t.id) from t;
select id,name,999+(select count(*) from b where id<=t.id) from t;刚才少加了个 999
select id,name,999+(select count(*) from b where id<=t.id) from t;这样很慢啊index不是KEY,用不了auto_increment 的。
+------+-------+----------+
| id | name | @x:=@x+1 |
+------+-------+----------+
| 1 | NAME1 | 1 |
| 3 | NAME2 | 2 |
+------+-------+----------+
2 rows in set (0.00 sec)mysql> set @x=999;
Query OK, 0 rows affected (0.00 sec)mysql> insert into a
-> select id,name,@x:=@x+1 from b;
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> select * from a;
+------+-------+-------+
| id | name | index |
+------+-------+-------+
| 1 | NAME1 | 1000 |
| 3 | NAME2 | 1001 |
+------+-------+-------+
2 rows in set (0.00 sec)mysql>