Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.1Mb
PDF (A4) - 31.1Mb
PDF (RPM) - 29.3Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb
HTML Download (RPM) - 6.2Mb
Man Pages (TGZ) - 176.3Kb
Man Pages (Zip) - 286.5Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  The INFORMATION_SCHEMA INNODB_SYS_COLUMNS Table

21.30.16 The INFORMATION_SCHEMA INNODB_SYS_COLUMNS Table

The INNODB_SYS_COLUMNS table provides metadata about InnoDB table columns, equivalent to the information from the SYS_COLUMNS table in the InnoDB data dictionary.

For related usage information and examples, see Section 14.15.3, “InnoDB INFORMATION_SCHEMA System Tables”.

The INNODB_SYS_COLUMNS table has these columns:

  • TABLE_ID

    An identifier representing the table associated with the column; the same value as INNODB_SYS_TABLES.TABLE_ID.

  • NAME

    The name of the column. These names can be uppercase or lowercase depending on the lower_case_table_names setting. There are no special system-reserved names for columns.

  • POS

    The ordinal position of the column within the table, starting from 0 and incrementing sequentially. When a column is dropped, the remaining columns are reordered so that the sequence has no gaps.

  • MTYPE

    Stands for main type. A numeric identifier for the column type. 1 = VARCHAR, 2 = CHAR, 3 = FIXBINARY, 4 = BINARY, 5 = BLOB, 6 = INT, 7 = SYS_CHILD, 8 = SYS, 9 = FLOAT, 10 = DOUBLE, 11 = DECIMAL, 12 = VARMYSQL, 13 = MYSQL.

  • PRTYPE

    The InnoDB precise type, a binary value with bits representing MySQL data type, character set code, and nullability.

  • LEN

    The column length, for example 4 for INT and 8 for BIGINT. For character columns in multibyte character sets, this length value is the maximum length in bytes needed to represent a definition such as VARCHAR(N); that is, it might be 2*N, 3*N, and so on depending on the character encoding.

Example

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where TABLE_ID = 71\G
*************************** 1. row ***************************
TABLE_ID: 71
    NAME: col1
     POS: 0
   MTYPE: 6
  PRTYPE: 1027
     LEN: 4
*************************** 2. row ***************************
TABLE_ID: 71
    NAME: col2
     POS: 1
   MTYPE: 2
  PRTYPE: 524542
     LEN: 10
*************************** 3. row ***************************
TABLE_ID: 71
    NAME: col3
     POS: 2
   MTYPE: 1
  PRTYPE: 524303
     LEN: 10

Notes

  • You must have the PROCESS privilege to query this table.

  • Use the INFORMATION_SCHEMA COLUMNS table or the SHOW COLUMNS statement to view additional information about the columns of this table, including data types and default values.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.