What is the method to view the table structure in mysql

mysql view table structure

1. show create table

show create table 表名称

This statement can be thought of every time. The semantics are easy to remember. You can directly view the table creation statement, but it is not convenient for code processing. .

2. desc

desc 表名称

This statement is simple and easy to use, and can obtain the more important fields in COLUMNS: name, type, whether it is empty, key, default value, and additional information. But I can't get the field's comments.

3. information_schema.COLUMNS

select * from information_schema.COLUMNS where TABLE_SCHEMA = '库名称' and TABLE_NAME = '表名称'

Compared with show create table, it is relatively simple and has comprehensive information. You can query the name, type, key, permissions, comments and other information of the fields. In order to get the field name, I use this as a method to get the table structure in my script.

Attachment: information_schema.COLUMNS fields and meanings

Field names and meanings

• ##TABLE_CATALOGTable Type (I don’t understand what it is used for?)

• TABLE_SCHEMAName of the library it belongs to

• TABLE_NAMETable name

• COLUMN_NAMEField name

• ORDINAL_POSITIONPosition number

• COLUMN_DEFAULTDefault value

• ##IS_NULLABLE

  Whether it can be empty

• DATA_TYPE

  Data type

• CHARACTER_MAXIMUM_LENGTH

  Maximum length of string (numeric type is empty)

• CHARACTER_OCTET_LENGTH

  Maximum storage length of string (generally the same as the previous field)

• NUMERIC_PRECISION

  Numeric precision (non-numeric types are empty)

• NUMERIC_SCALE

  The number of decimal places for numeric values ​​(null for non-numeric types)

##DATETIME_PRECISION
• Date precision

CHARACTER_SET_NAME
• Encoding method

COLLATION_NAME
• Sort method

COLUMN_TYPE
• Field type

COLUMN_KEY
• Key involved in the field (primary key, unique key, etc.)

EXTRA
• Others (such as auto_increment)
  ##PRIVILEGES
Permissions

• COLUMN_COMMENT
Field comments

• GENERATION_EXPRESSION
Representing expression (I don’t understand, can mysql table inheritance?)

• Get all table structures and comments

Export all table information based on the library name

SELECT
    *
FROM
    information_schema.`TABLES`
WHERE
    TABLE_SCHEMA = 'db_name'

Export all table names and table comments based on the library name

SELECT
    TABLE_NAME,
    TABLE_COMMENT
FROM
    information_schema.`TABLES`
WHERE
    TABLE_SCHEMA = 'db_name';

mysql Get the entire All tables in the library and table structures

SELECT
    TABLE_SCHEMA AS '库名',
    TABLE_NAME AS '表名',
    COLUMN_NAME AS '列名',
    ORDINAL_POSITION AS '列的排列顺序',
    COLUMN_DEFAULT AS '默认值',
    IS_NULLABLE AS '是否为空',
    DATA_TYPE AS '数据类型',
    CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
    NUMERIC_PRECISION AS '数值精度(最大位数)',
    NUMERIC_SCALE AS '小数精度',
    COLUMN_TYPE AS '列类型',
    COLUMN_KEY 'KEY',
    EXTRA AS '额外说明',
    COLUMN_COMMENT AS '注释'
FROM
    information_schema.`COLUMNS`
WHERE
    TABLE_SCHEMA = 'db_name'
ORDER BY
    TABLE_NAME,
    ORDINAL_POSITION;

The above is the detailed content of What is the method to view the table structure in mysql. For more information, please follow other related articles on the PHP Chinese website!