Shulou(Shulou.com)05/31 Report--

What I want to share with you today is when the hidden field rowid of mysql is visible. I believe many people don't know much about it. In order to make you understand better, I summarized the following contents for you. Let's look down together. I'm sure you'll get something.

Through my own actual combat, I can be sure that as long as the table is created, the rowid must exist, and the only difference is the difference between the display and the hermit, that is, whether it can be queried through select _ rowid from table. In which cases is the rowid displayed? 1. It is displayed only when there is a primary key in the table and it is numeric. 2. When there is no primary key in the table, but there is only one non-empty and numeric table in the table, it is displayed. Next, create a table to see whether it is possible to first create a numerical table create table Z1 (id bigint (20) primary key) engine=innodb with a primary key.

Then create a table with a primary key that is not numeric (although the business will not be created this way, just to prove rowid) create table Z2 (name varchar (20) primary key) engine=innodb

Then create a table create table Z3 (name int (11) not null, unique (name)) engine=innodb charset=utf8 that does not have a primary key but has a unique key and is numerically non-empty

At this point, create a table create table Z4 (name varchar (11) not null, unique (name)) engine=innodb charset=utf8;create table Z5 (name int (11), unique (name)) engine=innodb charset=utf8 that has no primary key and has a unique key, but can be empty or not numeric.

Let's take a look at the official website, and then understand the official website connection: https://dev.mysql.com/doc/refman/5.7/en/create-index.htmlIf 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, as follows:_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.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. At this point, I create another table, which has only one field and is of the type of string. Take a look at the generated implicit rowid. What happens when it reaches the maximum? (note that the underlying layer will generate a 6-byte pointer by default, with a maximum of 2 ^ 48 powers.)

At this point, use the gdb tool to maximize rowid and then insert it to see what happens. Answer: first find the mysqld process pid, and command ps aux | grep mysqld gdb-p your mysql pid-ex'p dict_sys- > row_id=1'-batch

You can see that three pieces of data have been inserted at this time

After changing rowid to the power of 2 ^ 48, insert the effect gdb-p 29410-ex'p dict_sys- > row_id=281474976710656'-batch.

At this time, three more pieces of data are inserted, and A1 / A2 is overwritten, so when the implicit rowid generated is exhausted, the previous records will be overwritten, so the table must have a primary key id to avoid overwriting. Although the probability is relatively low, this is only one of the reasons for using the primary key.

So to sum up: look at my xmind summary, and then understand and digest it. About when the mysql hidden field rowid is visible is shared here, I hope that the above content can have a certain reference value for everyone, you can learn to apply. If you like this article, you might as well share it for more people to see.

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.