(资料图片仅供参考)

一、MySQL默认值相关查询1. 列出 MySQL 数据库中的表默认值

select table_schema as database_name,       table_name,       column_name,       column_defaultfrom information_schema.columnswhere  column_default is not null      and table_schema not in ("information_schema", "sys",                               "performance_schema","mysql")--    and table_schema = "your database name"order by table_schema,         table_name,         ordinal_position;

说明：

database_name- 数据库的名称（模式）table_name- 表的名称column_name- 列的名称column_default- 定义此列默认值的 SQL 表达式2. MySQL数据库默认值汇总

select column_default,       count(distinct concat(col.table_schema, ".", col.table_name)) as tables,       count(column_name) as columnsfrom information_schema.columns coljoin information_schema.tables tab on col.table_schema = tab.table_schema                                   and col.table_name = tab.table_namewhere col.table_schema not in ("sys", "information_schema",                                "mysql", "performance_schema")      and tab.table_type = "BASE TABLE"group by column_defaultorder by tables desc,         columns desc;

说明：

column_default-为没有默认值的列定义默认约束（公式）和NULLtables- 具有此约束的表数（或具有对NULL行没有约束的列的表数）columns- 具有此特定约束的列数（或NULL行没有约束）二、约束1. 列出了数据库（模式）中的所有不可为空的列

select tab.table_schema as database_name,    tab.table_name,    col.ordinal_position as column_id,    col.column_name,    col.data_type,    case when col.numeric_precision is not null            then col.numeric_precision        else col.character_maximum_length end as max_length,    case when col.datetime_precision is not null            then col.datetime_precision        when col.numeric_scale is not null            then col.numeric_scale        else 0 end as "precision"from information_schema.tables as tabjoin information_schema.columns as col        on col.table_schema = tab.table_schema        and col.table_name = tab.table_name        and col.is_nullable = "no"where tab.table_schema not in ("information_schema", "sys",                                "mysql","performance_schema")      and tab.table_type = "BASE TABLE"      -- and tab.table_schema = "database name"order by tab.table_schema,         tab.table_name,         col.ordinal_position;

注意：如果您需要特定数据库（模式）的信息，请取消注释 where 子句中的条件并提供您的数据库名称。

说明：

database_name- 数据库的名称（模式）table_name- 表的名称column_id- 列在表中的位置column_name- 列的名称data_type- 列数据类型max_length- 数据类型的最大长度precision- 数据类型的精度2. 检查 MySQL 数据库中的列是否可为空

select c.table_schema as database_name,       c.table_name,       c.column_name,       case c.is_nullable            when "NO" then "not nullable"            when "YES" then "is nullable"       end as nullablefrom information_schema.columns cjoin information_schema.tables t     on c.table_schema = t.table_schema      and c.table_name = t.table_namewhere c.table_schema not in ("mysql", "sys", "information_schema",                             "performance_schema")      and t.table_type = "BASE TABLE"      -- and t.table_schema = "database_name" -- put your database name hereorder by t.table_schema,         t.table_name,         c.column_name;

说明：

database_name- 数据库名称（模式）table_name- 表名column_name- 列名nullable- 列的可空性属性：is nullable- 可以为空，not nullable- 不可为空

关键词： 数据类型 数据库名称 最大长度