COLUMNS

The COLUMNS table provides detailed information about columns in tables.

  1. USE INFORMATION_SCHEMA;
  2. DESC COLUMNS;

The output is as follows:

  1. +--------------------------+---------------+------+------+---------+-------+
  2. | Field                    | Type          | Null | Key  | Default | Extra |
  3. +--------------------------+---------------+------+------+---------+-------+
  4. | TABLE_CATALOG            | varchar(512)  | YES  |      | NULL    |       |
  5. | TABLE_SCHEMA             | varchar(64)   | YES  |      | NULL    |       |
  6. | TABLE_NAME               | varchar(64)   | YES  |      | NULL    |       |
  7. | COLUMN_NAME              | varchar(64)   | YES  |      | NULL    |       |
  8. | ORDINAL_POSITION         | bigint(64)    | YES  |      | NULL    |       |
  9. | COLUMN_DEFAULT           | text          | YES  |      | NULL    |       |
  10. | IS_NULLABLE              | varchar(3)    | YES  |      | NULL    |       |
  11. | DATA_TYPE                | varchar(64)   | YES  |      | NULL    |       |
  12. | CHARACTER_MAXIMUM_LENGTH | bigint(21)    | YES  |      | NULL    |       |
  13. | CHARACTER_OCTET_LENGTH   | bigint(21)    | YES  |      | NULL    |       |
  14. | NUMERIC_PRECISION        | bigint(21)    | YES  |      | NULL    |       |
  15. | NUMERIC_SCALE            | bigint(21)    | YES  |      | NULL    |       |
  16. | DATETIME_PRECISION       | bigint(21)    | YES  |      | NULL    |       |
  17. | CHARACTER_SET_NAME       | varchar(32)   | YES  |      | NULL    |       |
  18. | COLLATION_NAME           | varchar(32)   | YES  |      | NULL    |       |
  19. | COLUMN_TYPE              | text          | YES  |      | NULL    |       |
  20. | COLUMN_KEY               | varchar(3)    | YES  |      | NULL    |       |
  21. | EXTRA                    | varchar(30)   | YES  |      | NULL    |       |
  22. | PRIVILEGES               | varchar(80)   | YES  |      | NULL    |       |
  23. | COLUMN_COMMENT           | varchar(1024) | YES  |      | NULL    |       |
  24. | GENERATION_EXPRESSION    | text          | NO   |      | NULL    |       |
  25. +--------------------------+---------------+------+------+---------+-------+
  26. 21 rows in set (0.00 sec)

Create a table test.t1 and query the information in the COLUMNS table:

  1. CREATE TABLE test.t1 (a int);
  2. SELECT * FROM COLUMNS WHERE table_schema='test' AND TABLE_NAME='t1'\G

The output is as follows:

  1. *************************** 1. row ***************************
  2.            TABLE_CATALOG: def
  3.             TABLE_SCHEMA: test
  4.               TABLE_NAME: t1
  5.              COLUMN_NAME: a
  6.         ORDINAL_POSITION: 1
  7.           COLUMN_DEFAULT: NULL
  8.              IS_NULLABLE: YES
  9.                DATA_TYPE: int
  10. CHARACTER_MAXIMUM_LENGTH: NULL
  11.   CHARACTER_OCTET_LENGTH: NULL
  12.        NUMERIC_PRECISION: 11
  13.            NUMERIC_SCALE: 0
  14.       DATETIME_PRECISION: NULL
  15.       CHARACTER_SET_NAME: NULL
  16.           COLLATION_NAME: NULL
  17.              COLUMN_TYPE: int(11)
  18.               COLUMN_KEY:
  19.                    EXTRA:
  20.               PRIVILEGES: select,insert,update,references
  21.           COLUMN_COMMENT:
  22.    GENERATION_EXPRESSION:
  23. 1 row in set (0.02 sec)

The description of columns in the COLUMNS table is as follows:

  • TABLE_CATALOG: The name of the catalog to which the table with the column belongs. The value is always def.
  • TABLE_SCHEMA: The name of the schema in which the table with the column is located.
  • TABLE_NAME: The name of the table with the column.
  • COLUMN_NAME: The name of the column.
  • ORDINAL_POSITION: The position of the column in the table.
  • COLUMN_DEFAULT: The default value of the column. If the explicit default value is NULL, or if the column definition does not include the default clause, this value is NULL.
  • IS_NULLABLE: Whether the column is nullable. If the column can store null values, this value is YES; otherwise, it is NO.
  • DATA_TYPE: The type of data in the column.
  • CHARACTER_MAXIMUM_LENGTH: For string columns, the maximum length in characters.
  • CHARACTER_OCTET_LENGTH: For string columns, the maximum length in bytes.
  • NUMERIC_PRECISION: The numeric precision of a number-type column.
  • NUMERIC_SCALE: The numeric scale of a number-type column.
  • DATETIME_PRECISION: For time-type columns, the fractional seconds precision.
  • CHARACTER_SET_NAME: The name of the character set of a string column.
  • COLLATION_NAME: The name of the collation of a string column.
  • COLUMN_TYPE: The column type.
  • COLUMN_KEY: Whether this column is indexed. This field might have the following values:
    • Empty: This column is not indexed, or this column is indexed and is the second column in a multi-column non-unique index.
    • PRI: This column is the primary key or one of multiple primary keys.
    • UNI: This column is the first column of the unique index.
    • MUL: The column is the first column of a non-unique index, in which a given value is allowed to occur for multiple times.
  • EXTRA: Any additional information of the given column.
  • PRIVILEGES: The privilege that the current user has on this column. Currently, this value is fixed in TiDB, and is always select,insert,update,references.
  • COLUMN_COMMENT: Comments contained in the column definition.
  • GENERATION_EXPRESSION: For generated columns, this value displays the expression used to calculate the column value. For non-generated columns, the value is empty.

The corresponding SHOW statement is as follows:

  1. SHOW COLUMNS FROM t1 FROM test;

The output is as follows:

  1. +-------+---------+------+------+---------+-------+
  2. | Field | Type    | Null | Key  | Default | Extra |
  3. +-------+---------+------+------+---------+-------+
  4. | a     | int(11) | YES  |      | NULL    |       |
  5. +-------+---------+------+------+---------+-------+
  6. 1 row in set (0.00 sec)

See also