想在mysql數(shù)據(jù)庫中的表中插入一列，怎么做？

傳統(tǒng)情況

網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)建站！專注于網(wǎng)頁設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、小程序制作、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了邢臺(tái)免費(fèi)建站歡迎大家使用！

我們先回顧一下，在沒有 "立刻加列" 功能時(shí)，加列操作是怎么完成的。我們也借此來熟悉一下本期的圖例：

當(dāng)進(jìn)行 加列操作 時(shí)，所有的數(shù)據(jù)行 都必須要 增加一段數(shù)據(jù)（圖中的 列 4 數(shù)據(jù)）

如上一期圖解所講，當(dāng)改變數(shù)據(jù)行的長度，就需要 重建表空間（圖中灰藍(lán)的部分為發(fā)生變更的部分）

數(shù)據(jù)字典中的列定義也會(huì)被更新

以上操作的問題在于 每次加列 操作都需要重建表空間，這就需要大量 IO以及大量的時(shí)間

立刻加列

"立刻加列" 的過程如下圖：

請點(diǎn)擊輸入圖片描述

請點(diǎn)擊輸入圖片描述

"立刻加列" 時(shí)，只會(huì)變更數(shù)據(jù)字典中的內(nèi)容，包括：

在列定義中增加 新列的定義

增加 新列的默認(rèn)值

"立刻加列"?后，當(dāng)要讀取表中的數(shù)據(jù)時(shí)：

由于 "立刻加列" 沒有 變更行數(shù)據(jù)，讀取的行數(shù)據(jù)只有 3 列

MySQL 會(huì)將 新增的第 4 列的默認(rèn)值，追加到 讀取的數(shù)據(jù)后

以上過程描述了 如何讀取?在 "立刻加列" 之前寫入的數(shù)據(jù)，其實(shí)質(zhì)是：在讀取數(shù)據(jù)的過程中，"偽造"?了一個(gè)新列出來

那么如何讀取?在 "立刻加列" 之后?寫入的數(shù)據(jù)呢 ? 過程如下圖：

當(dāng)讀取 行 4 時(shí)：

請點(diǎn)擊輸入圖片描述

請點(diǎn)擊輸入圖片描述

通過判斷?數(shù)據(jù)行的頭信息中的instant?標(biāo)志位，可以知道該行的格式是 "新格式"：該行頭信息后有一個(gè)新字段?"列數(shù)"

通過讀取?數(shù)據(jù)行的?"列數(shù)"?字段，可以知道 該行數(shù)據(jù)中多少列有 "真實(shí)" 的數(shù)據(jù)，從而按列數(shù)讀取數(shù)據(jù)

通過上圖可以看到：讀取?在"立刻加列"?前/后寫入的數(shù)據(jù)是不同的流程

通過以上的討論，我們可以總結(jié)?"立刻加列"?之所以高效的原因是：

在執(zhí)行?"立刻加列"?時(shí)，不變更數(shù)據(jù)行的結(jié)構(gòu)

讀取 "舊" 數(shù)據(jù)時(shí)，"偽造"?新增的列，使結(jié)果正確

寫入 "新" 數(shù)據(jù)時(shí)，使用了新的數(shù)據(jù)格式（增加了instant標(biāo)志位 和?"列數(shù)"?字段)，以區(qū)分新舊數(shù)據(jù)

讀取 "新" 數(shù)據(jù)時(shí)，可以如實(shí)讀取數(shù)據(jù)

那么?我們是否能一直 "偽造"?下去???"偽造"?何時(shí)會(huì)被拆穿 ?

考慮以下場景：

用 "立刻加列" 增加列 A

寫入數(shù)據(jù)行 1

用 "立刻加列" 增加列?B

寫入數(shù)據(jù)行?2

刪除列?B

我們推測一下 "刪除列 B" 的最小代價(jià)：需要修改 數(shù)據(jù)行中的instant標(biāo)志位或?"列數(shù)"?字段，這至少會(huì)影響到?"立刻加列"?之后寫入的數(shù)據(jù)行，成本類似于重建數(shù)據(jù)

從以上推測可知：當(dāng)出現(xiàn) 與?"立刻加列"?操作不兼容 的 DDL 操作時(shí)，數(shù)據(jù)表需要進(jìn)行重建，如下圖所示：

請點(diǎn)擊輸入圖片描述

請點(diǎn)擊輸入圖片描述

擴(kuò)展思考題：是否能設(shè)計(jì)其他的數(shù)據(jù)格式，取代instant標(biāo)志位和?"列數(shù)"?字段，使得 加列/刪列 操作都能 "立刻完成" ?（提示：考慮 加列?- 刪列?- 再加列 的情況）

使用限制

在了解原理之后，我們來看看?"立刻加列"?的使用限制，就很容易能理解其中的前兩項(xiàng)：

"立刻加列"?的加列位置只能在表的最后，而不能加在其他列之間

在元數(shù)據(jù)中，只記錄了 數(shù)據(jù)行 應(yīng)有多少列，而沒有記錄 這些列 應(yīng)出現(xiàn)的位置。所以無法實(shí)現(xiàn)指定列的位置

"立刻加列"?不能添加主鍵列

加列 不能涉及聚簇索引的變更，否則就變成了 "重建" 操作，不是 "立刻" 完成了

"立刻加列"不支持壓縮的表格式

按照 WL 的說法："COMPRESSED is no need to supported"（沒必要支持不怎么用的格式）

總結(jié)回顧

我們總結(jié)一下上面的討論：

"立刻加列" 之所以高效的原因是：

在執(zhí)行 "立刻加列" 時(shí)，不變更數(shù)據(jù)行的結(jié)構(gòu)

讀取 "舊" 數(shù)據(jù)時(shí)，"偽造"?新增的列，使結(jié)果正確

寫入 "新" 數(shù)據(jù)時(shí)，使用了新的數(shù)據(jù)格式?(增加了?instant 標(biāo)志位?和 "列數(shù)" 字段)，以區(qū)分新舊數(shù)據(jù)

讀取 "新" 數(shù)據(jù)時(shí)，可以如實(shí)讀取數(shù)據(jù)

"立刻加列"?的 "偽造" 手法，不能一直維持下去。當(dāng)發(fā)生?與 "立刻加列" 操作不兼容?的 DDL?時(shí)，表數(shù)據(jù)就會(huì)發(fā)生重建

回到之前遺留的兩個(gè)問題：

"立刻加列" 是如何工作的 ?

我們已經(jīng)解答了這個(gè)問題

所謂 "立刻加列" 是否完全不影響業(yè)務(wù)，是否是真正的 "立刻" 完成 ?

可以看到：就算是 "立刻加列"，也需要變更 數(shù)據(jù)字典，那么 該上的鎖還是逃不掉的。也就是說 這里的 "立刻" 指的是 "不變更數(shù)據(jù)行的結(jié)構(gòu)"，而并非指 "零成本地完成任務(wù)"

mysql單獨(dú)添加一列的數(shù)據(jù)為123

mysql單獨(dú)添加一列的數(shù)據(jù)為123？

答案如下：單獨(dú)添加數(shù)據(jù)123正確的操作方法是，首先第一步先點(diǎn)擊打開設(shè)置按鈕，然后帳戶管理在頁面點(diǎn)擊賬號安全中心進(jìn)入即可完成！多實(shí)踐測試。

一文讓你徹底弄懂MySQL自增列

MYSQL的自增列在實(shí)際生產(chǎn)中應(yīng)用的非常廣泛，相信各位所在的公司or團(tuán)隊(duì)，MYSQL開發(fā)規(guī)范中一定會(huì)有要求盡量使用自增列去充當(dāng)表的主鍵，為什么DBA會(huì)有這樣的要求，各位在使用MYSQL自增列時(shí)遇到過哪些問題？這些問題是由什么原因造成的呢？本文由淺入深，帶領(lǐng)大家徹底弄懂MYSQL的自增機(jī)制。

1.? 通過auto_increment關(guān)鍵字來指定自增的列，并指定自增列的初始值為1。

[root@localhost][test1]Create table t(id int auto_increment ,namevarchar(10),primary key(id))auto_increment=1;

QueryOK, 0 rows affected (0.63 sec)

2.? 自增列上必須有索引，將t表的主鍵索引刪除掉，會(huì)報(bào)錯(cuò)

[root@localhost][test1]alter table t drop primary key;

ERROR1075 (42000): Incorrect table definition; there can be only one auto column andit must be defined as a key

3.? 設(shè)定auto_increment_increment參數(shù)，可以調(diào)整自增步長，該參數(shù)有session級跟global級，在分庫分表以及雙主or多主的模式下比較有用。

4.? 一個(gè)表上只能有一個(gè)自增列

5.? Mysql5.7及以下版本，innodb表的自增值保存在內(nèi)存中，重啟后表的自增值會(huì)設(shè)為max(id)+1，而myisam引擎的自增值是保存在文件中，重啟不會(huì)丟失。Mysql8.0開始，innodb的自增id能持久化了，重啟mysql，自增ID不會(huì)丟。

首先：表中自增列的上限是根據(jù)自增列的字段類型來定的。

若設(shè)定了自增id充當(dāng)主鍵，當(dāng)達(dá)到了自增id的上限值時(shí)，會(huì)發(fā)生什么樣的事情呢？還是以上面創(chuàng)建的 t表為例， 先回顧它的表結(jié)構(gòu)：

CREATETABLE `t` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(10) COLLATE utf8mb4_binDEFAULT NULL,

PRIMARY KEY (`id`)

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

無符號的int類型，上限是2147483647。這里我們將表的自增值設(shè)為2147483647，再插入兩行數(shù)據(jù)：

[root@localhost][test1]alter table t auto_increment=2147483647;

QueryOK, 0 rows affected (0.01 sec)

Records:0? Duplicates: 0? Warnings: 0

[root@localhost][test1]insert into t(name) values ('test');??????????

QueryOK, 1 row affected (0.01 sec)

[root@localhost][test1]insert into t(name) values ('test');

ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

可以看到，第一個(gè)插入沒問題，因?yàn)樽栽隽械闹禐?147483647，這是達(dá)到了上限，還沒有超過，第二行數(shù)據(jù)插入時(shí)，則報(bào)出主鍵重復(fù)，在達(dá)到上限后，無法再分配新的更大的自增值，也沒有從1開始從頭分配，在這里表的auto_increment值會(huì)一直是2147483647。

對于寫入量大，且經(jīng)常刪除數(shù)據(jù)的表，自增id設(shè)為int類型還是偏小的，所以我們?yōu)榱吮苊獬霈F(xiàn)自增id漲滿的情況，這邊統(tǒng)一建議自增id的類型設(shè)為unsigned bingint，這樣基本可以保障表的自增id是永遠(yuǎn)夠用的。

這里內(nèi)容比較多，innodb是索引組織表，所以涉及到索引的知識(shí)，但這不是本文的重點(diǎn)，我們快速回顧索引知識(shí)：

1.? Innodb索引分為主鍵跟輔助索引，主鍵即全表，輔助索引葉子節(jié)點(diǎn)保存主鍵的值，而主鍵的葉子節(jié)點(diǎn)保存數(shù)據(jù)行，中間節(jié)點(diǎn)存著葉子節(jié)點(diǎn)的路由值。

2.? Innodb存儲(chǔ)數(shù)據(jù)（索引）的單位是頁，這里默認(rèn)是16K，這也意味著，數(shù)據(jù)本身越小，一個(gè)頁中能存數(shù)據(jù)的量越多，而檢索效率不僅僅由索引的層數(shù)來決定，更是由一次能夠緩存的數(shù)據(jù)量來定，也就是說數(shù)據(jù)本身越小，則一次IO能夠提取到緩沖區(qū)的數(shù)據(jù)越多（OS每次IO的量是固定的4K），查詢的效率越好。

其實(shí)能夠理解索引的結(jié)構(gòu)及索引寫入插入、更新的原理，則自然就明白為何建議使用自增id。這里我直接列出使用自增id 當(dāng)主鍵的好處吧：

1.? 順序?qū)懭耄苊饬巳~的分裂，數(shù)據(jù)寫入效率好

2.? 縮小了表的體積，特別是相比于UUID當(dāng)主鍵，甚至組合字段當(dāng)主鍵時(shí)，效果更明顯

3.? 查詢效率好，原因就是我上面說到索引知識(shí)的第二點(diǎn)。

4.? 某些情況下，我們可以利用自增id來統(tǒng)計(jì)大表的大致行數(shù)。

5.? 在數(shù)據(jù)歸檔or垃圾數(shù)據(jù)清理時(shí)，也可方便的利用這個(gè)id去操作，效率高。

容易出現(xiàn)不連續(xù)的id

有的同志會(huì)發(fā)現(xiàn)，自己的表中id值存在空洞，如類似于1、2、3、8、9、10這樣，有的適合有想依賴于自增id的連續(xù)性來實(shí)現(xiàn)業(yè)務(wù)邏輯，所以會(huì)想方設(shè)法去修改id讓其變的連續(xù)，其實(shí)，這是沒有必要的，這一塊的業(yè)務(wù)邏輯交由MySQL實(shí)現(xiàn)是很不理智的，表的記錄小還好，要是表的數(shù)據(jù)量很大，修改起來就糟糕了。那么，為什么自增id會(huì)容易出現(xiàn)空洞呢？

自增id的修改機(jī)制如下：

在MySQL里面，如果字段id被定義為AUTO_INCREMENT，在插入一行數(shù)據(jù)的時(shí)候，自增值的行為如下：

1. 如果插入數(shù)據(jù)時(shí)id字段指定為0、null 或未指定值，那么就把這個(gè)表當(dāng)前的

AUTO_INCREMENT值填到自增字段；

2. 如果插入數(shù)據(jù)時(shí)id字段指定了具體的值，就直接使用語句里指定的值。

根據(jù)要插入的值和當(dāng)前自增值的大小關(guān)系，自增值的變更結(jié)果也會(huì)有所不同。假設(shè)，某次要插入的值是X，當(dāng)前的自增值是Y。

1. 如果XY，那么這個(gè)表的自增值不變；

2. 如果X≥Y，就需要把當(dāng)前自增值修改為 新的自增值 。

新的自增值生成算法是：從auto_increment_offset開始，以auto_increment_increment為步長，持續(xù)疊加，直到找到第一個(gè)大于X的值，作為新的自增值。

Insert、update、delete操作會(huì)讓id不連續(xù)。

Delete、update：刪除中間數(shù)據(jù)，會(huì)造成空動(dòng)，而修改自增id值，也會(huì)造成空洞（這個(gè)很少）。

Insert：插入報(bào)錯(cuò)（唯一鍵沖突與事務(wù)回滾），會(huì)造成空洞，因?yàn)檫@時(shí)候自增id已經(jīng)分配出去了，新的自增值已經(jīng)生成，如下面例子：

[root@localhost][test1] select * fromt;

+----+------+

| id | name |

+----+------+

|? 1| aaa? |

|? 2| aaa? |

|? 3| aaa? |

|? 4| aaa? |

+----+------+

4 rows in set (0.00 sec)

[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';

+----------------+

| Auto_increment |

+----------------+

|????????????? 5 |

+----------------+

1 row in set (0.00 sec)

[root@localhost][test1] begin;

Query OK, 0 rows affected (0.00 sec)

[root@localhost][test1] insert intot(name) values('aaa');

Query OK, 1 row affected (0.00 sec)

[root@localhost][test1] select * fromt;

+----+------+

| id | name |

+----+------+

|? 1| aaa? |

|? 2| aaa? |

|? 3| aaa? |

|? 4| aaa? |

|? 5| aaa? |

+----+------+

5 rows in set (0.00 sec)

[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';

+----------------+

| Auto_increment |

+----------------+

|????????????? 6 |

+----------------+

1 row in set (0.00 sec)

[root@localhost][test1] rollback;

Query OK, 0 rows affected (0.00 sec)

[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';

+----------------+

| Auto_increment |

+----------------+

|????????????? 6 |

+----------------+

1 row in set (0.01 sec)

[root@localhost][test1] select * fromt;

+----+------+

| id | name |

+----+------+

|? 1| aaa? |

|? 2| aaa? |

|? 3| aaa? |

|? 4| aaa? |

+----+------+

4 rows in set (0.00 sec)

可以看到，雖然事務(wù)回滾了，但自增id已經(jīng)回不到從前啦，唯一鍵沖突也是這樣的，這里就不做測試了。

在批量插入時(shí)（insert select等），也存在空洞的問題?？聪旅鎸?shí)驗(yàn)：

[root@localhost][test1] select * fromt;

+----+------+

| id | name |

+----+------+

|? 1| aaa? |

|? 2| aaa? |

|? 3| aaa? |

|? 4| aaa? |

+----+------+

4 rows in set (0.00 sec)

[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';

+----------------+

| Auto_increment |

+----------------+

|????????????? 5 |

+----------------+

1 row in set (0.00 sec)

[root@localhost][test1] insert intot(name) select name from t;??????????????????????

Query OK, 4 rows affected (0.04 sec)

Records: 4?Duplicates: 0? Warnings: 0

[root@localhost][test1] select * fromt;

+----+------+

| id | name |

+----+------+

|? 1| aaa? |

|? 2| aaa? |

|? 3| aaa? |

|? 4| aaa? |

|? 5| aaa? |

|? 6| aaa? |

|? 7| aaa? |

|? 8| aaa? |

+----+------+

8 rows in set (0.00 sec)

[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';

+----------------+

| Auto_increment |

+----------------+

|???????????? 12 |

+----------------+

1 row in set (0.00 sec)

可以看到，批量插入，導(dǎo)致下一個(gè)id值不為9了，再插入數(shù)據(jù)，即產(chǎn)生了空洞，這里是由mysql申請自增值的機(jī)制所造成的，MySQL在批量插入時(shí)，若一個(gè)值申請一個(gè)id，效率太慢，影響了批量插入的速度，故mysql采用下面的策略批量申請id。

1.? 語句執(zhí)行過程中，第一次申請自增id，會(huì)分配1個(gè)；

2.? 1個(gè)用完以后，這個(gè)語句第二次申請自增id，會(huì)分配2個(gè)；

3.? 2個(gè)用完以后，還是這個(gè)語句，第三次申請自增id，會(huì)分配4個(gè)；

4.? 依此類推，同一個(gè)語句去申請自增id，每次申請到的自增id個(gè)數(shù)都是上一次的兩倍。

在對自增列進(jìn)行操作時(shí)，存在著自增鎖，mysql的innodb_autoinc_lock_mode參數(shù)控制著自增鎖的上鎖機(jī)制。該參數(shù)有0、1、2三種模式：

0：語句執(zhí)行結(jié)束后釋放自增鎖，MySQL5.0時(shí)采用這種模式，并發(fā)度較低。

1：mysql的默認(rèn)設(shè)置。普通的insert語句申請后立馬釋放，insert select、replace insert、load data等批量插入語句要等語句執(zhí)行結(jié)束后才釋放，并發(fā)讀得到提升

2：所有的語句都是申請后立馬釋放，并發(fā)度大大提升！但是在binlog為statement格式時(shí)，主從數(shù)據(jù)會(huì)發(fā)生不一致。這一塊網(wǎng)上有很多介紹，我不做介紹了。

在徹底了解了MYSQL的自增機(jī)制以后，在實(shí)際生產(chǎn)中就能靈活避坑，這里建議不要用自增id值去當(dāng)表的行數(shù)，當(dāng)需要對大表準(zhǔn)確統(tǒng)計(jì)行數(shù)時(shí)，可以去count(*)從庫，如果業(yè)務(wù)很依賴大表的準(zhǔn)確行數(shù)，直接弄個(gè)中間表來統(tǒng)計(jì)，或者考慮要不要用mysql的innodb來存儲(chǔ)數(shù)據(jù)，這個(gè)是需要自己去權(quán)衡。另外對于要求很高的寫入性能，但寫入量又比較大的業(yè)務(wù)，自增id的使用依然存在熱點(diǎn)寫入的問題，存在性能瓶頸，這時(shí)候可通過分庫分表來解決。

分享文章：mysql中怎么增加列,mysql怎么加列數(shù)據(jù)
網(wǎng)頁網(wǎng)址：http://jinyejixie.com/article2/hojpoc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供Google、企業(yè)網(wǎng)站制作、網(wǎng)站導(dǎo)航、品牌網(wǎng)站制作、建站公司、做網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)