COLUMNS

The COLUMNS table provides detailed information about columns in tables.

USE INFORMATION_SCHEMA;
DESC COLUMNS;

The output is as follows:

+--------------------------+---------------+------+------+---------+-------+
| Field                    | Type          | Null | Key  | Default | Extra |
+--------------------------+---------------+------+------+---------+-------+
| TABLE_CATALOG            | varchar(512)  | YES  |      | NULL    |       |
| TABLE_SCHEMA             | varchar(64)   | YES  |      | NULL    |       |
| TABLE_NAME               | varchar(64)   | YES  |      | NULL    |       |
| COLUMN_NAME              | varchar(64)   | YES  |      | NULL    |       |
| ORDINAL_POSITION         | bigint(64)    | YES  |      | NULL    |       |
| COLUMN_DEFAULT           | text          | YES  |      | NULL    |       |
| IS_NULLABLE              | varchar(3)    | YES  |      | NULL    |       |
| DATA_TYPE                | varchar(64)   | YES  |      | NULL    |       |
| CHARACTER_MAXIMUM_LENGTH | bigint(21)    | YES  |      | NULL    |       |
| CHARACTER_OCTET_LENGTH   | bigint(21)    | YES  |      | NULL    |       |
| NUMERIC_PRECISION        | bigint(21)    | YES  |      | NULL    |       |
| NUMERIC_SCALE            | bigint(21)    | YES  |      | NULL    |       |
| DATETIME_PRECISION       | bigint(21)    | YES  |      | NULL    |       |
| CHARACTER_SET_NAME       | varchar(32)   | YES  |      | NULL    |       |
| COLLATION_NAME           | varchar(32)   | YES  |      | NULL    |       |
| COLUMN_TYPE              | text          | YES  |      | NULL    |       |
| COLUMN_KEY               | varchar(3)    | YES  |      | NULL    |       |
| EXTRA                    | varchar(30)   | YES  |      | NULL    |       |
| PRIVILEGES               | varchar(80)   | YES  |      | NULL    |       |
| COLUMN_COMMENT           | varchar(1024) | YES  |      | NULL    |       |
| GENERATION_EXPRESSION    | text          | NO   |      | NULL    |       |
+--------------------------+---------------+------+------+---------+-------+
21 rows in set (0.00 sec)

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

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

The output is as follows:

*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: test
              TABLE_NAME: t1
             COLUMN_NAME: a
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 11
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: int(11)
              COLUMN_KEY:
                   EXTRA:
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:
   GENERATION_EXPRESSION:
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:

SHOW COLUMNS FROM t1 FROM test;

The output is as follows:

+-------+---------+------+------+---------+-------+
| Field | Type    | Null | Key  | Default | Extra |
+-------+---------+------+------+---------+-------+
| a     | int(11) | YES  |      | NULL    |       |
+-------+---------+------+------+---------+-------+
1 row in set (0.00 sec)