ALTER TABLE 表名
[操作類型] 列名/索引名/約束名
[數(shù)據(jù)類型] [約束條件]
[FIRST | AFTER 列名]

sql
-- 基本語法：添加新列
ALTER TABLE employees 
ADD COLUMN email VARCHAR(100) NOT NULL;

-- 添加多列
ALTER TABLE employees
ADD COLUMN phone VARCHAR(20),
ADD COLUMN department VARCHAR(50) DEFAULT '未分配';

-- 指定位置添加列
ALTER TABLE employees
ADD COLUMN middle_name VARCHAR(50) AFTER first_name;

-- 添加到第一列
ALTER TABLE employees
ADD COLUMN employee_code VARCHAR(20) FIRST;

sql
-- MODIFY：修改列定義（不改名）
ALTER TABLE employees
MODIFY COLUMN email VARCHAR(150) UNIQUE;

-- 修改數(shù)據(jù)類型和約束
ALTER TABLE products
MODIFY price DECIMAL(10,2) NOT NULL DEFAULT 0.00;

-- CHANGE：修改列名和定義
ALTER TABLE employees
CHANGE COLUMN old_name new_name VARCHAR(100);

-- 同時(shí)修改列名、類型和約束
ALTER TABLE employees
CHANGE COLUMN phone mobile_phone VARCHAR(15) NOT NULL;

-- 修改列位置
ALTER TABLE employees
MODIFY COLUMN department VARCHAR(50) AFTER email;

sql
-- 刪除單列
ALTER TABLE employees
DROP COLUMN temp_column;

-- 刪除多列
ALTER TABLE employees
DROP COLUMN column1,
DROP COLUMN column2;

-- 安全刪除：先檢查是否存在
SET @database_name = DATABASE();
SET @table_name = 'employees';
SET @column_name = 'old_column';

SET @sql = IF(
    EXISTS(
        SELECT * FROM information_schema.COLUMNS 
        WHERE TABLE_SCHEMA = @database_name 
        AND TABLE_NAME = @table_name 
        AND COLUMN_NAME = @column_name
    ),
    CONCAT('ALTER TABLE ', @table_name, ' DROP COLUMN ', @column_name),
    'SELECT "列不存在" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;

sql
-- 重命名單表
ALTER TABLE old_table_name 
RENAME TO new_table_name;

-- 或使用RENAME TABLE命令
RENAME TABLE old_name TO new_name;

-- 重命名多表（原子操作）
RENAME TABLE 
    table1 TO new_table1,
    table2 TO new_table2,
    table3 TO new_table3;

-- 移動(dòng)到其他數(shù)據(jù)庫
ALTER TABLE current_db.table_name 
RENAME TO other_db.table_name;

sql
-- 修改存儲(chǔ)引擎
ALTER TABLE users ENGINE = InnoDB;

-- 修改字符集
ALTER TABLE users 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 修改自增起始值
ALTER TABLE orders AUTO_INCREMENT = 1000;

-- 修改行格式
ALTER TABLE logs ROW_FORMAT = DYNAMIC;

-- 修改表注釋
ALTER TABLE users COMMENT = '用戶信息主表';

-- 修改表壓縮方式
ALTER TABLE archive_data 
ROW_FORMAT=COMPRESSED 
KEY_BLOCK_SIZE=8;

sql
-- 添加索引
ALTER TABLE products
ADD INDEX idx_category (category_id);

-- 添加唯一索引
ALTER TABLE users
ADD UNIQUE INDEX idx_email (email);

-- 添加全文索引
ALTER TABLE articles
ADD FULLTEXT INDEX idx_content (title, content);

-- 添加外鍵約束
ALTER TABLE orders
ADD CONSTRAINT fk_user_id 
FOREIGN KEY (user_id) REFERENCES users(id);

-- 刪除索引
ALTER TABLE products
DROP INDEX idx_category;

-- 刪除外鍵
ALTER TABLE orders
DROP FOREIGN KEY fk_user_id;

-- 禁用/啟用鍵
ALTER TABLE large_table DISABLE KEYS;
-- 執(zhí)行大量插入操作...
ALTER TABLE large_table ENABLE KEYS;

sql
-- 添加分區(qū)
ALTER TABLE sales
ADD PARTITION (
    PARTITION p2024_04 VALUES LESS THAN (202405)
);

-- 刪除分區(qū)（數(shù)據(jù)會(huì)丟失?。?
ALTER TABLE sales
DROP PARTITION p2023_01;

-- 重組分區(qū)
ALTER TABLE sales
REORGANIZE PARTITION p_future INTO (
    PARTITION p2024_05 VALUES LESS THAN (202406),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 合并分區(qū)
ALTER TABLE sales
COALESCE PARTITION 4;

-- 重建分區(qū)（優(yōu)化）
ALTER TABLE sales
REBUILD PARTITION p2024_01;

sql
-- 使用ALGORITHM指定算法
ALTER TABLE large_table 
ADD COLUMN new_column INT,
ALGORITHM = INPLACE,      -- 在線修改
LOCK = NONE;              -- 不加鎖

-- 修改多個(gè)屬性
ALTER TABLE employees
CHANGE COLUMN name full_name VARCHAR(100) NOT NULL,
MODIFY COLUMN age TINYINT UNSIGNED,
ADD COLUMN nickname VARCHAR(50),
DROP COLUMN old_column,
ADD INDEX idx_full_name (full_name);

-- 條件修改（MySQL 8.0+）
ALTER TABLE users 
ADD COLUMN IF NOT EXISTS 
last_login DATETIME DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE users 
DROP COLUMN IF EXISTS 
old_password;

sql
-- 基本刪除
DROP TABLE table_name;

-- 安全刪除（推薦）
DROP TABLE IF EXISTS table_name;

-- 刪除多表
DROP TABLE table1, table2, table3;

-- 刪除多表（安全版）
DROP TABLE IF EXISTS table1, table2, table3;

sql
-- 1. 確認(rèn)表存在
SHOW TABLES LIKE 'table_to_drop';

-- 2. 查看表結(jié)構(gòu)和數(shù)據(jù)量
DESCRIBE table_to_drop;
SELECT COUNT(*) FROM table_to_drop;

-- 3. 檢查外鍵依賴
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'table_to_drop';

-- 4. 備份重要數(shù)據(jù)
-- 使用mysqldump或其他工具備份

sql
-- 查看外鍵約束
SHOW CREATE TABLE orders;

-- 刪除有外鍵引用的表（方法1：先刪除外鍵）
ALTER TABLE child_table DROP FOREIGN KEY fk_name;
DROP TABLE parent_table;

-- 方法2：使用CASCADE（小心?。?
-- 這會(huì)刪除所有引用該表的子表數(shù)據(jù)
DROP TABLE parent_table CASCADE;

-- 方法3：臨時(shí)禁用外鍵檢查
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE table_name;
SET FOREIGN_KEY_CHECKS = 1;

sql
-- 刪除指定前綴的表
SELECT CONCAT('DROP TABLE IF EXISTS `', TABLE_NAME, '`;') 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME LIKE 'temp_%';

-- 刪除指定后綴的表
SELECT CONCAT('DROP TABLE IF EXISTS `', TABLE_NAME, '`;') 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME LIKE '%_backup';

-- 刪除空表
SELECT CONCAT('DROP TABLE IF EXISTS `', TABLE_NAME, '`;') 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_ROWS = 0;

sql
-- 安全刪除存儲(chǔ)過程
DELIMITER $$

CREATE PROCEDURE safe_drop_table(
    IN db_name VARCHAR(64),
    IN tbl_name VARCHAR(64)
)
BEGIN
    DECLARE table_exists INT;
    
    -- 檢查表是否存在
    SELECT COUNT(*) INTO table_exists
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = db_name
    AND TABLE_NAME = tbl_name;
    
    IF table_exists > 0 THEN
        -- 記錄刪除操作
        INSERT INTO deletion_log 
        (database_name, table_name, deleted_at) 
        VALUES (db_name, tbl_name, NOW());
        
        -- 執(zhí)行刪除
        SET @sql = CONCAT('DROP TABLE IF EXISTS `', db_name, '`.`', tbl_name, '`');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SELECT CONCAT('表 ', tbl_name, ' 已安全刪除') AS result;
    ELSE
        SELECT CONCAT('表 ', tbl_name, ' 不存在') AS result;
    END IF;
END$$

DELIMITER ;

-- 使用存儲(chǔ)過程刪除表
CALL safe_drop_table('my_database', 'old_table');

sql
-- 創(chuàng)建回收站表
CREATE TABLE table_recycle_bin (
    id INT AUTO_INCREMENT PRIMARY KEY,
    original_name VARCHAR(64) NOT NULL,
    backup_name VARCHAR(64) NOT NULL,
    database_name VARCHAR(64) NOT NULL,
    dropped_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    dropped_by VARCHAR(50),
    restore_status ENUM('pending', 'restored', 'purged') DEFAULT 'pending',
    INDEX idx_dropped_at (dropped_at)
);

-- 安全的DROP TABLE函數(shù)
DELIMITER $$

CREATE PROCEDURE recycle_drop_table(
    IN tbl_name VARCHAR(64)
)
BEGIN
    DECLARE backup_name VARCHAR(64);
    DECLARE db_name VARCHAR(64);
    
    SET db_name = DATABASE();
    SET backup_name = CONCAT('recycle_', tbl_name, '_', UNIX_TIMESTAMP());
    
    -- 重命名表到回收站
    SET @sql = CONCAT('RENAME TABLE `', db_name, '`.`', tbl_name, 
                      '` TO `', db_name, '`.`', backup_name, '`');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- 記錄到回收站
    INSERT INTO table_recycle_bin 
    (original_name, backup_name, database_name, dropped_by)
    VALUES (tbl_name, backup_name, db_name, CURRENT_USER());
    
    SELECT CONCAT('表已移到回收站: ', backup_name) AS message;
END$$

DELIMITER ;

sql
-- 基本臨時(shí)表
CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    score INT
);

-- 臨時(shí)表也可以有索引
CREATE TEMPORARY TABLE temp_orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    quantity INT,
    INDEX idx_product (product_name)
);

-- 從查詢結(jié)果創(chuàng)建臨時(shí)表
CREATE TEMPORARY TABLE top_customers AS
SELECT customer_id, SUM(amount) as total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 10000
ORDER BY total_spent DESC;

-- 創(chuàng)建臨時(shí)表（帶完整定義）
CREATE TEMPORARY TABLE IF NOT EXISTS temp_data (
    id INT NOT NULL AUTO_INCREMENT,
    session_id VARCHAR(32) NOT NULL,
    data_key VARCHAR(50),
    data_value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_session_key (session_id, data_key),
    INDEX idx_session (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

sql
-- 臨時(shí)表只在當(dāng)前會(huì)話可見
CREATE TEMPORARY TABLE session_temp (
    id INT,
    data VARCHAR(100)
);

-- 其他會(huì)話看不到這個(gè)表
-- 會(huì)話結(jié)束（斷開連接）后自動(dòng)刪除

-- 臨時(shí)表可以和非臨時(shí)表同名
CREATE TABLE regular_table (id INT);
CREATE TEMPORARY TABLE regular_table (id INT); -- 不沖突

-- 在臨時(shí)表存在期間，它會(huì)"隱藏"同名的永久表

sql
-- 場(chǎng)景1：中間計(jì)算結(jié)果
CREATE TEMPORARY TABLE temp_calculations AS
SELECT 
    user_id,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY user_id;

-- 使用臨時(shí)表進(jìn)行復(fù)雜計(jì)算
SELECT 
    u.username,
    tc.order_count,
    tc.total_amount,
    ROUND(tc.total_amount / tc.order_count, 2) as avg_order_value
FROM users u
JOIN temp_calculations tc ON u.id = tc.user_id
WHERE tc.order_count > 5;

-- 場(chǎng)景2：會(huì)話數(shù)據(jù)存儲(chǔ)
CREATE TEMPORARY TABLE session_cart (
    session_id VARCHAR(32),
    product_id INT,
    quantity INT DEFAULT 1,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (session_id, product_id)
);

-- 添加商品到購物車
INSERT INTO session_cart (session_id, product_id, quantity)
VALUES ('abc123session', 1001, 2)
ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity);

-- 場(chǎng)景3：批量數(shù)據(jù)處理
CREATE TEMPORARY TABLE temp_import (
    id INT AUTO_INCREMENT PRIMARY KEY,
    raw_data TEXT,
    processed BOOLEAN DEFAULT FALSE
);

-- 加載數(shù)據(jù)到臨時(shí)表
LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE temp_import
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(raw_data);

-- 處理數(shù)據(jù)
UPDATE temp_import
SET processed = TRUE
WHERE raw_data LIKE '%valid%';

sql
-- 查看臨時(shí)表
SHOW TABLES; -- 不會(huì)顯示臨時(shí)表

-- 查看當(dāng)前會(huì)話的臨時(shí)表
SHOW CREATE TEMPORARY TABLE temp_users;

-- 修改臨時(shí)表結(jié)構(gòu)
ALTER TEMPORARY TABLE temp_users
ADD COLUMN email VARCHAR(100);

-- 刪除臨時(shí)表（可選）
DROP TEMPORARY TABLE IF EXISTS temp_users;

-- 臨時(shí)表不會(huì)出現(xiàn)在information_schema中
SELECT * FROM information_schema.TABLES 
WHERE TABLE_NAME = 'temp_users'; -- 無結(jié)果

sql
-- 創(chuàng)建內(nèi)存臨時(shí)表（更快）
CREATE TEMPORARY TABLE fast_temp (
    id INT,
    name VARCHAR(50)
) ENGINE=MEMORY;

-- 內(nèi)存表的特點(diǎn)：
-- 1. 數(shù)據(jù)存儲(chǔ)在內(nèi)存中
-- 2. 速度極快
-- 3. 會(huì)話結(jié)束或服務(wù)器重啟數(shù)據(jù)丟失
-- 4. 大小受內(nèi)存限制

-- 查看內(nèi)存使用
SHOW TABLE STATUS LIKE 'fast_temp';

sql
-- 使用合適的引擎
CREATE TEMPORARY TABLE temp_large_data (
    -- 大量數(shù)據(jù)用InnoDB
) ENGINE=InnoDB;

CREATE TEMPORARY TABLE temp_small_data (
    -- 小數(shù)據(jù)用MEMORY
) ENGINE=MEMORY;

-- 添加合適索引
CREATE TEMPORARY TABLE temp_indexed (
    id INT,
    category VARCHAR(50),
    value DECIMAL(10,2),
    INDEX idx_category (category),
    INDEX idx_value (value DESC)
);

-- 控制臨時(shí)表大小
SET max_heap_table_size = 64*1024*1024; -- 64MB
SET tmp_table_size = 64*1024*1024; -- 64MB

-- 監(jiān)控臨時(shí)表使用
SHOW STATUS LIKE 'Created_tmp%';
/*
Created_tmp_tables      # 創(chuàng)建的臨時(shí)表數(shù)量
Created_tmp_disk_tables # 磁盤臨時(shí)表數(shù)量
Created_tmp_files       # 臨時(shí)文件數(shù)量
*/