mysql 运用锦集
【目录】
- 调整 字段的顺序：
- 插入字段到 指定字段 的后面:
- 添加多个索引
- 清空表（最彻底的清除，自增起始值也被设置为 0 ）
- 删除表（如果表存在，不存在，不报错）
- 清空mysql数据库中所有表的数据
- 大数据量的数据同步的正确姿势
- MYISAM 与 Innodb 的被设置为 auto_increment 的列，稍有不同
- 通过配置文件，永久修改 自增步长
- 修改已有表的引擎
1. 调整 字段的顺序：
1 alter table t_user 2 change creator_id creator_id int default 0 not null comment '创建人id' AFTER user_name 3 ,change creator_name creator_name varchar(20) default '' not null comment '创建人名称' AFTER creator_id 4 ,change create_time create_time datetime default CURRENT_TIMESTAMP not null comment '创建时间,制单时间' AFTER creator_name 5 ,change deleted_flag deleted_flag tinyint(1) default 0 not null comment '逻辑删除标志 0:未删除 1:已删除' AFTER create_time
2. 插入字段到 指定字段 的后面:
alter table t_student add user_name varchar(100) default '' not null comment '姓名' after code;
3. 添加多个索引
1 ALTER TABLE pop_stock_out ADD INDEX idx_company_out_time(company_info_id, out_time) 2 ,ADD INDEX idx_create_time(create_time) 3 ,ADD INDEX idx_out_time(out_time);
4. 清空表（最彻底的清除，自增起始值也被设置为 0 ）
truncate table t_user; -- 清空数据
5. 删除表（如果表存在，不存在，不报错）
drop table if exists t_user;
6. 清空mysql数据库中所有表的数据
（1）查询所有表名，拼接成 清空的语句：
select CONCAT('TRUNCATE TABLE ',table_name,';') from information_schema.tables where TABLE_SCHEMA = 'database-name' ;
（2）复制（1）所得语句并执行，实现清除：
TRUNCATE TABLE table_1; TRUNCATE TABLE table_2; TRUNCATE TABLE table_3;
7.大数据量的数据同步的正确姿势：数据：源数据S 和 目标数据D
（1）删去 目标数据D 的索引；
（2）无事务、不同步索引，进行数据传输（推荐使用：Navicat 里有同步数据结构、同步数据、数据传输，请选择 “数据传输”）；
（3）结束（2）再恢复索引。
8.MYISAM 与 Innodb 的被设置为 auto_increment 的列，稍有不同
（1）删除最后一条记录后，【不重启】数据库服务：两个引擎，效果都一样。都是被占用，新增的记录会在删除的记录的基础上自动加“步长”。
（2）删除最后一条记录后，【重启】数据库服务：
innodb的表新插入数据会是之前被删除的数据再加“步长”.
但是当Mysql服务被重启后，再向InnodB的自增表表里插入数据，那么会使用当前Innodb表里的最大的自增列再加“步长”.
原因：
Myisam类型存储引擎的表将最大的ID值是记录到数据文件中，不管是否重启最大的ID值都不会丢失。但是InnoDB表的最大的ID值是存在内存中的，若不重启Mysql服务，新加入数据会使用内存中最大的数据+1.但是重启之后，会使用当前表中最大的值再加“步长”。
9.通过配置文件，永久修改 自增步长
（1）在安装目录下（如 C:Program FilesMySQLMySQL Server 8.0），添加 配置文件 my.ini 进行配置。
（2）mysqld --defaults-file=../my.ini --user=root，(root：数据库用户)【重启】数据库服务。
1 [mysqld] 2 # 设置3306端口 3 port=3306 4 # 设置mysql的安装目录，修改为自己的安装目录路径。 5 basedir=C:Program FilesMySQLMySQL Server 8.0 6 # 设置mysql数据库的数据的存放目录，在安装mysql-5.7.30-winx64.zip版本的时候，此配置不可添加，否则mysql将无法启动。修改为自己的安装目录路径。 7 datadir=C:Program FilesMySQLMySQL Server 8.0data 8 # 允许最大连接数 9 max_connections=200 10 # 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统 11 max_connect_errors=10 12 # 服务端使用的字符集默认为UTF8 13 character-set-server=utf8 14 # 创建新表时将使用的默认存储引擎 15 default-storage-engine=INNODB 16 # 默认使用“mysql_native_password”插件认证 17 default_authentication_plugin=mysql_native_password 18 # 关闭ssl 19 skip_ssl 20 # 配置时区 21 default-time_zone='+8:00' 22 23 # 配置自增步长 24 auto_increment_increment=2 25 26 [mysql] 27 # 设置mysql客户端默认字符集 28 default-character-set=utf8 29 [client] 30 # 设置mysql客户端连接服务端时默认使用的端口 31 port=3306 32 default-character-set=utf8
10.修改已有表的引擎
alter table settle_inventory_accounting_flow_202100 engine=InnoDB ;