--------------------------------------MYSQL批量修改字段类型---------------------------------

SELECT
CONCAT('alter table ',table_name,' ALTER COLUMN ',column_name,' SET DEFAULT NULL;') as col ,TABLE_NAME,COLUMN_NAME,DATA_TYPE
FROM
INFORMATION_SCHEMA.columns a
WHERE
TABLE_SCHEMA='TEST' -- 代表所在的数据库
AND
data_type in('varchar','char')
AND
NOT EXISTS(SELECT 1 FROM KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='TEST' AND CONSTRAINT_NAME='PRIMARY' AND TABLE_NAME=A.TABLE_NAME AND COLUMN_NAME=A.COLUMN_NAME)

MYSQL整来整去就是那些系统表，拼吧。

--- 下面是MYSQL查询所在数据不是主键的列

SELECT
TABLE_NAME, -- 表名

COLUMN_NAME, -- 字段名

DATA_TYPE -- 字段类型
FROM
`COLUMNS` A
WHERE
TABLE_SCHEMA='honghui2'
AND
NOT EXISTS(SELECT 1 FROM KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='honghui2' AND CONSTRAINT_NAME='PRIMARY' AND TABLE_NAME=A.TABLE_NAME AND COLUMN_NAME=A.COLUMN_NAME)