MySQL: Concept: Information Schema

6th December 2021 at 4:31pm

MySQL 中有一个内置的 schema,名叫 information_schema,里面存储了各类元信息。

在做需求时有一个场景,是获取一个表的列的类型,可以用 information_schema.COLUMNS。它本质上是个 view。

对于这样一个表:

CREATE TABLE `target_db`.`multi_type_column_tab`  
(  
    `id` int       NOT NULL AUTO_INCREMENT,  
    `double_value` double          DEFAULT NULL,  
    `decimal_value` decimal(10, 3)  DEFAULT NULL,  
    `date_value` date            DEFAULT NULL,  
    `time_value` time            DEFAULT NULL,  
    `datetime_value` datetime        DEFAULT NULL,  
    `timestamp_value` timestamp NULL  DEFAULT NULL,  
    `year_value` year            DEFAULT NULL,  
    `varchar_value` varchar(100)    DEFAULT NULL,  
    `varbinary_value` varbinary(100)  DEFAULT NULL,  
    `signed_int_value` int             DEFAULT NULL,  
    `unsigned_int_value` int unsigned    DEFAULT NULL,  
    `signed_bigint_value` bigint          DEFAULT NULL,  
    `unsigned_bigint_value` bigint unsigned DEFAULT NULL,  
    `bit_value` bit(20)         DEFAULT NULL,  
    `bool_value` tinyint(1)      DEFAULT NULL,  
    PRIMARY KEY (`id`)  
) DEFAULT CHARSET = utf8mb4;

其对应的 infromation_schema.COLUMNS 条目如下 :

TABLE_CATALOGTABLE_SCHEMATABLE_NAMECOLUMN_NAMEORDINAL_POSITIONCOLUMN_DEFAULTIS_NULLABLEDATA_TYPECHARACTER_MAXIMUM_LENGTHCHARACTER_OCTET_LENGTHNUMERIC_PRECISIONNUMERIC_SCALEDATETIME_PRECISIONCHARACTER_SET_NAMECOLLATION_NAMECOLUMN_TYPECOLUMN_KEYEXTRAPRIVILEGESCOLUMN_COMMENTGENERATION_EXPRESSIONSRS_ID
deftarget_dbmulti_type_column_tabid1NULLNOintNULLNULL100NULLNULLNULLintPRIauto_incrementselect,insert,update,referencesNULL
deftarget_dbmulti_type_column_tabdouble_value2NULLYESdoubleNULLNULL22NULLNULLNULLNULLdoubleselect,insert,update,referencesNULL
deftarget_dbmulti_type_column_tabdecimal_value3NULLYESdecimalNULLNULL103NULLNULLNULLdecimal(10,3)select,insert,update,referencesNULL
deftarget_dbmulti_type_column_tabdate_value4NULLYESdateNULLNULLNULLNULLNULLNULLNULLdateselect,insert,update,referencesNULL
deftarget_dbmulti_type_column_tabtime_value5NULLYEStimeNULLNULLNULLNULL0NULLNULLtimeselect,insert,update,referencesNULL
deftarget_dbmulti_type_column_tabdatetime_value6NULLYESdatetimeNULLNULLNULLNULL0NULLNULLdatetimeselect,insert,update,referencesNULL
deftarget_dbmulti_type_column_tabtimestamp_value7NULLYEStimestampNULLNULLNULLNULL0NULLNULLtimestampselect,insert,update,referencesNULL
deftarget_dbmulti_type_column_tabyear_value8NULLYESyearNULLNULLNULLNULLNULLNULLNULLyearselect,insert,update,referencesNULL
deftarget_dbmulti_type_column_tabvarchar_value9NULLYESvarchar100400NULLNULLNULLutf8mb4utf8mb4_0900_ai_civarchar(100)select,insert,update,referencesNULL
deftarget_dbmulti_type_column_tabvarbinary_value10NULLYESvarbinary100100NULLNULLNULLNULLNULLvarbinary(100)select,insert,update,referencesNULL
deftarget_dbmulti_type_column_tabsigned_int_value11NULLYESintNULLNULL100NULLNULLNULLintselect,insert,update,referencesNULL
deftarget_dbmulti_type_column_tabunsigned_int_value12NULLYESintNULLNULL100NULLNULLNULLint unsignedselect,insert,update,referencesNULL
deftarget_dbmulti_type_column_tabsigned_bigint_value13NULLYESbigintNULLNULL190NULLNULLNULLbigintselect,insert,update,referencesNULL
deftarget_dbmulti_type_column_tabunsigned_bigint_value14NULLYESbigintNULLNULL200NULLNULLNULLbigint unsignedselect,insert,update,referencesNULL
deftarget_dbmulti_type_column_tabbit_value15NULLYESbitNULLNULL20NULLNULLNULLNULLbit(20)select,insert,update,referencesNULL
deftarget_dbmulti_type_column_tabbool_value16NULLYEStinyintNULLNULL30NULLNULLNULLtinyint(1)select,insert,update,referencesNULL