————————————–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)