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 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

my.ini 配置

10.修改已有表的引擎

alter table settle_inventory_accounting_flow_202100 engine=InnoDB ;