Mysql-information_schema Columns Table

In the program, if you want to dynamically get the specific information of a table, you use the INFORMATION_SCHEMA information database in MySQL, and it contains many tables, see below:

INFORMATION_SCHEMA SCHEMATA表INFORMATION_SCHEMA TABLES表INFORMATION_SCHEMA COLUMNS表INFORMATION_SCHEMA STATISTICS表INFORMATION_SCHEMA USER_PRIVILEGES表INFORMATION_SCHEMA SCHEMA_PRIVILEGES表INFORMATION_SCHEMA TABLE_PRIVILEGES表INFORMATION_SCHEMA COLUMN_PRIVILEGES表INFORMATION_SCHEMA CHARACTER_SETS表INFORMATION_SCHEMA COLLATIONS表INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY表INFORMATION_SCHEMA TABLE_CONSTRAINTS表INFORMATION_SCHEMA KEY_COLUMN_USAGE表INFORMATION_SCHEMA ROUTINES表INFORMATION_SCHEMA VIEWS表INFORMATION_SCHEMA TRIGGERS表

The COLUMNS table gives specific column information in a table: we create a ' documents ' table in the ' Test ' library with the following statement:

CREATE TABLE `documents` (                                         `id` int(11) NOT NULL auto_increment,                            `group_id` int(11) NOT NULL,                                     `group_id2` int(11) NOT NULL,                                    `date_added` datetime NOT NULL,                                  `title` varchar(255) NOT NULL,                                   `content` text NOT NULL,                                         PRIMARY KEY(`id`)                                            ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=gbk

Fields and index information are:

/*字段信息 - test.documents*/-----------------------------Field       Type          Collation       NullKeyDefaultExtra           Privileges Comment----------  ------------  --------------  ------  ------  -------  --------------  -------------------------------  -------id          int(11)       (NULL)          NOPRI     (NULL)   auto_increment  select,insert,update,referencesgroup_id    int(11)       (NULL)          NO select,insert,update,referencesgroup_id2   int(11)       (NULL)          NOselect,insert,update,referencesdate_added  datetime      (NULL)          NO select,insert,update,referencestitle       varchar(255)  gbk_chinese_ci  NO select,insert,update,referencescontent     text          gbk_chinese_ci  NOselect,insert,update,references/*索引信息 - test.documents*/-----------------------------Table Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  NullIndex_type  Comment---------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------documents           0  PRIMARY 1  id           A                    4    (NULL)  (NULL)          BTREE

All right! Build the table to the end! First look at Information_schema COLUMNS table:

Here we begin to understand it! Type the SQL statement:

SELECTCOLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT  FROM INFORMATION_SCHEMA.COLUMNS  WHEREtable_name = ‘documents‘

As a result, you can compare the build statement and the field information we just started with:

Of course, you can also see the query information for other fields by referencing the SQL statement in the following format:

select column_name, Data_ TYPE, is_nullable, Column_default    From information_schema. COLUMNS    where table_name = ' Tbl_name '    [ and table_schema = ' db_name ' ]    [ and column_name like ' Wild '

Next look at the SHOW statement and its similarities and differences: Type the SQL statement:

SHOW COLUMNS  FROM`documents`

The results are as follows:

The basic format of the SHOW statement is:

SHOW COLUMNS  FROMtbl_name  [FROM db_name]  [LIKEwild]

For more detailed information, please refer to the MySQL manual.

Mysql-information_schema Columns Table