MySQL INFORMATION_SCHEMA database introduction:

Source: Internet
Author: User

1, character_sets table create temporary table ' character_sets ' (' character_set_name ' varchar ( +) Not NULL DEFAULT"', the name of the character set, such as set names UTF8; ' default_collate_name ' varchar ( +) Not NULL DEFAULT"', the full name of the character set ' DESCRIPTION ' varchar ( -) Not NULL DEFAULT"', the description of the field ' MaxLen ' bigint (3) Not NULL DEFAULT'0'the length of the character set, for example UTF8 is 3 bytes) ENGINE=memory DEFAULT charset=The UTF8 is stored in a MySQL-supported character set. 2, COLUMNS table, stores all column information create temporary table ' COLUMNS ' (' table_catalog ' varchar ( +) Not NULL DEFAULT"', ' table_schema ' varchar ( -) Not NULL DEFAULT"', database name ' table_name ' varchar ( -) Not NULL DEFAULT"', table name ' column_name ' varchar ( -) Not NULL DEFAULT"', column name ' Ordinal_position ' bigint ( +) Unsigned not NULL DEFAULT'0', ' Column_default ' longtext, ' is_nullable ' varchar (3) Not NULL DEFAULT"', whether it is a null ' data_type ' varchar ( -) Not NULL DEFAULT"', field data type ' Character_maximum_length ' bigint ( +) unsigned DEFAULT NULL, ' character_octet_length ' bigint ( +) unsigned DEFAULT NULL, ' numeric_precision ' bigint ( +) unsigned DEFAULT NULL, ' Numeric_scale ' bigint ( +) unsigned DEFAULT NULL, ' datetime_precision ' bigint ( +) unsigned DEFAULT NULL, ' character_set_name ' varchar ( +) DEFAULT NULL, the encoding of the field ' collation_name ' varchar ( +) DEFAULT NULL, the full name of the encoded ' Column_type ' longtext not NULL, field-specific type ' column_key ' varchar (3) Not NULL DEFAULT"', ' EXTRA ' varchar ( -) Not NULL DEFAULT"', ' privileges ' varchar ( the) Not NULL DEFAULT"', you can perform operations on the field, such as select,insert,update ' column_comment ' varchar (1024x768) Not NULL DEFAULT"'field of your notes) ENGINE=myisam DEFAULT charset=UTF8 records information about all the fields inside each table3, ENGINES, Database engine table create temporary table ' ENGINES ' (' engine ' varchar ( -) Not NULL DEFAULT"', the database engine name ' support ' varchar (8) Not NULL DEFAULT"', does it support ' COMMENT ' varchar ( the) Not NULL DEFAULT"', annotated ' transactions ' varchar (3DEFAULT NULL, whether to support the thing ' XA ' varchar (3) DEFAULT NULL, whether XA distributed things ' savepoints ' varchar is supported (3) DEFAULT NULL) ENGINE=memory DEFAULT charset=UTF8 stored the data to the engine support situation4, the Global_status table, which stores some global states, is the source of the show status results. CREATE temporary TABLE ' global_status ' (' variable_name ' varchar (as opposed to session level) -) Not NULL DEFAULT"', variable name ' variable_value ' varchar (2048default NULL variable value) ENGINE=memory DEFAULT charset=UTF8 can query some of the database related states. 5, Global_variables table, global variable name table (relative to session level) CREATE temporary table ' global_variables ' (' variable_name ' varchar (  -) Not NULL DEFAULT"', variable name ' variable_value ' varchar (2048default NULL variable value) ENGINE=memory DEFAULT charset=UTF8 stores the variables of the database, the MySQL client's setGlobalVariabe, is the operation of this table6, Processlist table, current process table, show processlist data source create temporary table ' processlist ' (' ID ' bigint ( +) Unsigned not NULL DEFAULT'0', ' USER ' varchar ( -) Not NULL DEFAULT"', the user name of the connection ' HOST ' varchar ( -) Not NULL DEFAULT"', host address and Port ' DB ' varchar ( -) DEFAULT NULL, connected database ' COMMAND ' varchar ( -) Not NULL DEFAULT"', execute the command ' time 'int(7) Not NULL DEFAULT'0', time-consuming ' state ' varchar ( -) DEFAULT NULL, State ' info ' longtext, information ' Time_ms ' bigint ( +) Not NULL DEFAULT'0', ' rows_sent ' bigint ( +) Unsigned not NULL DEFAULT'0', ' rows_examined ' bigint ( +) Unsigned not NULL DEFAULT'0') ENGINE=myisam DEFAULT charset=UTF87, PROFILING table, Performance profile table for SQL statements, such as executing a SELECT statement, some column information returned by the client create temporary table ' PROFILING ' (' query_id 'int( -) Not NULL DEFAULT'0', ' SEQ 'int( -) Not NULL DEFAULT'0', ' state ' varchar ( -) Not NULL DEFAULT"', ' DURATION 'decimal(9,6) Not NULL DEFAULT'0.000000', ' Cpu_user 'decimal(9,6) DEFAULT NULL, ' Cpu_system 'decimal(9,6) DEFAULT NULL, ' context_voluntary 'int( -) DEFAULT NULL, ' context_involuntary 'int( -) DEFAULT NULL, ' block_ops_in 'int( -) DEFAULT NULL, ' block_ops_out 'int( -) DEFAULT NULL, ' messages_sent 'int( -) DEFAULT NULL, ' messages_received 'int( -) DEFAULT NULL, ' page_faults_major 'int( -) DEFAULT NULL, ' Page_faults_minor 'int( -) DEFAULT NULL, ' SWAPS 'int( -) DEFAULT NULL, ' source_function ' varchar ( -) DEFAULT NULL, ' source_file ' varchar ( -) DEFAULT NULL, ' Source_line 'int( -) DEFAULT NULL) ENGINE=memory DEFAULT charset=UTF88, schema_privileges Database Permissions table, which records what permissions the account has on the data create temporary table ' schema_privileges ' (' GRANTEE ' varchar (Bayi) Not NULL DEFAULT"', the user @host ' table_catalog ' varchar ( +) Not NULL DEFAULT"', ' table_schema ' varchar ( -) Not NULL DEFAULT"', database name ' privilege_type ' varchar ( -) Not NULL DEFAULT"', permissions, such as insert ' is_grantable ' varchar (3) Not NULL DEFAULT"') ENGINE=memory DEFAULT charset=UTF89, Session_status,session_variables, with the upper Global_status,global_variables, is the SESSION-level variableTen, Statistics table, statistics about the index. CREATE temporary TABLE ' STATISTICS ' (' table_catalog ' varchar ( +) Not NULL DEFAULT"', ' table_schema ' varchar ( -) Not NULL DEFAULT"', database ' table_name ' varchar ( -) Not NULL DEFAULT"', table name ' Non_unique ' bigint (1) Not NULL DEFAULT'0', whether a unique index, 0 means yes, 1 is not ' index_schema ' varchar ( -) Not NULL DEFAULT"', ' index_name ' varchar ( -) Not NULL DEFAULT"', index name ' Seq_in_index ' bigint (2) Not NULL DEFAULT'0', ' column_name ' varchar ( -) Not NULL DEFAULT"', column name ' COLLATION ' varchar (1) DEFAULT NULL, ' cardinality ' bigint ( +) DEFAULT NULL, ' Sub_part ' bigint (3) DEFAULT NULL, ' PACKED ' varchar (Ten) DEFAULT NULL, ' NULLABLE ' varchar (3) Not NULL DEFAULT"', ' index_type ' varchar ( -) Not NULL DEFAULT"', index type, such as btree ' COMMENT ' varchar ( -) DEFAULT NULL, ' index_comment ' varchar (1024x768) Not NULL DEFAULT"') ENGINE=memory DEFAULT charset=UTF8 One, tables table, storage of table-related information. CREATE temporary TABLE ' TABLES ' (' table_catalog ' varchar ( +) Not NULL DEFAULT"', ' table_schema ' varchar ( -) Not NULL DEFAULT"', the database where the table resides ' table_name ' varchar ( -) Not NULL DEFAULT"', table name ' table_type ' varchar ( -) Not NULL DEFAULT"', ' ENGINE ' varchar ( -) DEFAULT NULL, table engine ' VERSION ' bigint ( +) unsigned DEFAULT NULL, ' row_format ' varchar ( -) DEFAULT NULL, ' table_rows ' bigint ( +) unsigned DEFAULT NULL, ' avg_row_length ' bigint ( +) unsigned DEFAULT NULL, ' data_length ' bigint ( +) unsigned DEFAULT NULL, ' max_data_length ' bigint ( +) unsigned DEFAULT NULL, ' index_length ' bigint ( +) unsigned DEFAULT NULL, ' Data_free ' bigint ( +) unsigned DEFAULT NULL, ' auto_increment ' bigint ( +) unsigned default null, ' Create_time ' datetime default NULL, ' Update_time ' datetime default NULL, ' Check_time ' dat ETime DEFAULT NULL, ' table_collation ' varchar ( +) DEFAULT NULL, ' CHECKSUM ' bigint ( +) unsigned DEFAULT NULL, ' create_options ' varchar (255) DEFAULT NULL, ' table_comment ' varchar (2048) Not NULL DEFAULT"') ENGINE=memory DEFAULT Charset=utf8


can be different in different beta environment, user diff database and table, resulting in SQL statements, synchronization database,
The next step is to write a script about the database table structure synchronization.

MySQL INFORMATION_SCHEMA database introduction:

Related Article

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.