Mycat Discovery (4)----The mycat of the self-growth primary key and returns the implementation of the generated primary key ID

Description: Mycat self-growing primary key and returns the implementation of the generated primary key ID

1) MySQL itself on the non-self-growing primary key, using last_insert_id () will not return the result, will only return 0; here's a simple test.

• Create a test table

----------------------------------------Create a test table------------------------------------- Usetest;CREATE TABLEIF  not EXISTSt_auto_increment (IDINT  not NULL PRIMARY KEY, NAMEVARCHAR( -) ) ENGINE=INNODBDEFAULTCHARSET=UTF8;

• To add a test statement

INSERT  into VALUES (1,'auto_01'); SELECT LAST_INSERT_ID ();

• Test results

• Modify Table

ALTER TABLE int auto_increment; DESC t_auto_increment;

• Add test

INSERT  into VALUES ('auto_02'); SELECT LAST_INSERT_ID ();

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 function, but if the corresponding MySQL node on the datasheet, there is no definition of auto_increment, Then the call to LAST_INSERT_ID () on the MYCAT layer will not return the result.

The correct configuration is as follows:

1) MySQL definition self-increment primary key

----------------------------------------------Create a test table--------------------------------------------CREATE TABLEIF  not EXISTStb_auto_increment (IDINT  not NULL PRIMARY KEYAuto_increment,nameVARCHAR( -)) DEFAULTCharSet=UTF8;

2) mycat define primary key increment: Modify Schema.xml

<!-- autoincrement= "True" indicates that the table primary key uses the self-growth policy  - <  name= "Tb_auto_increment"  primaryKey= "id"  autoincrement = "true" DataNode = "DN1,DN2" />

3) Mycat corresponding sequence_db_conf.properties to increase the corresponding settings

# Blow tables Add by Yzp 2016.03.10tb_auto_increment=dn1

Note: The tb_auto_increment here must be capitalized, and I have always reported the following error in lowercase at the beginning:

It means that the configuration of the time did not find tb_auto_increment, that is, the Sequence_db_conf.properties file added to the wrong content, when capitalized, no longer error.

4) Increase the sequence record of the TABLE1 table in the Mycat_sequence table in the database

INSERT  into mycat_sequence (name,current_value,increment) VALUES ('tb_auto_increment'1);

The current value is 100 and the step size is 1

Test using:

First set of test cases:

Insert  into Tb_auto_increment (name)values('increment_01'); Select *  from  Select* from Tb_auto_increment;

SelectSelect last_insert_id ();

The second set of test cases:

Insert  into Tb_auto_increment (name)values('increment_02'); Select *  from  Select* from Tb_auto_increment;

Select last_insert_id ();

Summary: At first there is a little doubt that when inserting, Mycat is automatically assigned to the configured two databases, but the execution

Select last_insert_id ();

, we see:

Only from a library to query, but then carefully think about it, because the data added to the two library is consistent, so there is no need to go to every library to query. Of course, the specific good to see the source code.

Mycat Discovery (4)----The mycat of the self-growth primary key and returns the implementation of the generated primary key ID