我在设计数据库的时候有遇到在将自动增长列删除后, 以后插入的时候该列值就不可以用了,如何设置可以重复利用该值呢?
(如果我将id为10 的行删掉,然后又插入一行,那么id就自动增长为 11了,如何让id 为10呢?)请教......
(如果我将id为10 的行删掉,然后又插入一行,那么id就自动增长为 11了,如何让id 为10呢?)请教......
解决方案 »
- mysql 建表 关联外键时 error 1005
- PowerDesigner做了个ORACLE数据模型 现在想把它改成MYSQL数据库的怎么改吗?
- 请教一个时间的计算方法
- 关于修改表的级联删除 的问题
- mysql 存储过程嵌套调用,在调用者中对被调用者返回的记录集进行处理,问题是如何再调用者中获得被调用者的记录集呢?临时表会不会速度太慢呢?
- 配置my.cnf 如何使查询速度更快
- 如何用一条update语句对整个表内所有记录的某个字段进行从1开始的递增填充???
- 多个版本MYSQL如何使系统正确选择的问题??????
- linux下wordpress站点mysql_connect() no such file...
- linux下,mysql无法启动,提示无法通过mysql.sock启动
- 往数据库中插入特殊字符
- 谁能帮我精简一下sql语句??
我试过,但是只是把替代以前删掉的哪一行啊,但是id没有还原啊.
刚刚引用错了...alter table Item auto_increment=1;
我试过,但是只是把替代以前删掉的哪一行啊,但是id没有还原啊.
Query OK, 0 rows affected (0.08 sec)mysql> insert into t_rickypc values (null,10);
Query OK, 1 row affected (0.03 sec)mysql> insert into t_rickypc values (null,20);
Query OK, 1 row affected (0.03 sec)mysql> insert into t_rickypc values (null,30);
Query OK, 1 row affected (0.02 sec)mysql> select * from t_rickypc;
+----+------+
| id | col |
+----+------+
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
+----+------+
3 rows in set (0.00 sec)mysql> delete from t_rickypc where id=3;
Query OK, 1 row affected (0.08 sec)mysql> select * from t_rickypc;
+----+------+
| id | col |
+----+------+
| 1 | 10 |
| 2 | 20 |
+----+------+
2 rows in set (0.00 sec)mysql> alter table t_rickypc auto_increment=3;
Query OK, 2 rows affected (0.19 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> insert into t_rickypc values (null,40);
Query OK, 1 row affected (0.03 sec)mysql> select * from t_rickypc;
+----+------+
| id | col |
+----+------+
| 1 | 10 |
| 2 | 20 |
| 3 | 40 |
+----+------+
3 rows in set (0.00 sec)mysql>
楼上的很详细了
set identity_insert A on
insert A(ID,Name) values(vid,vname)
set identity_insert A off请问mysql有没有这样的用法呢?
反正我试用过. 但是提示语法错误:Unknown system variable 'identity_insert'...
处理的列数很多的时候.可能都不知道上次什么时候删除的.但是我插入的时候肯定知道啊,那么上次的auto_increment 怎么来呢?
没看懂什么意思。last_insert_id() 可以得到刚插入记录的auto_incrementmysql> insert into t_rickypc values (null,50);
Query OK, 1 row affected (0.06 sec)mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 4 |
+------------------+
1 row in set (0.00 sec)mysql>
set identity_insert A on
insert A(ID,Name) values(vid,vname)
set identity_insert A offMYSQL中更简单,你可以直接指定,不需要去 set identity_insert A off[
mysql> select * from t_rickypc;
+----+------+
| id | col |
+----+------+
| 1 | 10 |
| 2 | 20 |
| 3 | 40 |
| 4 | 50 |
+----+------+
4 rows in set (0.00 sec)mysql> delete from t_rickypc where id=3;
Query OK, 1 row affected (0.06 sec)mysql> select * from t_rickypc;
+----+------+
| id | col |
+----+------+
| 1 | 10 |
| 2 | 20 |
| 4 | 50 |
+----+------+
3 rows in set (0.00 sec)mysql> insert into t_rickypc values (3,30);
Query OK, 1 row affected (0.08 sec)mysql> select * from t_rickypc;
+----+------+
| id | col |
+----+------+
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 50 |
+----+------+
4 rows in set (0.00 sec)mysql>
from xxx a
where not exists (select 1 from xxx where id=a.id+1)