mysql 运维锦集
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 Files\MySQL\MySQL 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 Files\MySQL\MySQL Server 8.0 6 # 设置mysql数据库的数据的存放目录，在安装mysql-5.7.30-winx64.zip版本的时候，此配置不可添加，否则mysql将无法启动。修改为自己的安装目录路径。 7 datadir=C:\Program Files\MySQL\MySQL Server 8.0\data 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 ;
11.某个字段内容 [部分]替换：引用：Mysql某个字段内容替换（字符串中的一部分内容替换） sql
【问题描述】：
将某一个字段的数据内容中的一部分替换：如图
数据量大情况下可以进行批量替换会很方便快捷
【问题解决】：
sql函数: INSERT(s1,x,len,s2)
字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串
从字符串第一个位置开始的 6 个字符替换为 runoob：
SELECT INSERT(“google.com”, 1, 6, “runoob”); – 输出： runoob.com
(注意，这里初始位置从1开始，包含这个起始位置)
sql语句：
UPDATE catalogcodetest SET CatalogCode = INSERT(CatalogCode, 5, 2, '18')
【总结】：
UPDATE 表 SET 字段A= INSERT(字段A, 起始位置, 几个, 替换内容)
【扩展示例】：
【需求】：批量更新id，并将id最新值，同步更新到其他文本字段的部分内容。
【思路】：
（1）为了避免主键冲突，先整体拉大id间差值：id 扩大n倍，n的取值为大于等于 本次调整的条数。总的目的是：满足，调整后的值，不与现有id值冲突。
UPDATE config cf SET cf.id = cf.id * 10 WHERE cf.id > 5 ;
（2）缩小之前的倍数，同时＋2.
UPDATE config cf SET cf.id = cf.id / 10 + 2 WHERE cf.id > 5 ;
（3）将id更新到其他字段或文本中：同一批次，只调整位数相同的数据；
UPDATE config cf SET cf.fee_type = cf.id, cf.config_info = INSERT(cf.config_info, 4, 1, cf.id) WHERE cf.id > 2 AND cf.id < 10; UPDATE config cf SET cf.fee_type = cf.id, cf.config_info = INSERT(cf.config_info, 4, 1, cf.id) WHERE cf.id >= 10 and cf.id < 12; UPDATE config cf SET cf.fee_type = cf.id, cf.config_info = INSERT(cf.config_info, 4, 2, cf.id) WHERE cf.id =12;
