Mycat distributed MySQL middleware (self-increasing primary key)

First, global serial number
The global serial number is a new feature provided by Mycat, where the primary key of a table is globally unique, and the default MySQL self-growing primary key does not meet this requirement in order to implement the Sub-Library table. The syntax for global serial numbers conforms to the standard SQL specification, in the form of:
Next value for Mycatseq_xxx
Mycatseq_xxx is the name of the serial number, Mycat automatically create a new serial number, eliminating the complexity of development, in addition, Mycat also provides a global serial number, the name is: Mycatseq_global

Note that the mycatseq_ must be capitalized to be recognized correctly.
Mycat warm tip: In practice, it is recommended that each table use its own serial number, the name of the serial number is MYCATSEQ _tablename_id_seq.

Instructions for use in SQL

The custom serial number is identified as: Mycatseq_xxx, where XXX is the name of the sequence that is specifically defined, the application example is as follows:

Use the default global sequence:

Insert into TB1 (id,name) values (next value for Mycatseq_global, ' micmiu.com ');

Using a custom sequence:

Insert into TB2 (id,name) values (next value for Mycatseq_my1, ' micmiu.com ');

Get the latest value

Select Next value for Mycatseq_xxx

Global serial numbers are available in a variety of ways
1) Local File
2) Database mode:
3) Local timestamp algorithm:

1) Local File
1. Configure Server.xml
<property name= "Sequncehandlertype" >0</property>
2. Configure Sequence_conf.properties
#default Global Sequence
GLOBAL. hisids=
GLOBAL. minid=10001
GLOBAL. maxid=20000
GLOBAL. curid=10000

# Self Define Sequence
Company. hisids=
Company. minid=1001
Company. maxid=2000

Company. curid=1000

Use example: INSERT INTO table1 (id,name) VALUES (next value for Mycatseq_global, ' test ');
Disadvantage: When Mycat is republished, sequence in the configuration file reverts to the initial value.
Advantage: Load locally, read faster.

2) Database mode:
1. Configure Server.xml
<property name= "Sequncehandlertype" >1</property>
2. Configure Sequence_db_conf.properties
#sequence stored in Datanode
Global=dn1
Myst=dn1

3. Database local creation script:
DROP TABLE IF EXISTS mycat_sequence;
CREATE TABLE mycat_sequence (name VARCHAR () not NULL, Current_value int NOT NULL, increment int. NOT NULL DEFAULT, P Rimary KEY (name)) Engine=innodb;
-- ----------------------------
--Function structure for ' mycat_seq_currval '
-- ----------------------------
DROP FUNCTION IF EXISTS ' mycat_seq_currval ';
DELIMITER;;
CREATE definer= ' root ' @ '% ' FUNCTION ' mycat_seq_currval ' (seq_name varchar) RETURNS varchar CHARSET latin1
Deterministic
BEGIN
DECLARE retval VARCHAR (64);
SET retval= " -999999999,null";
Select Concat (CAST (Current_value as Char), ",", cast (increment as char)) into retval from mycat_sequence WHERE name = Seq_n Ame
RETURN retval;
END
;;
DELIMITER;
-- ----------------------------
--Function structure for ' mycat_seq_nextval '
-- ----------------------------
DROP FUNCTION IF EXISTS ' mycat_seq_nextval ';
DELIMITER;;
CREATE definer= ' root ' @ '% ' FUNCTION ' mycat_seq_nextval ' (seq_name varchar) RETURNS varchar CHARSET latin1
Deterministic
BEGIN
UPDATE mycat_sequence
SET current_value = current_value + increment WHERE name = Seq_name;
RETURN Mycat_seq_currval (seq_name);
END
;;
DELIMITER;
-- ----------------------------
--Function structure for ' mycat_seq_setval '
-- ----------------------------
DROP FUNCTION IF EXISTS ' mycat_seq_setval ';
DELIMITER;;
CREATE definer= ' root ' @ '% ' FUNCTION ' mycat_seq_setval ' (seq_name varchar (), value INTEGER) RETURNS varchar CHARSET Latin1
Deterministic
BEGIN
UPDATE mycat_sequence
SET Current_value = value
WHERE name = Seq_name;
RETURN Mycat_seq_currval (seq_name);
END
;;
DELIMITER;
INSERT into Mycat_sequence VALUES (' GLOBAL ', 0, 100);
SELECT mycat_seq_setval (' GLOBAL ', 1);
SELECT mycat_seq_currval (' GLOBAL ');
SELECT mycat_seq_nextval (' GLOBAL ');
4. Insert sequence data:
INSERT into Mycat_sequence VALUES (' GLOBAL ', 0, 100);
INSERT into Mycat_sequence VALUES (' GLOBAL ', 0, 100);
Script Download:/attached/file/20150323/20150323175900_382.txt
Description

? Create serial number-related tables and functions on a partition (DataNode) database, and SQL scripts in Sequnce-sql.txt in the doc directory need to be executed on the database rather than on the mycat.
? The configuration information for the db stored in the sequnce is recorded in the mycat_home/conf/quence_db_conf.properties.
#sequence stored in Datanode
Global=dn1
Company=dn1
Customer=dn1
? In the Sequnce table, insert the corresponding sequnce record and determine its initial value, as well as the growth step size, which suggests an appropriate range, such as 50-500, that needs to be performed on the database rather than on the mycat.
INSERT into Mycat_sequence VALUES (' GLOBAL ', 0, 100);
? Modifying the current value of the sequnce is a new value that needs to be performed on the database, not on the mycat.
SELECT mycat_seq_curval (' GLOBAL ');

Tip: The size of the step selection, depending on the TPS with your data insertion, if it is 1000 per second, then the step is 1000x60=6 million, is not very large, that is, 60 seconds will be re-read from the database the next batch of serial number values.

3) Local timestamp algorithm:
Id= 64-bit binary (42 (MS) +5 (machine ID) +5 (business code) +12 (repeat cumulative)

A long type that is converted to a 18-digit decimal number, which can be combined with a 12-bit binary increment per millisecond.

How to use:

A. Configuring Server.xml

<property name= "Sequncehandlertype" >2</property>

B. Under Mycat configuration: sequence_time_conf.properties

workid=0-31 any integer

dataacenterid=0-31 any integer

The Workid,dataacenterid of each mycat configuration under multiple Mycat nodes makes up a unique identity that supports 32*32=1024 combinations in total.

ID Example: 56763083475511

Self-increment primary key configuration:

Starting with Mycat 1.3, supporting self-growing primary keys, depending on the global serial number mechanism, it is recommended to adopt the global serial number of the database, and set the step correctly, so as not to affect the actual performance.

The first thing to do is to turn on the global serial number of the database mode, to establish the corresponding global sequence number for the table that needs to define the self-growing primary key, and capitalize the name of the table

If the customer sequence name is customer, and then schema.xml the table element in the Customer table, the property AutoIncrement value is true.

<table name= "CUSTOMER" autoincrement= "true" >

Execute INSERT INTO customer (NAME,COMPANY_ID,SHARDING_ID) VALUES (' Test ', 2,10000), view effects,

The primary key is not supported for NULL, such as INSERT into customer (ID,NAME,COMPANY_ID,SHARDING_ID) VALUES (null, ' Test ', 2,10000);

How the application obtains the self-increment primary key:

Mycat experience sharing for the self-increment field and return the generated primary key ID
Description
1, MySQL itself on the non-self-growth primary key, using last_insert_id () will not return the results, will only return 0.
2, MySQL will only define the self-growth primary key, you can use LAST_INSERT_ID () to return the primary key value.

Mycat currently provides self-growing primary key functionality, but if the corresponding MySQL node is on a datasheet, no auto_increment is defined,
Then the call to LAST_INSERT_ID () on the MYCAT layer will not return the result.
Use the correct method as follows:

1. mysql definition self-increment primary key
CREATE TABLE ' Tt2 ' (
' ID ' INT (Ten) UNSIGNED not NULL auto_increment,//Must be self-increment
' Nm ' INT (Ten) UNSIGNED not NULL,
PRIMARY KEY (' id ')
) Engine=myisam auto_increment=6 DEFAULT Charset=utf8;
2, mycat definition of self-increment
[Email protected] conf]# vim Schema.xml
<?xml version= "1.0"?>
<! DOCTYPE mycat:schema SYSTEM "SCHEMA.DTD" >
<mycat:schema xmlns:mycat= "http://org.opencloudb/" >

<schema name= "TESTDB" checksqlschema= "false" sqlmaxlimit= ">"
<!--random sharding using mod sharind rule--
<!--autoincrement= "true" Property-
<table name= "TT2" primarykey= "id" autoincrement= "true" datanode= "Dn1,dn2,dn3,dn4,dn5" rule= "Mod-long"/>
<table name= "mycat_sequence" primarykey= "name" datanode= "Dn1"/>
</schema>

<datanode name= "dn1" datahost= "Localhost1" database= "DB1"/>
<datanode name= "DN2" datahost= "Localhost1" database= "DB2"/>
<datanode name= "Dn3" datahost= "Localhost1" database= "db3"/>
<datanode name= "Dn4" datahost= "Localhost1" database= "DB4"/>
<datanode name= "Dn5" datahost= "Localhost1" database= "DB5"/>

<datahost name= "Localhost1" maxcon= "$" mincon= "balance=" 0 "writetype=" 0 "dbtype=" MySQL "dbdriver=" native " >
<writehost host= "hostM1" url= "127.0.0.1:3366" user= "root" password= "123456" >
</writeHost>
</dataHost>
</mycat:schema>

3, mycat corresponding sequence_db_conf.properties to increase the corresponding settings;
4, mycat corresponding mycat_sequence to increase the corresponding record.
5, link mycat, test results are as follows:

127.0.0.1/root:[testdb> INSERT into TT2 (NM) VALUES (99);
Query OK, 1 row affected (0.14 sec)

127.0.0.1/root:[testdb> select last_insert_id ();
+------------------+
| last_insert_id () |
+------------------+
| 101 |
+------------------+
1 row in Set (0.01 sec)

About BULK INSERT use:

If there is no global serial number with MYCAT, it is normal BULK insert:

Insert (A,B,C) VALUES (x,x,x), (x,x,x);

If a global serial number is used, it must be annotated:

/*!mycat:catlet=demo.catlets.batchinsertsequence */insert (A,b,c) VALUES (x,x,x), (x,x,x);

The Sharding key must be included in the column enumeration, especially if the primary key is self-increment when the call must be displayed:

/*!mycat:catlet=demo.catlets.batchinsertsequence */insert (Id,a,b,c) values (, next value for Mycatseq_id,x,x,x), (Next value for mycatseq_id,x,x,x);

Mycat distributed MySQL middleware (self-increasing primary key)