ROWID in MySQL

Time:2019-11-8

Preface

stayOracleEach row of data in a database’s table has a unique identifier calledrowidInOracleIt is often used internally to access data.

While inMySQLThere is a similar hidden column in_rowidTo mark a unique identity. But we need to pay attention_rowidIt’s not a real column, its essence is aNon empty unique columnAlias.

PS: This article is based onMySQL 5.7Research

_What is ROWID

As mentioned earlier_rowidIt’s not a real column, its essence is aNon empty unique columnAlias. Why do you say that?

Because in some cases_rowidIt does not exist. It only exists in the following situations:

  1. When there is aNumeric typeWhen the single column primary key of,_rowidIn fact, it refers to the primary key column
  2. When in the tablePrimary key does not existBut there is oneNumeric typeOfNon empty unique columnAt that time,_rowidIn fact, it means correspondenceNon empty unique column

It should be noted that the following situations do not exist_rowidOf

  1. Primary key columnperhapsNon empty unique columnThe type of is notNumeric type
  2. Primary keyIs the union primary key
  3. OnlyColumn is not non empty.

For details, please refer toMySQLOfficial document content:

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

Reference material

  1. 13.1.14 CREATE INDEX Syntax
  2. Re: Oracle ROWID equivalent in MySQL