{"id":601,"date":"2019-03-30T01:46:34","date_gmt":"2019-03-29T17:46:34","guid":{"rendered":"http:\/\/wordpress.cine.idv.tw\/?p=601"},"modified":"2021-01-05T00:34:27","modified_gmt":"2021-01-04T16:34:27","slug":"mysql-auto-increment-column-gap-1","status":"publish","type":"post","link":"https:\/\/wordpress.cine.idv.tw\/index.php\/2019\/03\/30\/mysql-auto-increment-column-gap-1\/","title":{"rendered":"MySQL AUTO_INCREMENT \u6703 “\u8df3\u865f” \u9996\u90e8\u66f2"},"content":{"rendered":"\n

\u5076\u723e\u958b\u767c\u4eba\u54e1\u6703\u88ab\u81ea\u52d5\u7de8\u865f\u6b04\u4f4d\u8df3\u865f\u7684\u73fe\u8c61\u8aa4\u5c0e, \u5c07\u5176\u89e3\u91cb\u70ba\u7a0b\u5f0f\u6709\u5beb\u5165\u8cc7\u6599\u5eab, \u4f46\u662f\u88ab\u8cc7\u6599\u5eab\u628a\u8cc7\u6599\u7d66\u641e\u4e1f\u4e86. (\u56e0\u70ba\u958b\u767c\u4eba\u54e1\u6c92\u770b\u5230\u7a0b\u5f0f\u57f7\u884c\u7684\u932f\u8aa4, \u4e5f\u4e0d\u8a8d\u70ba\u5beb\u5165\u8cc7\u6599\u5eab\u7684\u8cc7\u6599\u6709\u554f\u984c, \u66f4\u4e0d\u8a8d\u70ba\u7a0b\u5f0f\u5beb\u5165\u8cc7\u6599\u5eab\u7684\u908f\u8f2f\u6709\u554f\u984c.)<\/p>\n\n\n\n

\"\"
578326 \u9019\u7b46\u8cc7\u6599\u7a0b\u5f0f\u660e\u660e\u6709\u5beb\u5165, MySQL \u8cc7\u6599\u5eab\u600e\u9ebc\u628a\u8cc7\u6599\u7d66\u5beb\u4e1f\u4e86\u5462?<\/figcaption><\/figure>\n\n\n\n

\u5176\u5be6, \u95dc\u806f\u5f0f\u8cc7\u6599\u5eab\u5f15\u64ce\u767c\u5c55\u9019\u9ebc\u4e45\u4e86, \u8981\u5b83\u628a\u5beb\u5165\u7684\u8cc7\u6599 “\u641e\u4e1f”, \u61c9\u8a72\u7b97\u662f\u9084\u883b\u56f0\u96e3\u7684\u4e00\u4ef6\u4e8b. \u958b\u767c\u4eba\u54e1\u9032\u884c\u7a0b\u5f0f\u9664\u932f\u7684\u6642\u5019, \u62b1\u6301\u8457\u5343\u932f\u842c\u932f, \u7d55\u5c0d\u4e0d\u6703\u662f\u8cc7\u6599\u5eab\u5f15\u64ce\u7684\u932f, \u53ef\u80fd\u662f\u4e00\u500b\u9084\u4e0d\u932f\u7684\u539f\u5247!<\/p>\n\n\n\n

\u81ea\u52d5\u7de8\u865f\u6b04\u4f4d\u6703 “\u8df3\u865f”, \u4e5f\u4e0d\u662f MySQL \u7279\u6709\u7684\u884c\u70ba, Oracle \/ SQL Server \u4e5f\u90fd\u662f\u5982\u6b64.<\/p>\n\n\n\n

\u4ee5\u4e0b\u662f\u7528\u4f86\u5c55\u793a MySQL AUTO_INCREMENT \u6b04\u4f4d\u8df3\u865f\u73fe\u8c61\u7684\u7bc4\u4f8b SQL:<\/p>\n\n\n\n

USE MYDB;

DROP TEMPORARY TABLE IF EXISTS t1;
DROP TEMPORARY TABLE IF EXISTS l1;

CREATE TEMPORARY TABLE t1
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    f1 VARCHAR(50)  NOT NULL,
    PRIMARY KEY (id)
);

CREATE TEMPORARY TABLE l1
(
     message VARCHAR(255) NOT NULL
);

INSERT INTO t1 (f1) VALUES ('r1');
INSERT INTO l1 (message)
  SELECT CONCAT_WS(' ', 'In the beginning,', 't1 has only', (SELECT count(*) FROM t1), 'rows.')
    FROM DUAL;
 INSERT INTO l1 (message)
  SELECT CONCAT('This row is:', '(', (SELECT CONCAT_WS(', ', id, f1) FROM t1 ORDER BY id DESC LIMIT 1), ')')
    FROM DUAL;
 
START TRANSACTION;
  INSERT INTO t1 (f1) VALUES ('r2');
  INSERT INTO t1 (f1) VALUES ('r3');
ROLLBACK;

INSERT INTO l1 (message)
  SELECT CONCAT_WS(' ', 'After two rollbacked inserts,', 't1 still has', (SELECT count(*) FROM t1), 'row only.')
    FROM DUAL;
 INSERT INTO l1 (message)
   SELECT CONCAT_WS(' ', 'Next auto increment id will be: ', LAST_INSERT_ID() + 1)
     FROM DUAL;
 INSERT INTO t1 (f1) VALUES ('r4');
 INSERT INTO l1 (message)
   SELECT CONCAT_WS(' ', 'After 1 committed insert,', 't1 now has', (SELECT count(*) FROM t1), 'rows.')
     FROM DUAL;
 INSERT INTO l1 (message)
   SELECT CONCAT('The new row is: ', '(', (SELECT CONCAT_WS(', ', id, f1) FROM t1 ORDER BY id DESC LIMIT 1), ')')
     FROM DUAL;

SELECT * FROM l1;

DROP TEMPORARY TABLE IF EXISTS t1;
DROP TEMPORARY TABLE IF EXISTS l1;<\/pre>\n\n\n\n
\"\"
MySQL AUTO_INCREMENT \u6b04\u4f4d\u8df3\u865f\u7bc4\u4f8b SQL \u8f38\u51fa<\/figcaption><\/figure>\n\n\n\n
\u53c3\u8003\u8cc7\u6599<\/h4>\n\n\n\n