MySQL 增刪改查 (CRUD) 與 DDL 常用語句速查手冊

MySQL 增刪改查 (CRUD) 與 DDL 常用語句速查手冊

本文匯總 MySQL 日常開發運維中高頻使用的 DDL（結構變更）與 DML（數據操作）語句，含創建/刪除列、外鍵管理、索引操作、約束控制等，附帶實戰示例，適合作為桌邊速查手冊。

一、列操作

1. 新增列

ALTER TABLE tablename ADD colname type NOT NULL DEFAULT '0';

示例：ALTER TABLE mmanapp_mmanmedia ADD appid_id INTEGER NOT NULL DEFAULT 372;

2. 刪除列

ALTER TABLE tablename DROP COLUMN colname;

3. 修改列：重命名 / 變更類型

ALTER TABLE t1 CHANGE a b INTEGER;
ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
ALTER TABLE infos CHANGE list list TINYINT NOT NULL DEFAULT '0';

4. 重命名表

ALTER TABLE t1 RENAME t2;

二、索引操作

1. 創建普通索引

ALTER TABLE tablename ADD INDEX idx_name (col1[, col2 ...]);

2. 創建主鍵索引

ALTER TABLE tablename ADD PRIMARY KEY (id);

3. 創建唯一索引

ALTER TABLE tablename ADD UNIQUE uk_name (cardnumber);

4. 刪除索引

ALTER TABLE tablename DROP INDEX idx_name;

5. 自增主鍵一鍵創建

ALTER TABLE tabelname ADD new_field_id INT(5) UNSIGNED DEFAULT 0 NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (new_field_id);

三、外鍵約束管理

1. 添加外鍵

ALTER TABLE yourtablename 
ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) 
REFERENCES tbl_name (index_col_name, ...) 
[ON DELETE {RESTRICT|CASCADE|SET NULL|NO ACTION}] 
[ON UPDATE {RESTRICT|CASCADE|SET NULL|NO ACTION}];

示例：ALTER TABLE mmanapp_mmanmedia ADD CONSTRAINT fk_mdappid FOREIGN KEY(appid_id) REFERENCES app(id);

2. 刪除外鍵

ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;

需先用 SHOW CREATE TABLE 確認約束名（InnoDB 內部生成的名稱如 0_38775）。

3. 相關實用命令

• 查看表外鍵：SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';（Comment 列顯示）
• 導入轉儲文件前禁用外鍵檢查：SET FOREIGN_KEY_CHECKS = 0; 導入後 SET FOREIGN_KEY_CHECKS = 1;
• mysqldump 自動在輸出中包含禁用外鍵語句，便於任意順序導入。

四、數據操作 (DML) 基礎範例

-- 插入
INSERT INTO t (col1, col2) VALUES ('val1', 'val2');

-- 查詢
SELECT col1, col2 FROM t WHERE col1 = 'val1';

-- 更新
UPDATE t SET col2 = 'new_val' WHERE col1 = 'val1';

-- 刪除
DELETE FROM t WHERE col1 = 'val1';

五、避坑提示

• 修改列類型前確認數據兼容性，避免截斷或報錯。
• 外鍵引用列必須有索引，否則報錯 errno 150。
• 大表 DDL（加索引、改類型）建議使用 pt-online-schema-change 或原生 ALGORITHM=INPLACE 避免鎖表。
• 生產環境執行 DROP COLUMN / DROP FOREIGN KEY 前務必備份並驗證無業務依賴。

總結

熟練掌握上述語句，能顯著提升日常開發運維效率。建議收藏備查，並在測試環境充分演練後再上生產。