MySQL数据表修改与管理的完整指南

alter table 表名
[操作类型] 列名/索引名/约束名
[数据类型] [约束条件]
[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;

-- 修改数据类型和约束
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);

-- 同时修改列名、类型和约束
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;

-- 移动到其他数据库
alter table current_db.table_name 
rename to other_db.table_name;

sql
-- 修改存储引擎
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;
-- 执行大量插入操作...
alter table large_table enable keys;

sql
-- 添加分区
alter table sales
add partition (
    partition p2024_04 values less than (202405)
);

-- 删除分区（数据会丢失！）
alter table sales
drop partition p2023_01;

-- 重组分区
alter table sales
reorganize partition p_future into (
    partition p2024_05 values less than (202406),
    partition p_future values less than maxvalue
);

-- 合并分区
alter table sales
coalesce partition 4;

-- 重建分区（优化）
alter table sales
rebuild partition p2024_01;

sql
-- 使用algorithm指定算法
alter table large_table 
add column new_column int,
algorithm = inplace,      -- 在线修改
lock = none;              -- 不加锁

-- 修改多个属性
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. 确认表存在
show tables like 'table_to_drop';

-- 2. 查看表结构和数据量
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. 备份重要数据
-- 使用mysqldump或其他工具备份

sql
-- 查看外键约束
show create table orders;

-- 删除有外键引用的表（方法1：先删除外键）
alter table child_table drop foreign key fk_name;
drop table parent_table;

-- 方法2：使用cascade（小心！）
-- 这会删除所有引用该表的子表数据
drop table parent_table cascade;

-- 方法3：临时禁用外键检查
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
-- 安全删除存储过程
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());
        
        -- 执行删除
        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 ;

-- 使用存储过程删除表
call safe_drop_table('my_database', 'old_table');

sql
-- 创建回收站表
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函数
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
-- 基本临时表
create temporary table temp_users (
    id int primary key,
    name varchar(50),
    score int
);

-- 临时表也可以有索引
create temporary table temp_orders (
    order_id int auto_increment primary key,
    product_name varchar(100),
    quantity int,
    index idx_product (product_name)
);

-- 从查询结果创建临时表
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;

-- 创建临时表（带完整定义）
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
-- 临时表只在当前会话可见
create temporary table session_temp (
    id int,
    data varchar(100)
);

-- 其他会话看不到这个表
-- 会话结束（断开连接）后自动删除

-- 临时表可以和非临时表同名
create table regular_table (id int);
create temporary table regular_table (id int); -- 不冲突

-- 在临时表存在期间，它会"隐藏"同名的永久表

sql
-- 场景1：中间计算结果
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;

-- 使用临时表进行复杂计算
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;

-- 场景2：会话数据存储
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);

-- 场景3：批量数据处理
create temporary table temp_import (
    id int auto_increment primary key,
    raw_data text,
    processed boolean default false
);

-- 加载数据到临时表
load data infile '/path/to/data.csv'
into table temp_import
fields terminated by ','
lines terminated by '\n'
(raw_data);

-- 处理数据
update temp_import
set processed = true
where raw_data like '%valid%';

sql
-- 查看临时表
show tables; -- 不会显示临时表

-- 查看当前会话的临时表
show create temporary table temp_users;

-- 修改临时表结构
alter temporary table temp_users
add column email varchar(100);

-- 删除临时表（可选）
drop temporary table if exists temp_users;

-- 临时表不会出现在information_schema中
select * from information_schema.tables 
where table_name = 'temp_users'; -- 无结果

sql
-- 创建内存临时表（更快）
create temporary table fast_temp (
    id int,
    name varchar(50)
) engine=memory;

-- 内存表的特点：
-- 1. 数据存储在内存中
-- 2. 速度极快
-- 3. 会话结束或服务器重启数据丢失
-- 4. 大小受内存限制

-- 查看内存使用
show table status like 'fast_temp';

sql
-- 使用合适的引擎
create temporary table temp_large_data (
    -- 大量数据用innodb
) engine=innodb;

create temporary table temp_small_data (
    -- 小数据用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)
);

-- 控制临时表大小
set max_heap_table_size = 64*1024*1024; -- 64mb
set tmp_table_size = 64*1024*1024; -- 64mb

-- 监控临时表使用
show status like 'created_tmp%';
/*
created_tmp_tables      # 创建的临时表数量
created_tmp_disk_tables # 磁盘临时表数量
created_tmp_files       # 临时文件数量
*/