mysql 資料庫自增id 的總結

有一個表StuInfo，裡面只有兩列 StuID，StuName其中StuID是int型，主鍵，自增列。現在我要插入資料，讓他自動的向上增長，insert into StuInfo(StuID,StuName) values(????) 如何寫？

INSERT INTO StuInfo(StuID,StuName) VALUES(NULL, `字元`)或者
INSERT INTO StuInfo(StuName) VALUES (`字元`)

 

INSERT和REPLACE語句的功能都是向表中插入新的資料。這兩條語句的語法類似。它們的主要區別是如何處理重複的資料。

1. INSERT的一般用法

MySQL中的INSERT語句和標準的INSERT不太一樣，在標準的SQL語句中，一次插入一條記錄的INSERT語句只有一種形式。

INSERT INTO tablename(列名…) VALUES(列值);

而在MySQL中還有另外一種形式。

INSERT INTO tablename SET column_name1 =value1, column_name2 = value2，…;

第一種方法將列名和列值分開了，在使用時，列名必須和列值的數一致。如下面的語句向users表中插入了一條記錄：

INSERT INTO users(id, name, age)VALUES(123, '姚明', 25);

第二種方法允許列名和列值成對出現和使用，如下面的語句將產生中樣的效果。

INSERT INTO users SET id = 123, name = '姚明', age = 25;

如果使用了SET方式，必須至少為一列賦值。如果某一個欄位使用了省缺值（如預設或自增值），這兩種方法都可以省略這些欄位。如id欄位上使用了自增值，上面兩條語句可以寫成如下形式：

INSERT INTO users (name, age) VALUES('姚明',25);

INSERT INTO uses SET name = '姚明', age = 25;

MySQL在VALUES上也做了些變化。如果VALUES中什麼都不寫，那MySQL將使用表中每一列的預設值來插入新記錄。

INSERT INTO users () VALUES();

如果表名後什麼都不寫，就表示向表中所有的欄位賦值。使用這種方式，不僅在VALUES中的值要和列數一致，而且順序不能顛倒。 INSERT INTO users VALUES(123, '姚明', 25);

如果將INSERT語句寫成如下形式MySQL將會報錯。

INSERT INTO users VALUES('姚明',25);

2. 使用INSERT插入多條記錄

看到這個標題也許大家會問，這有什麼好說的，呼叫多次INSERT語句不就可以插入多條記錄了嗎！但使用這種方法要增加伺服器的負荷，因為，執行每一次SQL伺服器都要同樣對SQL進行分析、優化等操作。幸好MySQL提供了另一種解決方案，就是使用一條INSERT語句來插入多條記錄。這並不是標準的SQL語法，因此只能在MySQL中使用。

INSERT INTO users(name, age)

VALUES('姚明', 25), ('比爾.蓋茨', 50), ('火星人', 600);

上面的INSERT 語句向users表中連續插入了3條記錄。值得注意的是，上面的INSERT語句中的VALUES後必須每一條記錄的值放到一對(…)中，中間使用","分割。假設有一個表table1

CREATE TABLE table1(n INT)；

如果要向table1中插入5條記錄，下面寫法是錯誤的：

INSERT INTO table1 (i) VALUES(1,2,3,4,5);

MySQL將會丟擲下面的錯誤

ERROR 1136: Column count doesn't matchvalue count at row 1

而正確的寫法應該是這樣：

INSERT INTO t able1(i)VALUES(1),(2),(3),(4),(5);

當然，這種寫法也可以省略列名，這樣每一對括號裡的值的數目必須一致，而且這個數目必須和列數一致。如：

INSERT INTO t able1VALUES(1),(2),(3),(4),(5);

3. REPLACE語句

我們在使用資料庫時可能會經常遇到這種情況。如果一個表在一個欄位上建立了唯一索引，當我們再向這個表中使用已經存在的鍵值插入一條記錄，那將會丟擲一個主鍵衝突的錯誤。當然，我們可能想用新記錄的值來覆蓋原來的記錄值。如果使用傳統的做法，必須先使用DELETE語句刪除原先的記錄，然後再使用INSERT插入新的記錄。而在MySQL中為我們提供了一種新的解決方案，這就是REPLACE語句。使用REPLACE插入一條記錄時，如果不重複，REPLACE就和 INSERT的功能一樣，如果有重複記錄，REPLACE就使用新記錄的值來替換原來的記錄值。

使用REPLACE的最大好處就是可以將DELETE和INSERT合二為一，形成一個原子操作。這樣就可以不必考慮在同時使用DELETE和INSERT時新增事務等複雜操作了。

在使用REPLACE時，表中必須有唯一索引，而且這個索引所在的欄位不能允許空值，否則REPLACE就和INSERT完全一樣的。

在執行REPLACE後，系統返回了所影響的行數，如果返回1，說明在表中並沒有重複的記錄，如果返回2，說明有一條重複記錄，系統自動先呼叫了DELETE刪除這條記錄，然後再記錄用INSERT來插入這條記錄。如果返回的值大於2，那說明有多個唯一索引，有多條記錄被刪除和插入。

REPLACE的語法和INSERT非常的相似，如下面的REPLACE語句是插入或更新一條記錄。

REPLACE INTO users (id,name,age)VALUES(123, '趙本山', 50);

插入多條記錄：

REPLACE INTO users(id, name, age)

VALUES(123, '趙本山', 50), (134,'Mary',15);

REPLACE也可以使用SET語句

REPLACE INTO users SET id = 123, name = '趙本山', age = 50;

上面曾提到REPLACE可能影響3條以上的記錄，這是因為在表中有超過一個的唯一索引。在這種情況下，REPLACE將考慮每一個唯一索引，並對每一個索引對應的重複記錄都刪除，然後插入這條新記錄。假設有一個table1表，有3個欄位a, b, c。它們都有一個唯一索引。

CREATE TABLE table1(a INT NOT NULL UNIQUE,bINT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);

假設table1中已經有了3條記錄

a b c

1 1 1

2 2 2

3 3 3

下面我們使用REPLACE語句向table1中插入一條記錄。

REPLACE INTO table1(a, b, c) VALUES(1,2,3);

返回的結果如下

Query OK, 4 rows affected (0.00 sec)

在table1中的記錄如下

a b c

1 2 3

我們可以看到，REPLACE將原先的3條記錄都刪除了，然後將（1, 2, 3）插入。

二、UPDATE

UPDATE的功能是更新表中的資料。這的語法和INSERT的第二種用法相似。必須提供表名以及SET表示式，在後面可以加WHERE以限制更新的記錄範圍。

UPDATE table_anem SET column_name1 =value1, column_name2 = value2, ...

WHERE ... ;

如下面的語句將users表中id等於123的記錄的age改為24

UPDATE users SET age = 24 WHERE id = 123;

同樣，可以使用UPDATE更新多個欄位的值 UPDATE users SETage = 24, name = 'Mike' WHERE id = 123;

上面的UPDATE語句通過WHERE指定一個條件，否則，UPDATE將更新表中的所有記錄的值。

在使用UPDATE更新記錄時，如果被更新的欄位的型別和所賦的值不匹配時，MySQL將這個值轉換為相應型別的值。如果這個欄位是數值型別，而且所賦值超過了這個資料型別的最大範圍，那麼MySQL就將這個值轉換為這個範圍最大或最小值。如果字串太長，MySQL就將多餘的字串截去。如果設定非空欄位為空，那麼將這個欄位設定為它們的預設值，數字的預設值是0，字串的預設值是空串（不是 null，是""）。

有兩種情況UPDATE不會對影響表中的資料。

1. 當WHERE中的條件在表中沒有記錄和它匹配時。

2. 當我們將同樣的值賦給某個欄位時，如將欄位abc賦為'123'，而abc的原值就是'123'。

和INSERT、REPLACE一樣，UPDATE也返回所更新的記錄數。但這些記錄數並不包括滿足WHERE條件的，但卻未被更新的記錄。如下同的UPDATE語句就未更新任何記錄。

UPDATE users SET age = 30 WHERE id = 12;

Query OK, 0 rows affected (0.00 sec)

需要注意的時，如果一個欄位的型別是TIMESTAMP，那麼這個欄位在其它欄位更新時自動更新。

在有些時候我們需要得到UPDATE所選擇的行數，而不是被更新的行數。我們可以通過一些API來達到這個目的。如MySQL提供的C API提供了一個選項可以得到你想要的記錄數。而MySQL的JDBC驅動得到的預設記錄數也是匹配的記錄數。

UPDATE和REPLACE基本類似，但是它們之間有兩點不同。

1. UPDATE在沒有匹配記錄時什麼都不做，而REPLACE在有重複記錄時更新，在沒有重複記錄時插入。

2. UPDATE可以選擇性地更新記錄的一部分欄位。而REPLACE在發現有重複記錄時就將這條記錄徹底刪除，再插入新的記錄。也就是說，將所有的欄位都更新了。

三、DELETE和TRUNCATE TABLE

在MySQL中有兩種方法可以刪除資料，一種是DELETE語句，另一種是TRUNCATE TABLE語句。DELETE語句可以通過WHERE對要刪除的記錄進行選擇。而使用TRUNCATE TABLE將刪除表中的所有記錄。因此，DELETE語句更靈活。

如果要清空表中的所有記錄，可以使用下面的兩種方法：

DELETE FROM table1

TRUNCATE TABLE table1

其中第二條記錄中的TABLE是可選的。

如果要刪除表中的部分記錄，只能使用DELETE語句。

DELETE FROM table1 WHERE ...;

如果DELETE不加WHERE子句，那麼它和TRUNCATE TABLE是一樣的，但它們有一點不同，那就是DELETE可以返回被刪除的記錄數，而TRUNCATE TABLE返回的是0。

如果一個表中有自增欄位，使用TRUNCATE TABLE和沒有WHERE子句的DELETE刪除所有記錄後，這個自增欄位將起始值恢復成1.如果你不想這樣做的話，可以在DELETE語句中加上永真的WHERE，如WHERE 1或WHERE true。

DELETE FROM table1 WHERE 1;

上面的語句在執行時將掃描每一條記錄。但它並不比較，因為這個WHERE條件永遠為true。這樣做雖然可以保持自增的最大值，但由於它是掃描了所有的記錄，因此，它的執行成本要比沒有WHERE子句的DELETE大得多。

DELETE和TRUNCATE TABLE的最大區別是DELETE可以通過WHERE語句選擇要刪除的記錄。但執行得速度不快。而且還可以返回被刪除的記錄數。而TRUNCATE TABLE無法刪除指定的記錄，而且不能返回被刪除的記錄。但它執行得非常快。

和標準的SQL語句不同，DELETE支援ORDER BY和LIMIT子句，通過這兩個子句，我們可以更好地控制要刪除的記錄。如當我們只想刪除WHERE子句過濾出來的記錄的一部分，可以使用LIMIB，如果要刪除後幾條記錄，可以通過ORDER BY和LIMIT配合使用。假設我們要刪除users表中name等於"Mike"的前6條記錄。可以使用如下的DELETE語句：

DELETE FROM users WHERE name = 'Mike' LIMIT6;

一般MySQL並不確定刪除的這6條記錄是哪6條，為了更保險，我們可以使用ORDER BY對記錄進行排序。

DELETE FROM users WHERE name = 'Mike' ORDERBY id DESC LIMIT 6;

將B表查詢的資料插入A表：

insert into A select 。。。from B where。。。

or

insert into A (。。。）select 。。。from B where。。。

將B表的值更新到A表：

update A set a = (select b from B whereB.id = A.id) where exists(select 1 from B where id = A.id) and 。。。

同時刪除多表資料：

delete A,B from A,B where A.id = B.id and 。。。

or

delete from A,B using A,B where A.id = B.idand 。。。

 

 

1.建表時就建立自增列：

create table test
(
 id int auto_increment primary key,
 name varchar(20) not null,
 password varchar(20) not null
);

insert into test values(null,'aa','aa');
insert into test values(null,'bb','bb');

 

注意：

      插入語句時，自增列的值為NULL。

 

2、建立表格後新增： alter table table1 add id int auto_increment primary key自增欄位，一定要設定為primary key. 

 

 

注:只有int型別且為primary key 才可以使用auto_increment.

如果用hibernate作為持久層,那麼主鍵ID處用identity型別.

對於一個表主鍵的選擇，從架構設計角度來說，儘量設計一個與業務無關的欄位來作為主鍵會對系統更好
當然，這勢必會增加表資料量的大小，可能有些人會認為這個副作用不值得而選擇一個有業務意義的欄位（或者多個欄位）來作為主鍵

但是對於大多數時候，這個欄位所增加的物理儲存空間實際上是很小的，並沒有很多人想象的那麼可怕。而帶來邏輯上的簡單是很可觀的。

另外，是否選擇自增型別作為主鍵，這個可以說是各有好處，如果用自增，應用可以不care這個主鍵的值得設定，但是有在高併發下成為瓶頸的風險，當然如果併發並不是非常非常高的話，一般不會成為瓶頸。如果自己控制這個主鍵值的話，更需要付出一點點代價來生成這個值，併發問題可以通過擴充套件應用叢集來解決。

 

2種解決辦法 

1.修改你的資料庫表，將ID設為自增長，注：只有主鍵才可以設定為自增長 
例：CREATE TABLE`message ` (`id` INT(8) NOT NULL AUTO_INCREMENT, PRIMARY KEY(`id`)); 

2.程式碼中進行處理 
Statement ps; 
ResultSet rs; 
int id = 0; 
.................. 
String sql="select max(id) as id from message"; 
rs = ps.executeQuery(sql);//查詢出最大ID 
if(rs.next){//如果有ID，原有ID+1 
id = rs.getInt("id")+1; 
}else{//否則 ID 初始為0 
id = 0; 
} 
//下面接你寫的程式碼就OK了 
PreparedStatement ps; 
String sql="insert message (id,name,msg) value(?,?,?)" 

ps.setString(1,id); 
ps.setString(2,name); 
ps.setString(3,msg);

mysql 資料庫自增id 的總結

部落格分類：

mysql

mysqlauto_increment

mysql資料庫使用auto_increment 欄位來輔助為自增列賦值。

 

SHOW VARIABLES LIKE 'auto_incre%';

 

 輸出

 

auto_increment_increment 1 
auto_increment_offset 1

 

auto_increment_increment  = 1 ，每次插入資料，id++；auto_increment_offset=1 ，說明從1開始。

一般情況下auto_increment_increment=1,auto_increment_offset=1.是預設初始值。

我們可以在my.cnf檔案中進行重新指定。auto_increment滿足一個數學當納法推導公

式:

value=auto_increment_increment*N+auto_increment_offset

 

表中有auto_increment列，innodb用一種鎖策略來保正這個auto_increment列值。

如果我們建立一個表，指定了auto_increment表。那麼innodb會維護一個auto_increment

計數器。

 

當訪問這個計數器，innodb會使用表級鎖來鎖定這訪問過程，這個發生在分析auto_increment

值的過程中，而不是在一個執行事務中。也就是說：這個訪問發生在事務執行前面。所以和這個鎖

不是發生整個事務過程中，僅僅是分析語句判斷出auto_increment這一過程中.

 

auto_increment計數器，一直維護在記憶體中，當server重啟或是停止後重啟，innodb會為每個表

初始化這個計數器.

 

 

 

對一個含有自增列（通常為id）的表執行

 

mysql> SHOW CREATE TABLE table_name;

 

 可以得到類似輸出：

 

CREATE TABLE `news` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`url` varchar(255) NOT NULL,
`pub_date` datetime NOT NULL,
`site` tinyint(4) NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime DEFAULT NULL,
`org` varchar(255) DEFAULT NULL,
`author` varchar(255) DEFAULT NULL,
`trend` tinyint(4) DEFAULT '-1',
`md5url` char(32) NOT NULL,
`content` longtext NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `md5url` (`md5url`),
KEY `pub_date` (`pub_date`),
KEY `created_at` (`created_at`),
KEY `title` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=704677 DEFAULT CHARSET=utf8

 

 

其中該狀態下，表中AUTO_INCREMENT 欄位值為704677 ，說明下一個插入的id 為704677 ，同時插入成功後，AUTO_INCREMENT=704678.

 

mysql 資料庫中的id設為自增，容易產生id不連續的問題。有時將一個表中的所有資料清除，但是在插入資料時，id還是在未清除前的基礎上累加。

要解決的問題：

1. 刪除資料，同時要求再次插入資料時，id從1 開始累計

（1） TRUNCATE TABLE table_name;

（2） DELETE FROM table_name;   

 ALTER TABLEtable_name auto_increment =1 ;

對上面兩種方法進行一下分析。Truncate table 表名速度快，而且效率高，因為：TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均刪除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日誌資源少。DELETE 語句每次刪除一行，並在事務日誌中為所刪除的每行記錄一項。TRUNCATE TABLE 通過釋放儲存表資料所用的資料頁來刪除資料，並且只在事務日誌中記錄頁的釋放。

TRUNCATE TABLE 刪除表中的所有行，但表結構及其列、約束、索引等保持不變。新行標識所用的計數值重置為該列的種子。如果想保留標識計數值，請改用 DELETE。如果要刪除表定義及其資料，請使用 DROP TABLE 語句。

2. 檢視當前狀態下一個表的auto_increment值：

 

SHOW TABLE STATUS LIKE 'table_name'

輸出：

Name: ddx
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 2449
Avg_row_length: 93
Data_length: 229376
Max_data_length: 0
Index_length: 114688
Data_free: 0
Auto_increment: 2353
Create_time: 2012-05-02 12:54:55
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:

  可以檢視該表當前的Auto_increment的值。

 

3. 對有些失敗插入操作，如果不是語法錯誤，比如重複的唯一鍵值，也會造成自增id的不連續。

4. 5.1.22之前，innodb使用一個表鎖解決自增欄位的一致性問題（內部是用一個計數器維護，每次自增時要加表鎖），如果一行一行的插入資料則沒有什麼問題，但是如果大量的併發插入就廢了，表鎖會引起SQL堵塞，不但影響效率，而且可能會瞬間達到max_connections而崩潰。在 5.1.22之後，innodb使用新的方式解決自增欄位一致性問題，對於可以預判行數的insert語句，innodb使用一個輕量級的互斥量。如：某一insert語句1執行前，表的AUTO_INCREMENT=1，語句1的插入行數已知為3，innodb在語句1的實際插入操作執行前就預分配給該語句三個自增值，當有一個新的insert語句2要執行時，讀取的AUTO_INCREMENT=4，這樣雖然語句1可能還沒有執行完，語句2就可直接執行無需等待語句2。這種方式對於可預判插入行數的插入語句有效，如：insert和replace。對於無法提前獲知插入行數的語句，如：insert...select...、replace...select...和load

data則innodb還是使用表鎖。

insert語句中有時會顯示的設定自增欄位的值，對於這種情況innodb還是會預分配給語句總行數的自增值而不是隻有實際使用系統自增的行。因而有可能會造成自增欄位的值不連續。如：

 

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

 c1 為自增欄位，實際的AUTO_INCREMENT 會大2。

設定新自增互斥方式：通過配置選項：innodb_autoinc_lock_mode，它是專門用來在使用auto_increment的情況下調整鎖策略的，目前有三種選擇：
innodb_autoinc_lock_mode = 0 (“traditional” lock mode：全部使用表鎖)
innodb_autoinc_lock_mode = 1 (預設)(“consecutive”lock
mode：可預判行數時使用新方式，不可時使用表鎖) 
innodb_autoinc_lock_mode = 2 (“interleaved” lock
mode：全部使用新方式，不安全，不適合replication)

 

MySQL自增序列的妙用

MySQL字增序列的基本知識：

自增欄位的資料型別必須為：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT中的一種，並且必須作為主鍵或聯合主鍵的一部分。若是作為主鍵的一部分，自增序列欄位值將會喪失唯一性保證。

自增序列，對INSERT語句敏感，讀UPDATE，DELETE語句是不敏感的。演示示例：

root@localhost : test 02:36:44> CREATE TABLE`auto_test` (

   ->  `ID` bigint(20)unsigned NOT NULL AUTO_INCREMENT,

   ->  `MEMBER_ID`varchar(32) DEFAULT '',

   ->  PRIMARY KEY (`ID`),

   ->  UNIQUE KEY`idx_auto_test_MEMBER_ID` (`MEMBER_ID`)

   -> ) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULTCHARSET=utf8;

Query OK, 0 rows affected (0.01 sec)

 

root@localhost : test 02:39:20> INSERT INTOauto_test(member_id) values('eugene');

Query OK, 1 row affected (0.00 sec)

 

root@localhost : test 02:43:21> show create tableauto_test;

| Table    | CreateTable                                                                                                                                                                                                                                         

| auto_test | CREATE TABLE `auto_test` (

 `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

 `MEMBER_ID` varchar(32) DEFAULT '',

 PRIMARY KEY (`ID`),

 UNIQUE KEY `idx_auto_test_MEMBER_ID` (`MEMBER_ID`)

) ENGINE=InnoDB AUTO_INCREMENT=2DEFAULTCHARSET=utf8

1 row in set (0.00 sec)

  root@localhost : test 04:39:11> insertinto auto_test(id,member_id) values(10,'alibaba');

Query OK, 1 row affected (0.00 sec)

 

root@localhost : test 04:41:20> show create tableauto_test;

Table    | Create Table                                                                                                                                                                                                                                         

| auto_test | CREATE TABLE `auto_test` (

 `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

 `MEMBER_ID` varchar(32) DEFAULT '',

 PRIMARY KEY (`ID`),

 UNIQUE KEY `idx_auto_test_MEMBER_ID` (`MEMBER_ID`)

) ENGINE=InnoDB AUTO_INCREMENT=11DEFAULTCHARSET=utf8 |

1 row in set (0.00 sec)

 

root@localhost : test 04:53:29>update auto_testset ID=ID+20 WHERE ID =10;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

 

root@localhost : test 04:54:40> show create tableauto_test;

Table    | CreateTable                                                                                                                                                                                                                                         

| auto_test | CREATE TABLE `auto_test` (

 `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

 `MEMBER_ID` varchar(32) DEFAULT '',

 PRIMARY KEY (`ID`),

 UNIQUE KEY `idx_auto_test_MEMBER_ID` (`MEMBER_ID`)

) ENGINE=InnoDB AUTO_INCREMENT=11DEFAULTCHARSET=utf8 |

1 row in set (0.00 sec)

 

所以若真的特殊情況下，需要使用UPDATE語句更新自增序列值，而且更新後的自增序列的最大值超過定義中的AUTO_INCREMENT值，就必須使用類似字句：

ALTER TABLE auto_test AUTO_INCREMENT=MAX(ID)+1;

                                                                                                                                                                                                                                                                      

MySQL還提供了兩個全域性引數AUTO_INCREMENT_INCREMENT與AUTO_INCREMENT_OFFSET，用於配置MySQL例項的自增序列增長起始值與步長。

AUTO_INCREMENT_INCREMENT= n，則設定自增序列增長的步長為n；

AUTO_INCREMENT_OFFSET=m，則設定自增序列的起始位置為m；

若使用這個特性與複製功能結合的話，就可以方便地實現Master-Master的兩邊讀寫，以及更多的應用擴充套件。

 

 殘留問題：

自增ID如何實現該條記錄被重複採集時內容不重複？？