MySQL version is greater than 5.0, there is a default database Information_schema, which holds all the database information (such as table name, column name, corresponding permissions, etc. ), through this database, we can cross-Library query, explode the list.
To retrieve information from these views, specify the fully qualified information_schema view_name name.
Column Name |
Data Type |
Description |
Table_catalog |
nvarchar (128) |
Table qualifier. |
Table_schema |
nvarchar (128) |
The table owner. |
table_name |
nvarchar (128) |
The table name. |
column_name |
nvarchar (128) |
The column name. |
Ordinal_position |
smallint |
The column identification number. |
Column_default |
nvarchar (4000) |
The default value for the column. |
Is_nullable |
varchar (3) |
The nullability of the column. If the column allows NULL, the column returns YES. Otherwise, NO is returned. |
Data_type |
nvarchar (128) |
The type of data provided by the system. |
Character_maximum_length |
smallint |
The maximum length in characters, suitable for binary data, character data, or text and image data. Otherwise, NULL is returned. For more information, see Data types. |
Character_octet_length |
smallint |
The maximum length in bytes, suitable for binary data, character data, or text and image data. Otherwise, NULL is returned. |
Numeric_precision |
tinyint |
Accuracy of approximate numeric data, precise numeric data, integer data, or Currency data. Otherwise, NULL is returned. |
Numeric_precision_radix |
smallint |
The precision cardinality of approximate numeric data, precise numeric data, integer data, or Currency data. Otherwise, NULL is returned. |
Numeric_scale |
tinyint |
The number of decimal digits for approximate numeric data, exact numeric data, integer data, or Currency data. Otherwise, NULL is returned. |
Datetime_precision |
smallint |
The subtype code for datetime and SQL-92 interval data types. For other data types, returns NULL. |
Character_set_catalog |
varchar (6) |
If the column is character data or text data type, return master, indicating the database where the character set resides. Otherwise, NULL is returned. |
Character_set_schema |
varchar (3) |
If the column is character data or text data type, the DBOis returned, indicating the owner name of the character set. Otherwise, NULL is returned. |
Character_set_name |
nvarchar (128) |
If the column is character data or text data type, a unique name is returned for the character set. Otherwise, NULL is returned. |
Collation_catalog |
varchar (6) |
If the column is character data or text data type, returns master, indicating the database in which the sort order is defined. Otherwise, this column is NULL. |
Collation_schema |
varchar (3) |
Returns the DBO, which indicates the owner of the sort order for character data or text data types. Otherwise, NULL is returned. |
Collation_name |
nvarchar (128) |
If the column is character data or text data type, a unique name is returned for the sort order. Otherwise, NULL is returned. |
Domain_catalog |
nvarchar (128) |
If the column is a user-defined data type, the column is a database name that creates this user-defined data type in the database name. Otherwise, NULL is returned. |
Domain_schema |
nvarchar (128) |
If the column is a user-defined data type, the column is the creator of this user-defined data type. Otherwise, NULL is returned. |
Domain_Name |
nvarchar (128) |
If the column is a user-defined data type, the column is the name of this user-defined data type. Otherwise, NULL is returned. |
first introduced is the explosion library
select schema_name from INFORMATION_SCHEMA. schemata limit 5,1/*5,1 means starting from 1th, counting to 5th
select table_name from information_schema. tables where table_schema=0x6d656d626572 limit5,1/*table_schema= is followed by the 16 binary
re-explode field
select column_name from Information_schema. columns wheretable_name=0x61646d5f75736572 limit 5,1/*
All data is obtained from the information_schema.columns table, as we can see from the Information_schema this library , from information_ Schema.columns This table, we can find all the information, because it is in the inside,Table_schema, table_name,column_name This three columns have, so we can directly through this form, To find out all the information we need, we'll save the table. This step, further increase the speed
Mysql through the INFORMATION_SCHEMA, explode the table, explode the field