-- 移除現有預設值
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;
-- 設定新預設值
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 'new_value';
-- 將 topic 表 cateId 預設值從 1 改為 2
ALTER TABLE topic ALTER COLUMN cateId DROP DEFAULT;
ALTER TABLE topic ALTER COLUMN cateId SET DEFAULT 2;
pt-online-schema-changeNOT NULL 且無顯式預設值,插入時會報錯而非用隱式預設值 (0/''/NULL)TIMESTAMP 欄位若無顯式 DEFAULT,隱式為 CURRENT_TIMESTAMP (僅第一個 TIMESTAMP 列)SHOW CREATE TABLE topic\G
-- 或
SELECT COLUMN_NAME, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'db' AND TABLE_NAME = 'topic' AND COLUMN_NAME = 'cateId';
SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ALTER COLUMN ', COLUMN_NAME,
' SET DEFAULT ', QUOTE('new_default'), ';')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'db'
AND COLUMN_DEFAULT IS NOT NULL
AND COLUMN_NAME IN ('status','type','category');
**一句話:DROP DEFAULT → SET DEFAULT,元數據操作,秒級生效。**