.jpg)
假如表中包含一列為auto_increment，
如果是Myisam類型的引擎，那么在刪除了最新一筆數(shù)據(jù)，無論是否重啟Mysql，下一次插入之后仍然會使用上次刪除的最大ID+1.
mysql>?create?table?test_myisam?(id?int?not?null?auto_increment?primary?key,?name?char(5))?engine=myisam; Query?OK,?0?rows?affected?(0.04?sec) ?? mysql>?insert?into?test_myisam?(name)?select?‘a(chǎn)‘; Query?OK,?1?row?affected?(0.00?sec) Records:?1?Duplicates:?0?Warnings:?0 ?? mysql>?insert?into?test_myisam?(name)?select?‘b‘; Query?OK,?1?row?affected?(0.00?sec) Records:?1?Duplicates:?0?Warnings:?0 ?? mysql>?insert?into?test_myisam?(name)?select?‘c‘; Query?OK,?1?row?affected?(0.00?sec) Records:?1?Duplicates:?0?Warnings:?0 ?? mysql>?insert?into?test_myisam?(name)?select?name?from?test_myisam; Query?OK,?3?rows?affected?(0.00?sec) Records:?3?Duplicates:?0?Warnings:?0 ?? mysql>?select?*?from?test_myisam; +----+------+ |?id?|?name?| +----+------+ |?1?|?a??| |?2?|?b??| |?3?|?c??| |?4?|?a??| |?5?|?b??| |?6?|?c??| +----+------+ 6?rows?in?set?(0.00?sec) ?? mysql>?delete?from?test_myisam?where?id=6; Query?OK,?1?row?affected?(0.00?sec)
? ?
mysql>?insert?into?test_myisam(name)?select?‘d‘; Query?OK,?1?row?affected?(0.00?sec) Records:?1?Duplicates:?0?Warnings:?0 ?? mysql>?select?*?from?test_myisam; +----+------+ |?id?|?name?| +----+------+ |?1?|?a??| |?2?|?b??| |?3?|?c??| |?4?|?a??| |?5?|?b??| |?7?|?d??| +----+------+ 6?rows?in?set?(0.00?sec)
下面是對Innodb表的測試。
mysql>?create?table?test_innodb(id?int?not?null?auto_increment?primary?key,?name?char(5))?engine=innodb; Query?OK,?0?rows?affected?(0.26?sec) ?? mysql>?insert?into?test_innodb?(name)select?‘a(chǎn)‘; Query?OK,?1?row?affected?(0.06?sec) Records:?1?Duplicates:?0?Warnings:?0 ?? mysql>?insert?into?test_innodb?(name)select?‘b‘; Query?OK,?1?row?affected?(0.06?sec) Records:?1?Duplicates:?0?Warnings:?0 ?? mysql>?insert?into?test_innodb?(name)select?‘c‘; Query?OK,?1?row?affected?(0.07?sec) Records:?1?Duplicates:?0?Warnings:?0 ?? mysql>?select?*?from?test_innodb; +----+------+ |?id?|?name?| +----+------+ |?1?|?a??| |?2?|?b??| |?3?|?c??| +----+------+ 3?rows?in?set?(0.00?sec) ?? mysql>?delete?from?test_innodb?where?id=3; Query?OK,?1?row?affected?(0.05?sec) ?? mysql>?insert?into?test_innodb?(name)select?‘d‘; Query?OK,?1?row?affected?(0.20?sec) Records:?1?Duplicates:?0?Warnings:?0 ?? mysql>?select?*?from?test_innodb; +----+------+ |?id?|?name?| +----+------+ |?1?|?a??| |?2?|?b??| |?4?|?d??| +----+------+ 3?rows?in?set?(0.00?sec) ?? mysql>?exit Bye [2@a?data]$?mysql?-uroot?-pwsdad Welcome?to?the?MySQL?monitor.?Commands?end?with?;?or?g. Your?MySQL?connection?id?is?5 Server?version:?5.5.37-log?Source?distribution ?? Copyright?(c)?2000,?2014,?Oracle?and/or?its?affiliates.?All?rights?reserved. ?? Oracle?is?a?registered?trademark?of?Oracle?Corporation?and/or?its affiliates.?Other?names?may?be?trademarks?of?their?respective owners. ?? Type?‘help;‘?or?‘h‘?for?help.?Type?‘c‘?to?clear?the?current?input?statement. ?? mysql>?use?wison Database?changed mysql>?delete?from?test_innodb?where?id=4; Query?OK,?1?row?affected?(0.07?sec) ?? mysql>?exit Bye [2@a?data]$?sudo?service?mysql?restart Shutting?down?MySQL...?SUCCESS! Starting?MySQL..?SUCCESS! [2@a?data]$?mysql?-uroot?-pwison Welcome?to?the?MySQL?monitor.?Commands?end?with?;?or?g. Your?MySQL?connection?id?is?1 Server?version:?5.5.37-log?Source?distribution ?? Copyright?(c)?2000,?2014,?Oracle?and/or?its?affiliates.?All?rights?reserved. ?? Oracle?is?a?registered?trademark?of?Oracle?Corporation?and/or?its affiliates.?Other?names?may?be?trademarks?of?their?respective owners. ?? Type?‘help;‘?or?‘h‘?for?help.?Type?‘c‘?to?clear?the?current?input?statement. ?? mysql>?use?wison Database?changed mysql>?insert?into?test_innodb?(name)?select?‘z‘; Query?OK,?1?row?affected?(0.07?sec) Records:?1?Duplicates:?0?Warnings:?0 ?? mysql>?select?*?from?test_innodb; +----+------+ |?id?|?name?| +----+------+ |?1?|?a??| |?2?|?b??| |?3?|?z??| +----+------+ 3?rows?in?set?(0.00?sec)
? ?
可以看到在mysql數(shù)據(jù)庫沒有重啟時，innodb的表新插入數(shù)據(jù)會是之前被刪除的數(shù)據(jù)再加1.
但是當(dāng)Mysql服務(wù)被重啟后，再向InnodB的自增表表里插入數(shù)據(jù)，那么會使用當(dāng)前Innodb表里的最大的自增列再加1.
原因：
Myisam類型存儲引擎的表將最大的ID值是記錄到數(shù)據(jù)文件中，不管是否重啟最大的ID值都不會丟失。但是InnoDB表的最大的ID值是存在內(nèi)存中的，若不重啟Mysql服務(wù)，新加入數(shù)據(jù)會使用內(nèi)存中最大的數(shù)據(jù)+1.但是重啟之后，會使用當(dāng)前表中最大的值再+1
感謝閱讀此文，以上就是Mysql中Identity 詳細(xì)介紹的內(nèi)容，希望能幫助到大家，更多相關(guān)文章請關(guān)注PHP中文網(wǎng)（www.php.cn）！
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載。
THE END
.png)
