mysql获取所有空表,MySQL元数据操作：查询 MySQL 空表，拥有某字段的表，等

1、查询MySQL库下所有表名，数据为空的表

SELECT

table_name,

table_rows

FROM

information_schema. TABLES

WHERE

table_schema = 'ahbo'

AND table_rows < 1;

2、查询指定库拥有某字段的表

SELECT DISTINCT

TABLE_NAME

FROM

information_schema. COLUMNS

WHERE

COLUMN_NAME = 'columnName'

AND TABLE_SCHEMA = 'dbName'

AND TABLE_NAME NOT LIKE 'vw%';

3、修改指定数据库中所有varchar类型的表字段的字符集为UTF8，并将排序规则修改为utf8_general_ci

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET UTF8 COLLATE utf8_general_ci', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')

别名

FROM information_schema.COLUMNS

WHERE TABLE_SCHEMA = 'SchoolUserOnline_20170416'

AND DATA_TYPE = 'varchar'

AND

(

CHARACTER_SET_NAME != 'utf8'

OR

COLLATION_NAME != 'utf8_general_ci'

);

4、修改指定数据库中所有数据表的字符集为UTF8，并将排序规则修改为utf8_general_ci

SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;')

FROM information_schema.TABLES

WHERE TABLE_SCHEMA = 'databaseName'

标签：ci,空表,某字,NAME,utf8,MySQL,table,TABLE,schema

来源： https://www.cnblogs.com/erlongxizhu-03/p/14299368.html