Mysql through the INFORMATION_SCHEMA, explode the table, explode the field

Source: Internet
Author: User
Tags explode mysql version

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.