-- 创建新表CREATETABLEnew_id_table(new_idINT(11)AUTO_INCREMENTPRIMARYKEY,old_idINT(11));-- 定义触发器DELIMITER//CREATETRIGGERinsert_old_id_triggerAFTERDELETEONyour_tableFOR EACH ROWBEGININSERTINTOnew_id_table(old_id)VALUES(OLD.id);END//DELIMITER;-- 定义存储过程DELIMITER//CREATEPROCEDUREreset_...
2.2 代码示例 -- 创建存储过程DELIMITER//CREATEPROCEDUREreset_auto_increment(INtable_nameVARCHAR(255),INauto_increment_columnVARCHAR(255))BEGINSET@sql=CONCAT('ALTER TABLE ',table_name,' AUTO_INCREMENT = 1');PREPAREstmtFROM@sql;EXECUTEstmt;DEALLOCATEPREPAREstmt;END//DELIMITER; 1. 2. 3. 4. 5. ...
Index_length: 2048 Data_free: 0 Auto_increment: 3 Create_time: 2008-11-05 12:48:01 Update_time: 2008-11-05 12:48:16 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.01 sec) mysql> alter table i auto_increment=0; Query OK, ...
Index_length 表索引文件的长度 - Data_free 表被整序后,但是未使用的字节的数目 - Auto_increment 表中下一个AUTO_INCREMENT值 - Create_time 表的创建时间 - Update_time 表的最后一次更新时间 - Check_time 表的最后一次检查的时间 - Collation 表的字符集 - Checksum 表的活性校验 - Create_options 表...
THe implication in your question is that the value has some inherent meaning other than simply its uniqueness - in which case you shouldn't be using AUTO_INCREMENT. Use "...SELECT COALESCE(MAX(id)+1,1)...", or something similar, instead. ...
(3) log_bin_index 不设置的话,会根据log_bin值名称自动生成mybinlog.index log_bin_index=var/lib/mysql/mybinlog.index (4) sql_log_bin 默认为ON 此变量用于标识当前会话中的操作是否会被记录于二进制日志,此变量值设置为ON,则表示在当前数据库连接中,对数据库进行修改的语句将会被记录到binlog中,此...
However, you can reset the number generated by MySQL to 3 using the following ALTER TABLE instruction: ALTER TABLE tmp AUTO_INCREMENT = 3; Now let’s try to insert a new line in the tmp table and request data from it to see the effect:...
(id) ) ENGINE InnoDB AUTO_INCREMENT 1024; CREATE INDEX idx_foo_key_name on foo(key_name); INSERT INTO foo (key_name) VALUES ('foo'); INSERT INTO foo (key_name) VALUES ('bar'); SELECT * FROM foo; When I run this through mysql < test.sql, it outputs: id key_name 2 bar 1 ...
服务器变量auto_increment_increment和auto_increment_offset可以协调多主服务器复制auto_increment列在多主服务器复制到从服务器的过程中会发生主键冲突问题,可以将不同的主服务器的这两个参数重新进行设置,将A库上设置auto_increment_increment=1,auto_increment_offset=1,此时B库上设置auto_increment_increment=1,auto...
Under what circumstances could the AUTO_INCREMENT reset? I just noticed that the several orders placed into our system have keys which are really old and already allocated, thus duplicating the records. Checking through the db, it seems that 3 tables were affected, in one of them AUTO_INCREMEN...