标签： SQL

记录一下MySQL8中配置GTID双主的方式。

需要添加以下配置信息：

vim /etc/my.cnf
# 添加
[mysqld]
# 两台服务器的server-id不能一致
server-id=1
gtid_mode=on
enforce-gtid-consistency=true

具体使用到的SQL语句：

# 创建一个账号用于另一台主机复制数据
# create user 'slave'@'0.0.0.0' identified by 'this is your password';

# 授权
grant REPLICATION SLAVE on *.* to 'slave'@'0.0.0.0';

# 查看主机状态
SHOW MASTER STATUS;

# 以下为另一台服务器执行内容
CHANGE REPLICATION SOURCE to
    SOURCE_HOST = '0.0.0.0',
    SOURCE_PORT = 3306,
    SOURCE_USER = 'slave',
    SOURCE_PASSWORD = 'this is your password';

# 停止并重置复制
STOP REPLICA;
reset REPLICA;

# 开始复制并查看复制状态
START REPLICA;
SHOW REPLICA STATUS;

每次都记不起来，记录一下…

# 添加自增约束
alter table table_name modify column COLUMN_NAME COLUMN_TYPE auto_increment;
# 配置自增起始值
alter table table_name auto_increment=10000;

背景：一个记录表，类似日志的信息，查询大量集中在某个用户个人的数据，分区需要尽量保证一个人的数据在一个分区里。因此采用通过user_id进行hash分区的方式。

-- 将分区字段添加为主键
alter table logs modify column id int not null;
alter table logs drop primary key;
alter table logs add primary key(id, user_id);
alter table logs modify column id int not null auto_increment;

-- 创建带分区的表
CREATE TABLE `logs_withs_partitions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  ...
  PRIMARY KEY (`id`,`user_id`)
) PARTITION BY HASH(user_id) PARTITIONS 5;
-- 将数据复制到带分区的表
insert into logs_withs_partitions
select * from logs;

-- 重命名表
rename table logs to logs_without_partitions;
rename table logs_withs_partitions to logs;

-- 删除不带分区的表
drop table logs_without_partitions;

业务中有思维导图的功能，涉及到大量的树形结构搜索、查询相关的功能，使用场景上查询量远高于增删改操作，记录一下当前的解决方案。

一、表结构

简化的表结构类似

create table nodes (
  id int primary key auto_increment,
  name varchar(255) not null default '' comment '节点名称',
  parent_id int not null default 0 comment '上级节点',

  index nodes_parent_id_index (parent_id),
  index nodes_name_index (name)
);

二、当前解决方案

更新表结构：

-- 添加字段
alter table nodes add column path text not null comment '节点路径';

-- 创建索引
create index nodes_path_index on nodes(path);

-- 更新历史数据
update nodes current
left join nodes parent on current.parent_id = parent.id
set path = ifnull(concat(parent.path, ',', current.parent_id), '0');

-- 插入更新后执行
update nodes current
left join nodes parent on current.parent_id = parent.id
set path = ifnull(concat(parent.path, ',', current.parent_id), '0');
where current.id = 198;

-- 级联删除
delete from nodes where id = 198;
delete from nodes where (path like '0,5,198,%' and parent_id = 198);

1. 查询ID为“5”的节点的所有子级、孙子级中name包含“搜索词”的记录

更新表后的查询方式：

-- 查询父级节点记录，获取到父级的path
select * from nodes where id = 5;

-- 通过父级path进行模糊查询
select * from nodes where (parent_id = 5 or path like '0,5,%') and name like '%搜索词%';

可以创建一个触发器，在插入、修改数据时，更新子级的path。

2. 查询ID为“5”的节点的所有父级

-- 获取当前节点
select * from nodes where id = 5;

-- 使用当前节点的path查询所有父级
select * from nodes where find_in_set(id, '0,5');

-- 或者也可以使用in
select * from nodes where id in (5);

因为有缓存，所以都尽量使用的简单查询，不使用缓存可以使用子查询。

记录一下MySQL8中配置GTID双主的方式。

（更多…）

记录一些常用的MySQL语句，方便查找翻阅。

（更多…）

每次都记不起来，记录一下…

# 添加自增约束
alter table table_name modify column COLUMN_NAME COLUMN_TYPE auto_increment;
# 配置自增起始值
alter table table_name auto_increment=10000;

业务中有思维导图的功能，涉及到大量的树形结构搜索、查询相关的功能，使用场景上查询量远高于增删改操作，记录一下当前的解决方案。

（更多…）

背景：一个记录表，类似日志的信息，查询大量集中在某个用户个人的数据，分区需要尽量保证一个人的数据在一个分区里。因此采用通过user_id进行hash分区的方式。

（更多…）