主頁(yè) > 知識(shí)庫(kù) > MySQL中的隱藏列的具體查看

MySQL中的隱藏列的具體查看

熱門標(biāo)簽:電話機(jī)器人接口是什么樣的 怎么在高德地圖標(biāo)注多個(gè)點(diǎn) 四川穩(wěn)定外呼系統(tǒng)公司 百度地圖標(biāo)注信息怎么修改 沈陽(yáng)外呼系統(tǒng)有效果嗎 商家地圖標(biāo)注圖片 溫州語(yǔ)音外呼系統(tǒng)排名 AI智能云呼電話機(jī)器人怎么注冊(cè) 福州外呼系統(tǒng)招商

在介紹mysql的多版本并發(fā)控制mvcc的過(guò)程中,我們提到過(guò)mysql中存在一些隱藏列,例如行標(biāo)識(shí)、事務(wù)ID、回滾指針等,不知道大家是否和我一樣好奇過(guò),要怎樣才能實(shí)際地看到這些隱藏列的值呢?

本文我們就來(lái)重點(diǎn)討論一下諸多隱藏列中的行標(biāo)識(shí)DB_ROW_ID,實(shí)際上,將行標(biāo)識(shí)稱為隱藏列并不準(zhǔn)確,因?yàn)樗⒉皇且粋€(gè)真實(shí)存在的列,DB_ROW_ID實(shí)際上是一個(gè)非空唯一列的別名。在撥開它的神秘面紗之前,我們看一下官方文檔的說(shuō)明:

If a table has a PRIMARY KEY or UNIQUE NOT NULL index that consists of a single column that has an integer type, you can use _rowid to refer to the indexed column in SELECT statements

簡(jiǎn)單翻譯一下,如果在表中存在主鍵或非空唯一索引,并且僅由一個(gè)整數(shù)類型的列構(gòu)成,那么就可以使用SELECT語(yǔ)句直接查詢_rowid,并且這個(gè)_rowid的值會(huì)引用該索引列的值。

著重看一下文檔中提到的幾個(gè)關(guān)鍵字,主鍵、唯一索引、非空、單獨(dú)一列、數(shù)值類型,接下來(lái)我們就要從這些角度入手,探究一下神秘的隱藏字段_rowid。

1、存在主鍵

先看設(shè)置了主鍵且是數(shù)值類型的情況,使用下面的語(yǔ)句建表:

CREATE TABLE `table1` (
  `id` bigint(20) NOT NULL PRIMARY KEY ,
  `name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB;

插入三條測(cè)試數(shù)據(jù)后,執(zhí)行下面的查詢語(yǔ)句,在select查詢語(yǔ)句中直接查詢_rowid

select *,_rowid from table1

查看執(zhí)行結(jié)果,_rowid可以被正常查詢:

可以看到在設(shè)置了主鍵,并且主鍵字段是數(shù)值類型的情況下,_rowid直接引用了主鍵字段的值。對(duì)于這種可以被select語(yǔ)句查詢到的的情況,可以將其稱為顯式的rowid。

回顧一下前面提到的文檔中的幾個(gè)關(guān)鍵字,分別對(duì)其進(jìn)行分析。由于主鍵必定是非空字段,下面來(lái)看一下主鍵是非數(shù)值類型字段的情況,建表如下:

CREATE TABLE `table2` (
  `id` varchar(20) NOT NULL PRIMARY KEY ,
  `name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB;

table2執(zhí)行上面相同的查詢,結(jié)果報(bào)錯(cuò)無(wú)法查詢_rowid,也就證明了如果主鍵字段是非數(shù)值類型,那么將無(wú)法直接查詢_rowid。

2、無(wú)主鍵,存在唯一索引

上面對(duì)兩種類型的主鍵進(jìn)行了測(cè)試后,接下來(lái)我們看一下當(dāng)表中沒(méi)有主鍵、但存在唯一索引的情況。首先測(cè)試非空唯一索引加在數(shù)值類型字段的情況,建表如下:

CREATE TABLE `table3` (
  `id` bigint(20) NOT NULL UNIQUE KEY,
  `name` varchar(32)
) ENGINE=InnoDB;

查詢可以正常執(zhí)行,并且_rowid引用了唯一索引所在列的值:

唯一索引與主鍵不同的是,唯一索引所在的字段可以為NULL。在上面的table3中,在唯一索引所在的列上添加了NOT NULL非空約束,如果我們把這個(gè)非空約束刪除掉,還能顯式地查詢到_rowid嗎?下面再創(chuàng)建一個(gè)表,不同是在唯一索引所在的列上,不添加非空約束:

CREATE TABLE `table4` (
  `id` bigint(20) UNIQUE KEY,
  `name` varchar(32)
) ENGINE=InnoDB;

執(zhí)行查詢語(yǔ)句,在這種情況下,無(wú)法顯式地查詢到_rowid

和主鍵類似的,我們?cè)賹?duì)唯一索引被加在非數(shù)值類型的字段的情況進(jìn)行測(cè)試。下面在建表時(shí)將唯一索引添加在字符類型的字段上,并添加非空約束:

CREATE TABLE `table5` (
  `id` bigint(20),
  `name` varchar(32) NOT NULL UNIQUE KEY
) ENGINE=InnoDB;

同樣無(wú)法顯示的查詢到_rowid

針對(duì)上面三種情況的測(cè)試結(jié)果,可以得出結(jié)論,當(dāng)沒(méi)有主鍵、但存在唯一索引的情況下,只有該唯一索引被添加在數(shù)值類型的字段上,且該字段添加了非空約束時(shí),才能夠顯式地查詢到_rowid,并且_rowid引用了這個(gè)唯一索引字段的值。

3、存在聯(lián)合主鍵或聯(lián)合唯一索引

在上面的測(cè)試中,我們都是將主鍵或唯一索引作用在單獨(dú)的一列上,那么如果使用了聯(lián)合主鍵或聯(lián)合唯一索引時(shí),結(jié)果會(huì)如何呢?還是先看一下官方文檔中的說(shuō)明:

_rowid refers to the PRIMARY KEY column if there is a PRIMARY KEY consisting of a single integer column. If there is a PRIMARY KEY but it does not consist of a single integer column, _rowid cannot be used.

簡(jiǎn)單來(lái)說(shuō)就是,如果主鍵存在、且僅由數(shù)值類型的一列構(gòu)成,那么_rowid的值會(huì)引用主鍵。如果主鍵是由多列構(gòu)成,那么_rowid將不可用。

根據(jù)這一描述,我們測(cè)試一下聯(lián)合主鍵的情況,下面將兩列數(shù)值類型字段作為聯(lián)合主鍵建表:

執(zhí)行結(jié)果無(wú)法顯示的查詢到_rowid

同樣,這一理論也可以作用于唯一索引,如果非空唯一索引不是由單獨(dú)一列構(gòu)成,那么也無(wú)法直接查詢得到_rowid。這一測(cè)試過(guò)程省略,有興趣的小伙伴可以自己動(dòng)手試試。

4、存在多個(gè)唯一索引

在mysql中,每張表只能存在一個(gè)主鍵,但是可以存在多個(gè)唯一索引。那么如果同時(shí)存在多個(gè)符合規(guī)則的唯一索引,會(huì)引用哪個(gè)作為_rowid的值呢?老規(guī)矩,還是看官方文檔的解答:

Otherwise, _rowid refers to the column in the first UNIQUE NOT NULL index if that index consists of a single integer column. If the first UNIQUE NOT NULL index does not consist of a single integer column, _rowid cannot be used.

簡(jiǎn)單翻譯一下,如果表中的第一個(gè)非空唯一索引僅由一個(gè)整數(shù)類型字段構(gòu)成,那么_rowid會(huì)引用這個(gè)字段的值。否則,如果第一個(gè)非空唯一索引不滿足這種情況,那么_rowid將不可用。

在下面的表中,創(chuàng)建兩個(gè)都符合規(guī)則的唯一索引:

CREATE TABLE `table8_2` (
  `id` bigint(20) NOT NULL,
  `no` bigint(20) NOT NULL,
  `name` varchar(32),
  UNIQUE KEY(no),
  UNIQUE KEY(id)
) ENGINE=InnoDB;

看一下執(zhí)行查詢語(yǔ)句的結(jié)果:

可以看到_rowid的值與no這一列的值相同,證明了_rowid會(huì)嚴(yán)格地選取第一個(gè)創(chuàng)建的唯一索引作為它的引用。

那么,如果表中創(chuàng)建的第一個(gè)唯一索引不符合_rowid的引用規(guī)則,第二個(gè)唯一索引滿足規(guī)則,這種情況下,_rowid可以被顯示地查詢嗎?針對(duì)這種情況我們建表如下,表中的第一個(gè)索引是聯(lián)合唯一索引,第二個(gè)索引才是單列的唯一索引情況,再來(lái)進(jìn)行一下測(cè)試:

CREATE TABLE `table9` (
  `id` bigint(20) NOT NULL,
  `no` bigint(20) NOT NULL,
  `name` varchar(32),
  UNIQUE KEY `index1`(`id`,`no`),
  UNIQUE KEY `index2`(`id`)
) ENGINE=InnoDB;

進(jìn)行查詢,可以看到雖然存在一個(gè)單列的非空唯一索引,但是因?yàn)轫樞蜻x取的第一個(gè)不滿足要求,因此仍然不能直接查詢_rowid

如果將上面創(chuàng)建唯一索引的語(yǔ)句順序調(diào)換,那么將可以正常顯式的查詢到_rowid。

5、同時(shí)存在主鍵與唯一索引

從上面的例子中,可以看到唯一索引的定義順序會(huì)決定將哪一個(gè)索引應(yīng)用_rowid,那么當(dāng)同時(shí)存在主鍵和唯一索引時(shí),定義順序會(huì)對(duì)其引用造成影響嗎?

按照下面的語(yǔ)句創(chuàng)建兩個(gè)表,只有創(chuàng)建主鍵和唯一索引的順序不同:

CREATE TABLE `table11` (
  `id` bigint(20) NOT NULL,
  `no` bigint(20) NOT NULL,
  PRIMARY KEY(id),
  UNIQUE KEY(no)
) ENGINE=InnoDB;

CREATE TABLE `table12` (
  `id` bigint(20) NOT NULL,
  `no` bigint(20) NOT NULL,
  UNIQUE KEY(id),
  PRIMARY KEY(no)
) ENGINE=InnoDB;

查看運(yùn)行結(jié)果:

可以得出結(jié)論,當(dāng)同時(shí)存在符合條件的主鍵和唯一索引時(shí),無(wú)論創(chuàng)建順序如何,_rowid都會(huì)優(yōu)先引用主鍵字段的值。

6、無(wú)符合條件的主鍵與唯一索引

上面,我們把能夠直接通過(guò)select語(yǔ)句查詢到的稱為顯式的_rowid,在其他情況下雖然_rowid不能被顯式查詢,但是它也是一直存在的,這種情況我們可以將其稱為隱式的_rowid。

實(shí)際上,innoDB在沒(méi)有默認(rèn)主鍵的情況下會(huì)生成一個(gè)6字節(jié)長(zhǎng)度的無(wú)符號(hào)數(shù)作為自動(dòng)增長(zhǎng)的_rowid,因此最大為2^48-1,到達(dá)最大值后會(huì)從0開始計(jì)算。下面,我們創(chuàng)建一個(gè)沒(méi)有主鍵與唯一索引的表,在這張表的基礎(chǔ)上,探究一下隱式的_rowid。

CREATE TABLE `table10` (
  `id` bigint(20),
  `name` varchar(32)
) ENGINE=InnoDB;

首先,我們需要先查找到mysql的進(jìn)程pid

ps -ef | grep mysqld

可以看到,mysql的進(jìn)程pid是2068:

在開始動(dòng)手前,還需要做一點(diǎn)鋪墊, 在innoDB中其實(shí)維護(hù)了一個(gè)全局變量dictsys.row_id,沒(méi)有定義主鍵的表都會(huì)共享使用這個(gè)row_id,在插入數(shù)據(jù)時(shí)會(huì)把這個(gè)全局row_id當(dāng)作自己的主鍵,然后再將這個(gè)全局變量加 1。

接下來(lái)我們需要用到gdb調(diào)試的相關(guān)技術(shù),gdb是一個(gè)在Linux下的調(diào)試工具,可以用來(lái)調(diào)試可執(zhí)行文件。在服務(wù)器上,先通過(guò)yum install gdb安裝,安裝完成后,通過(guò)下面的gdb命令 把 row_id 修改為 1:

gdb -p 2068 -ex 'p dict_sys->row_id=1' -batch

命令執(zhí)行結(jié)果:

在空表中插入3行數(shù)據(jù):

INSERT INTO table10 VALUES (100000001, 'Hydra');
INSERT INTO table10 VALUES (100000002, 'Trunks');
INSERT INTO table10 VALUES (100000003, 'Susan');

查看表中的數(shù)據(jù),此時(shí)對(duì)應(yīng)的_rowid理論上是1~3:

然后通過(guò)gdb命令把row_id改為最大值2^48,此時(shí)已超過(guò)dictsys.row_id最大值:

gdb -p 2068 -ex 'p dict_sys->row_id=281474976710656' -batch

命令執(zhí)行結(jié)果:

再向表中插入三條數(shù)據(jù):

INSERT INTO table10 VALUES (100000004, 'King');
INSERT INTO table10 VALUES (100000005, 'Queen');
INSERT INTO table10 VALUES (100000006, 'Jack');

查看表中的全部數(shù)據(jù),可以看到第一次插入的三條數(shù)據(jù)中,有兩條數(shù)據(jù)被覆蓋了:

為什么會(huì)出現(xiàn)數(shù)據(jù)覆蓋的情況呢,我們對(duì)這一結(jié)果進(jìn)行分析。首先,在第一次插入數(shù)據(jù)前_rowid為1,插入的三條數(shù)據(jù)對(duì)應(yīng)的_rowid為1、2、3。如下圖所示:

當(dāng)手動(dòng)設(shè)置_rowid為最大值后,下一次插入數(shù)據(jù)時(shí),插入的_rowid重新從0開始,因此第二次插入的三條數(shù)據(jù)的_rowid應(yīng)該為0、1、2。這時(shí)準(zhǔn)備被插入的數(shù)據(jù)如下所示:

當(dāng)出現(xiàn)相同_rowid的情況下,新插入的數(shù)據(jù)會(huì)根據(jù)_rowid覆蓋掉原有的數(shù)據(jù),過(guò)程如圖所示:

所以當(dāng)表中的主鍵或唯一索引不滿足我們前面提到的要求時(shí),innoDB使用的隱式的_rowid是存在一定風(fēng)險(xiǎn)的,雖然說(shuō)2^48這個(gè)值很大,但還是有可能被用盡的,當(dāng)_rowid用盡后,之前的記錄就會(huì)被覆蓋。從這一角度也可以提醒大家,在建表時(shí)一定要?jiǎng)?chuàng)建主鍵,否則就有可能發(fā)生數(shù)據(jù)的覆蓋。

本文基于mysql 5.7.31 進(jìn)行測(cè)試

官方文檔:https://dev.mysql.com/doc/refman/5.7/en/create-index.html

到此這篇關(guān)于MySQL中的隱藏列的具體使用的文章就介紹到這了,更多相關(guān)MySQL 隱藏列內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

您可能感興趣的文章:
  • MySQL 8.0新特性之隱藏字段的深入講解
  • mysql sql語(yǔ)句隱藏手機(jī)號(hào)碼中間四位的方法
  • MySQL在Linux系統(tǒng)中隱藏命令行中的密碼的方法

標(biāo)簽:營(yíng)口 汕尾 寶雞 西寧 邯鄲 來(lái)賓 無(wú)錫 七臺(tái)河

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MySQL中的隱藏列的具體查看》,本文關(guān)鍵詞  MySQL,中的,隱藏,列,的,具體,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《MySQL中的隱藏列的具體查看》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于MySQL中的隱藏列的具體查看的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章