Shulou(Shulou.com)06/03 Report--

This article mainly introduces the "specific viewing methods of hidden columns in MySQL". In daily operation, I believe that many people have doubts about the specific viewing methods of hidden columns in MySQL. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "specific viewing methods of hidden columns in MySQL". Next, please follow the editor to study!

Catalogue

1. Primary key exists

2. No primary key, there is a unique index

3. There is a federated primary key or federated unique index

4. There are multiple unique indexes

5. There are both primary key and unique index.

6. No qualified primary key and unique index

In the process of introducing the multi-version concurrency control mvcc of mysql, we mentioned that there are some hidden columns in mysql, such as row identification, transaction ID, rollback pointer and so on. I wonder if you are as curious as I am about how to actually see the values of these hidden columns.

In this article, we will focus on the row identity DB_ROW_ID in many hidden columns. In fact, it is not accurate to call a row identity a hidden column because it is not a real column, and DB_ROW_ID is actually an alias for a non-empty unique column. Before we unveil it, let's take a look at the description of the official document:

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

To put it into simple translation, if there is a primary key or a non-empty unique index in the table, and it consists of only one column of integer type, then you can use the select statement to query _ rowid directly, and the value of this _ rowid refers to the value of the index column.

Focus on a few keywords mentioned in the document, primary key, unique index, non-empty, separate column, numeric type, and then we will explore the mysterious hidden field _ rowid from these angles.

1. Primary key exists

First, if the primary key is set and it is a numeric type, use the following statement to create the table:

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

After inserting three pieces of test data, execute the following query statement to query _ rowid directly in the select query statement:

Select *, _ rowid from table1

To view the execution result, _ rowid can be queried normally:

You can see that when the primary key is set and the primary key field is of a numeric type, _ rowid references the value of the primary key field directly. For a situation that can be queried by a select statement, it can be called an explicit rowid.

Review several keywords in the previously mentioned document and analyze them separately. Since the primary key must be a non-empty field, let's take a look at the case where the primary key is a non-numeric field. The table is as follows:

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

When the same query is executed in table2, the result error cannot query _ rowid, which proves that if the primary key field is of a non-numeric type, it will not be possible to query _ rowid directly.

2. No primary key, there is a unique index

After testing two types of primary keys on the, let's take a look at a situation where there is no primary key but there is a unique index in the table. First, test the addition of a non-empty unique index to a numeric type field, and create the table as follows:

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

The query executes normally, and _ rowid references the value of the column in which the unique index resides:

The unique index differs from the primary key in that the field in which the unique index is located can be NULL. In the above table3, a NOT NULL non-null constraint is added to the column where the unique index resides. If we delete this non-null constraint, can we explicitly query _ rowid? Let's create another table, unlike on the column where the unique index resides, without adding a non-null constraint:

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

Execute the query statement, in which case the _ rowid cannot be explicitly queried:

Similar to the primary key, we test the case where a unique index is added to a field of a non-numeric type. The following adds a unique index to a field of character type when creating the table, and adds a non-null constraint:

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

The query that also cannot be displayed reaches _ rowid:

Based on the test results in the above three cases, it can be concluded that when there is no primary key but there is a unique index, the _ rowid can be explicitly queried only if the unique index is added to a field of numeric type and a non-null constraint is added to the field, and _ rowid references the value of the unique index field.

3. There is a federated primary key or federated unique index

In the above tests, we all acted on a primary key or unique index on a separate column, so what would happen if we used a federated primary key or a federated unique index? Let's take a look at the instructions in the official documentation:

_ 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.

To put it simply, if the primary key exists and consists of only one column of numeric type, then the value of _ rowid references the primary key. If the primary key is made up of multiple columns, _ rowid will not be available.

According to this description, let's test the situation of federated primary keys. Here are two columns of numeric type fields as federated primary keys:

CREATE TABLE `table6` (`id` bigint (20) NOT NULL, `no` bigint (20) NOT NULL, `name` varchar (32), PRIMARY KEY (`id`, `no`) ENGINE=InnoDB

Execute a query whose results cannot be displayed to _ rowid:

Similarly, this theory can also be applied to unique indexes, and if a non-empty unique index is not made up of a single column, it is not possible to query directly to get _ rowid. This testing process is omitted, and interested partners can try it themselves.

4. There are multiple unique indexes

In mysql, there can be only one primary key per table, but there can be multiple unique indexes. So if there are multiple unique indexes that match the rules at the same time, which one will be referenced as the value of _ rowid? The old rule is to look at the answer of the official document:

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.

To make a simple translation, if the first non-empty unique index in the table consists of only one integer type field, _ rowid references the value of that field. Otherwise, if the first non-null unique index does not satisfy this situation, _ rowid will not be available.

In the following table, create two unique indexes that both conform to the rules:

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

Take a look at the results of executing the query:

You can see that the value of _ rowid is the same as that of the no column, proving that _ rowid strictly selects the first created unique index as its reference.

So, if the first unique index created in the table does not conform to the reference rule of _ rowid, and the second unique index meets the rule, can _ rowid be queried explicitly? In view of this situation, we create the table as follows. The first index in the table is the federated unique index, and the second index is the unique index in a single column. Let's test it again:

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

When querying, you can see that although there is a non-empty unique index with a single column, the _ rowid cannot be queried directly because the first one selected in order does not meet the requirements:

If you change the order of the statements above that create a unique index, you can query _ rowid normally and explicitly.

5. There are both primary key and unique index.

From the above example, you can see that the definition order of a unique index determines which index to apply _ rowid, so when both a primary key and a unique index exist, does the definition order affect their references?

Create two tables according to the following statement, except for the order in which the primary key and unique index are created:

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

View the running results:

It can be concluded that when both a qualified primary key and a unique index exist, _ rowid takes precedence to reference the value of the primary key field, regardless of the order in which it is created.

6. No qualified primary key and unique index

Above, we call the explicit _ rowid that can be queried directly through the select statement. In other cases, although _ rowid cannot be explicitly queried, it still exists all the time, in which case we can call it implicit _ rowid.

In fact, innoDB generates an unsigned number of 6 bytes in length as an auto-growing _ rowid without a default primary key, so the maximum is 2 ^ 48-1, which is calculated from 0 when it reaches the maximum. Next, let's create a table without a primary key and unique index, and explore the implicit _ rowid based on this table.

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

First, we need to find the process pid of mysql:

Ps-ef | grep mysqld

As you can see, the process pid of mysql is 2068:

Before you start, you need to lay the groundwork. In fact, a global variable dictsys.row_id is maintained in innoDB. Tables that do not define a primary key will share this row_id. When inserting data, they will regard the global row_id as their own primary key, and then add 1 to the global variable.

Next we need to use the relevant technology of gdb debugging, gdb is a debugging tool under Linux, which can be used to debug executable files. On the server, first install through yum install gdb, and after the installation is complete, modify row_id to 1 with the following gdb command:

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

The result of the command execution:

Insert 3 rows of data into an empty table:

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

Looking at the data in the table, the corresponding _ rowid is theoretically 1: 3:

Then change row_id to the maximum value of 2 ^ 48 through the gdb command, which exceeds the maximum value of dictsys.row_id:

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

The result of the command execution:

Insert three more pieces of data into the table:

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

Looking at all the data in the table, you can see that of the three pieces of data inserted for the first time, two were overwritten:

Why is there data coverage? we analyze this result. First, before inserting data for the first time, _ rowid is 1, and the three inserted data correspond to _ rowid of 1, 2, and 3. As shown in the following figure:

When you manually set _ rowid to the maximum value, the next time you insert data, the inserted _ rowid starts at 0 again, so the _ rowid of the three pieces of data inserted the second time should be 0,1,2. The data to be inserted at this point is as follows:

When the same _ rowid occurs, the newly inserted data will overwrite the original data according to _ rowid, as shown in the figure:

So when the primary key or unique index in the table does not meet the requirements we mentioned earlier, the implicit _ rowid used by innoDB is risky. Although the value of 2 ^ 48 is very large, it is still possible to be exhausted. When _ rowid is exhausted, the previous record will be overwritten. From this point of view, we can also remind you that you must create a primary key when creating a table, otherwise data overwriting may occur.

At this point, the study on "specific ways to view hidden columns in MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.