Stop MySQL Reusing AUTO_INCREMENT IDs
来源:互联网 发布:中文域名前景 编辑:程序博客网 时间:2024/06/05 08:20
问题:
I have a table with an AUTO_INCREMENT primary key. If the last row in the table is deleted, the next-inserted row will take the same ID.
Is there a way of getting MySQL to behave like t-SQL, and not reuse the ID? Then if the deleted row is erroneously referenced from something external to the database, no rows will be returned, highlighting the error.
回答:
In this case, you probably should not be using AUTO_INCREMENT indices in publicly accessible places.
Either derive a key field from other data, or use a different mechanism to create your id's. One way I've used before, although you need to be aware of the (potentially severe) performance implications, is a "keys" table to track the last-used key, and increment that.
That way, you can use any type of key you want, even non-numeric, and increment them using your own algorithm.
I have used 6-character alpha-numeric keys in the past:
CREATE TABLE `TableKeys` (
`table_name` VARCHAR(8) NOT NULL,
`last_key` VARCHAR(6) NOT NULL,
PRIMARY KEY (`table_name`)
);
SELECT * FROM `TableKeys`;
table_name | last_key
-----------+---------users | U00003A2articles | A000166Dproducts | P000009G
- Stop MySQL Reusing AUTO_INCREMENT IDs
- MySQL AUTO_INCREMENT
- mysql AUTO_INCREMENT
- MySql: AUTO_INCREMENT
- MySQL的AUTO_INCREMENT
- MySQL - 使用AUTO_INCREMENT
- MySQL的AUTO_INCREMENT
- MySQL的AUTO_INCREMENT
- MySQL的AUTO_INCREMENT
- mysql的auto_increment
- MySQL的AUTO_INCREMENT
- MySQL的AUTO_INCREMENT
- mysql auto_increment 认识
- mysql的auto_increment
- MySQL AUTO_INCREMENT 简介
- MySQL的AUTO_INCREMENT
- MySQL的AUTO_INCREMENT
- MySQL的AUTO_INCREMENT
- C++异常处理实例
- 智能法庭
- 线性表的链式存储
- 多线程编程之二——MFC中的多线程开发
- IIS应用程序发布的时候出现的问题
- Stop MySQL Reusing AUTO_INCREMENT IDs
- POJ 1204
- flex canvas焦点问题
- 通过编程方式获取backtrace(函数调用栈)(接上篇core dump调试解释)
- Data-Intensive Text Processing with MapReduce第三章(2)-MapReduce算法设计-3.1局部聚集
- FSEditLog记录
- .net学习二:页面和自定义控件
- GridView 数据行实现光棒效果
- 老男孩