创建中间表mysql mysql中间键

转载

月光倾城美 2024-03-08 12:04:50

文章标签 创建中间表mysql 主键自增数据库 文章分类 MySQL 数据库

开启新的专题【数据库中间件】第7节，一起开启数据库中间件的学习，上节回顾：本机windows系统启动mycat，访问3台虚拟机，演示mycat的11种分片策略演示，防止入坑。本节就演示上节提到的主键6种生成方式。

（一）主键值生成方式

① 介绍

在实现分库分表的情况下，原来是一张表就分到多个库上面和多个表上面，各个表都有自增，无法保证数据库自增主键自增主键的全局唯一。为此，MyCat 提供了全局sequence，并且提供了包含本地配置和数据库配置等多种实现方式。

CREATETABLE t_customer(  
 

     idBIGINT PRIMARY KEY,   
 

     namevarchar(100) notnull,   
 

     province intnotnull   
 

     );  
 

     <tablename="t_customer"primaryKey="id"autoIncrement="true"dataNode="dn1,dn2,dn3"rule="sharding-by-province" />

② 本地文件方式

原理：此方式MyCAT 将sequence 配置到文件中，当使用到sequence 中的配置后，MyCAT 会更新 conf中的sequence_conf.properties 文件中sequence 当前的值。

配置方式：

1.在sequence_conf.properties 文件中做如下配置

HISIDS 历史ID

MIXID 最小ID

MAXID 最大ID

CURID 当前ID

GLOBAL.HISIDS=   
 

     GLOBAL.MINID=1001GLOBAL.MAXID=1000000000GLOBAL.CURID=1000

其中HISIDS 表示使用过的历史分段(一般无特殊需要可不配置)，MINID 表示最小ID 值，MAXID 表示最大 ID 值，CURID 表示当前ID 值。

2.server.xml 中配置

注：sequnceHandlerType 需要配置为0，表示使用本地文件方式。

<system><propertyname="sequnceHandlerType">0</property></system>

使用示例

缺点：当MyCAT 重新发布后，配置文件中的sequence 会恢复到初始值。优点：本地加载，读取速度较快。为表配置主键自增值的序列。如果需要重启，需要先将这个文件进行拷贝出来，修改后，在拷贝到这个位置。

insertinto table1(id,name) values(nextvaluefor MYCATSEQ_GLOBAL,‘test’);

为表配置主键自增值的序列：

规则：在sequence_conf.properties 中配置以表名为名的序列。

T_COMPANY.CURID=501   
 

     T_COMPANY.MINID=1   
 

     T_COMPANY.MAXID=1000000000

就可以使用了。

<tablename="t_company"primaryKey="id"autoIncrement="true"dataNode="dn1,dn2,dn3"rule="range-sharding-by-members-count" />  
 

     INSERTINTO t_company(name,members) VALUES('company06',200);   
 

     select * from t_company;

③ 数据库方式

在数据库中建立一张表，存放sequence 名称(name)，sequence 当前值(current_value)，步长(increment int类型，每次读取多少个sequence)等信息。

Sequence 获取步骤

1. 当初次使用该sequence 时，根据传入的sequence 名称，从数据库这张表中读取
2. current_value，和 increment 到MyCat 中，并将数据库中的current_value 设置为原
3. current_value 值+increment 值。
4. MyCat 将读取到current_value+increment 作为本次要使用的sequence 值，下次使用
5. 时，自动加1，当 使用increment 次后，执行步骤1)相同的操作。

MyCat 负责维护这张表，用到哪些sequence，只需要在这张表中插入一条记录即可。若某次读取的 sequence 没有用完，系统就停掉了，则这次读取的sequence 剩余值不会再使用。

注：sequnceHandlerType 需要配置为1，表示使用数据库方式生成sequence。

<system><propertyname="sequnceHandlerType">1</property></system>

数据库配置：

创建MYCAT_SEQUENCE 表

-- 创建存放sequence 的表 DROPTABLEIFEXISTS MYCAT_SEQUENCE;   
 

     -- name sequence 名称 -- current_value 当前value -- increment 增长步长! 可理解为mycat 在数据库中一次读取多少个sequence. 当这些用完后, 下次再从数 据库中读取。 CREATETABLE MYCAT_SEQUENCE (   
 

     nameVARCHAR(50) NOTNULL,   
 

     current_value INTNOTNULL,   
 

     incrementINTNOTNULLDEFAULT100,   
 

     PRIMARY KEY(name)  
 

     );

-- 插入一条sequence INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('GLOBAL', 100000, 100);

创建相关function

-- 获取sequence当前值(返回当前值,增量)的函数 DROPFUNCTIONIFEXISTS mycat_seq_currval;

CREATEFUNCTION mycat_seq_currval(seq_name VARCHAR(50))   
 

     RETURNSvarchar(64)  
 

     BEGINDECLARE retval VARCHAR(64);   
 

     SET retval='-999999999,null';   
 

     SELECTconcat(CAST(current_value ASCHAR),',',CAST(incrementASCHAR)) INTO retval   
 

     FROM MYCAT_SEQUENCE   
 

     WHEREname = seq_name;   
 

     RETURN retval;   
 

     END;

-- 设置sequence 值的函数 DROPFUNCTIONIFEXISTS mycat_seq_setval;

CREATEFUNCTION mycat_seq_setval(seq_name VARCHAR(50),valueINTEGER)   
 

     RETURNSvarchar(64)  
 

     BEGINUPDATE MYCAT_SEQUENCE   
 

     SET current_value = valueWHEREname = seq_name;   
 

     RETURN mycat_seq_currval(seq_name);   
 

     END;

-- 获取下一个sequence 值 DROPFUNCTIONIFEXISTS mycat_seq_nextval;   
 

     CREATEFUNCTION mycat_seq_nextval(seq_name VARCHAR(50))   
 

     RETURNSvarchar(64)   
 

     BEGINUPDATE MYCAT_SEQUENCE SET current_value = current_value + incrementWHEREname = seq_name;   
 

     RETURN mycat_seq_currval(seq_name);   
 

     END;

注意：MYCAT_SEQUENCE 表和以上的3 个function，需要放在同一个节点上。function 请直接在具体节点的数据库上执行，如果执行的时候报： you might want to use the less safe log_bin_trust_function_creators variable

需要对数据库做如下设置： windows 下my.ini[mysqld]加上

log_bin_trust_function_creators=1 linux 下/etc/my.cnf 下my.ini[mysqld]加上

log_bin_trust_function_creators=1 修改完后，即可在mysql 数据库中执行上面的函数。

sequence_db_conf.properties 相关配置,指定sequence 相关配置在哪个节点上：

USER_SEQ=test_dn1

使用示例：

insertinto table1(id,name) values(nextvaluefor MYCATSEQ_GLOBAL,'test')；

配置表的主键自增使用序列

1 在序列定义表中增加名字为表名的序列

INSERTINTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('T_COMPANY', 1,100);

2 在sequence_db_conf.properties中增加表的序列配置

T_COMPANY=dn1

3 主键自增就可以使用了

<tablename="t_company"primaryKey="id"autoIncrement="true"dataNode="dn1,dn2,dn3"rule="range-sharding-by-members-count" />  
 

     INSERTINTO t_company(name,members) VALUES('company08',200);   
 

     select * from t_company;

④ 本地时间戳方式

原理：

ID= 64 位二进制：42(毫秒)+5(机器ID)+5(业务编码)+12(重复累加)。

换算成十进制为18 位数的long 类型，每毫秒可以并发12 位二进制的累加。

使用方式：

1.配置server.xml

<propertyname="sequnceHandlerType">2</property>

2.在mycat 下配置：sequence_time_conf.properties

WORKID=0-31 任意整数表示机器id（或mycat实例id）

DATAACENTERID=0-31 任意整数业务编码

多个mycat 节点下每个mycat 配置的WORKID，DATAACENTERID 不同，组成唯一标识，总共支持32*32=1024 种组合。

ID 示例：56763083475511 。

主键自增配置

<tablename="t_company"primaryKey="id"autoIncrement="true"dataNode="dn1,dn2,dn3"rule="range-sharding-by-members-count" />  
 

     INSERTINTO t_company(name,members) VALUES('company09',200);   
 

     select * from t_company;

⑤ 分布式ZK ID生成器

server.xml

# system 中添加  
 

     <propertyname="sequnceHandlerType">3</property>

配置

1.Zk 的连接信息统一在myid.properties 的zkURL 属性中配置。此只需关注zkURL。

loadZk=false   
 

     zkURL=127.0.0.1:2181   
 

     clusterId=mycat-cluster-1   
 

     myid=mycat_fz_01   
 

     clusterSize=3   
 

     clusterNodes=mycat_fz_01,mycat_fz_02,mycat_fz_04   
 

     #server booster ; booster install on db same server,will reset all   
 

     minCon to 2 type=server   
 

     boosterDataHosts=dataHost1

基于ZK 与本地配置的分布式ID 生成器，ID 结构：long 64 位，ID 最大可占63 位：

|current time millis(微秒时间戳38 位,可以使用17 年)|clusterId（机房或者ZKid，通过配置文件配置5 位）|instanceId（实例ID，可以通过ZK 或者配置文件获取，5
位）|threadId（线程ID，9 位） |increment(自增,6 位)
一共63 位，可以承受单机房单机器单线程1000*(2^6)=640000 的并发。
无悲观锁，无强竞争，吞吐量更高
配置文件：sequence_distributed_conf.properties，只要配置里面：INSTANCEID=ZK 就是从ZK 上获取 InstanceID。(可以通过ZK 获取集群（机房）唯一InstanceID，也可以通过配置文件配置InstanceID)

测试

<tablename="t_company"primaryKey="id"autoIncrement="true"dataNode="dn1,dn2,dn3"rule="range-sharding-by-members-count" />  
 

     INSERTINTO t_company(name,members) VALUES('company10',200);   
 

     select * from t_company;

⑥ ZK 递增的方式

server.xml

# system 中添加  
 

     <propertyname="sequnceHandlerType">4</property>

Zk 的连接信息统一在myid.properties 的zkURL 属性中配置。

配置：

配置文件：sequence_conf.properties 只要配置好ZK 地址和表名的如下属性

TABLE.MINID 某线程当前区间内最小值
TABLE.MAXID 某线程当前区间内最大值
TABLE.CURID 某线程当前区间内当前值

文件配置的MAXID 以及MINID 决定每次取得区间，这个对于每个线程或者进程都有效。文件中的这三个属性配置只对第一个进程的第一个线程有效，其他线程和进程会动态读取ZK。

测试

<tablename="t_company"primaryKey="id"autoIncrement="true"dataNode="dn1,dn2,dn3"rule="range-sharding-by-members-count" />  
 

     INSERTINTO t_company(name,members) VALUES('company12',200);   
 

     select * from t_company;

⑦ last_insert_id() 问题

我们配置分片表主键自增。

<tablename="t_company"primaryKey="id"autoIncrement="true"dataNode="dn1,dn2,dn3"rule="range-sharding-by-members-count" />

如需通过 select last_insert_id() 来获得自增主键值，则表定义中主键列需是自增的

AUTO_INCREMENT

CREATETABLE t_company(   
 

     idBIGINT PRIMARY KEY AUTO_INCREMENT,   
 

     namevarchar(100) notnull,   
 

     members intnotnull   
 

     );

如果没有指定 AUTO_INCREMENT，则select last_insert_id() 获取不到刚插入数据的主键值。

CREATETABLE t_company(   
 

     idBIGINT PRIMARY KEY,   
 

     namevarchar(100) notnull,   
 

     members intnotnull   
 

     );

Mybatis 中新增记录后获取last_insert_id 的示例

<insertid="insert"parameterType="com.study.mike.user.model.User">   
 

     insert into t_user (user_name,login_name,login_pwd,role_id) values(#{userName},#{loginName},#{loginPwd},#{roleId})   
 

     <selectKeyresultType="java.lang.Long"order="AFTER"keyProperty="id">   
 

     select last_insert_id() as id   
 

     </selectKey></insert>

PS：这就是myCat相关分库分表先关的内容，建议不选择文件的方式，选择数据库的方式或者是zk的方式来确定主键的生成，保证唯一性

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。