數據庫與表基礎操作

創建/切換/刪除數據庫

CREATE DATABASE school CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE school;
DROP DATABASE school;

創建表（含常見約束）

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    sex CHAR(2) CHECK (sex IN ('男','女')),
    age INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    info TEXT,
    tel CHAR(11) UNIQUE,
    bak VARCHAR(500) DEFAULT '我是學生'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

查看表結構

DESC student;
SHOW CREATE TABLE student\G

字段/列操作 (ALTER TABLE)

1. 增加字段

ALTER TABLE student ADD COLUMN tel VARCHAR(20) AFTER name;
ALTER TABLE student ADD COLUMN score INT NOT NULL DEFAULT 0 FIRST;

2. 修改字段

-- 修改類型/默認值/註釋 (MODIFY)
ALTER TABLE student MODIFY age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年齡';

-- 修改字段名 (CHANGE 必須重複聲明完整定義)
ALTER TABLE student CHANGE sex gender CHAR(2) NOT NULL DEFAULT '男' COMMENT '性別';

3. 刪除字段

ALTER TABLE student DROP COLUMN bak;

索引操作

-- 普通索引
ALTER TABLE student ADD INDEX idx_name (name);
ALTER TABLE student ADD INDEX idx_class_age (class_id, age);

-- 唯一索引
ALTER TABLE student ADD UNIQUE INDEX uk_tel (tel);
ALTER TABLE student ADD CONSTRAINT uk_email UNIQUE (email);

-- 全文索引 (MySQL 5.6+ InnoDB)
ALTER TABLE article ADD FULLTEXT INDEX ft_content (content);

-- 刪除索引
ALTER TABLE student DROP INDEX idx_name;
ALTER TABLE student DROP PRIMARY KEY;  -- 主鍵單獨語法

約束管理

主鍵

-- 添加 (非自增)
ALTER TABLE grade ADD PRIMARY KEY (id);
-- 添加 (自增需先 MODIFY)
ALTER TABLE grade MODIFY id INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id);
-- 刪除 (自增需先去 AUTO_INCREMENT)
ALTER TABLE grade MODIFY id INT, DROP PRIMARY KEY;

外鍵

ALTER TABLE shop_xs 
ADD CONSTRAINT fk_xs_jb FOREIGN KEY (id) REFERENCES shop_jb(id),
ADD CONSTRAINT fk_xs_yw FOREIGN KEY (ywid) REFERENCES shop_yw(ywid);

檢查約束 (MySQL 8.0.16+ 生效)

ALTER TABLE grade ADD CONSTRAINT chk_sex CHECK (sex IN ('男','女'));
ALTER TABLE grade DROP CONSTRAINT chk_sex;

默認值

ALTER TABLE grade ALTER COLUMN bak SET DEFAULT '我是好學生';
ALTER TABLE grade ALTER COLUMN bak DROP DEFAULT;

數據操作進階

1. 表數據備份 (mysqldump)

mysqldump -u root -p database_name table_name > backup_file.sql

2. 導出數據到文件 (SELECT INTO OUTFILE)

SELECT cooperate_code, create_time 
FROM publish 
LIMIT 10 
INTO OUTFILE '/home/mzc/temp/tempbad.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

3. 從文件導入數據 (LOAD DATA INFILE)

LOAD DATA INFILE '/home/mzc/temp/tempbad.txt' 
INTO TABLE pad
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;  -- 跳過表頭

4. 跨表數據拼接插入

INSERT INTO tx (col1, col2)
SELECT t1.com1, CONCAT(t1.com2, t2.com1)
FROM t1, t2;  -- 隱式笛卡爾積，建議顯式 JOIN

5. 複雜條件刪除/更新

-- 刪除字段
ALTER TABLE form1 DROP COLUMN col_name;

-- 批量更新
UPDATE mybook SET publish_house = '新出版社' WHERE id > 100;

PHP 操作 MySQL 表結構變更示例

<?php
$mysqli = new mysqli($host, $user, $pass, $db);
if ($mysqli->connect_errno) die("Connect failed: " . $mysqli->connect_error);

$sql = "ALTER TABLE `article` ADD `keywords` VARCHAR(100) NOT NULL DEFAULT ''";
if ($mysqli->query($sql)) {
    echo "Add succeed";
} else {
    echo "Error: " . $mysqli->error;
}
$mysqli->close();
?>

生產環境 DDL 最佳實踐

1. MySQL 8.0+ 瞬時 DDL：ADD COLUMN、DROP INDEX 等支持 ALGORITHM=INSTANT, LOCK=NONE
2. 大表用 pt-online-schema-change：影子表+觸發器零停機變更
3. 業務低峰期執行：配合 SET SESSION sql_log_bin=0 避免主從延遲（謹慎）
4. 備份先行：DDL 前 mysqldump --no-data 備份結構
5. 驗證回滾：準備逆向 DDL 語句，驗證可回滾

總結

MySQL 表操作涵蓋 **DDL（結構）、DCL（權限/約束）、DML（數據）** 三大類。掌握 CREATE/ALTER/DROP 表與字段、**索引/主鍵/外鍵/檢查/默認** 約束管理、**mysqldump/SELECT INTO OUTFILE/LOAD DATA INFILE** 數據遷移三大核心技能，配合 **Instant DDL / pt-osc / 低峰變更 / 備份驗證** 生產級守則，可覆蓋從開發建表到核心業務架構演進的全生命週期需求。