Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

MyCat self-increasing primary key

2025-01-15 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

Global serial number is a new function provided by MyCAT. In order to realize the case of sub-library and sub-table, the primary key of the table is globally unique, but the default self-growing primary key of MySQL can not meet this requirement. The syntax of the global serial number conforms to the standard SQL specification and has the following format:

Next value for MYCATSEQ_XXX

MYCATSEQ_XXX is the name of the serial number, and MyCAT automatically creates a new serial number, eliminating the complexity of development.

In addition, MyCAT also provides a global serial number named: MYCATSEQ_GLOBAL

Note that MYCATSEQ_ must be capitalized to be recognized correctly.

MyCAT warm Tip: in practice, it is recommended that each table use its own serial number, and the naming of the serial number is recommended as MYCATSEQ _ tableName_ID_SEQ.

There are three main ways to realize it: local file mode, database mode and local timestamp algorithm.

1. Local file method:

1. Principle: in this way, MyCAT configures sequence to the file. When the configuration in sequence is used, MyCAT will update the current value of sequence in the sequence_conf.properties file in conf.

2. Mode of use:

(1) configure Server.xml of MyCat

# where 0 means local file method is used.

0

(2) configure sequence_conf.properties

$vim mycat/conf/sequence_conf.properties

# default global sequence Global

GLOBAL.HISIDS=

GLOBAL.MINID=10001

GLOBAL.MAXID=20000

GLOBAL.CURID=10000

# self define sequence customization

COMPANY.HISIDS=

COMPANY.MINID=1001

COMPANY.MAXID=2000

COMPANY.CURID=1000

# where HISIDS represents the used history segment (generally, no special needs can not be configured), MINID represents the minimum ID value, MAXID represents the maximum ID value, and CURID represents the current ID value.

# GLOBAL can also use other names here, but must be uppercase; the definition can be used globally later.

# you can use mysql > select next value for MYCATSEQ_xxx (custom name, here is MYCATSEQ_ GLOBAL); to see the next self-added ID.

Restart MyCat when the setup is complete.

3. Testing

Mysql > create table test (id int,name varchar (20))

Mysql > insert into test (id,name) values (next value for MYCATSEQ_GLOBAL,@@hostname)

Mysql > select * from test

4. Advantages and disadvantages

Advantages: local loading, fast reading speed.

Cons: when MyCAT is rereleased, the sequence in the configuration file will revert to its original value.

Second, the database mode:

1. Principle:

Create a table in the database to store the sequence name (name), the current value of sequence (current_value), and the step size (how many sequence are read by the increment int type at a time, when the read increment is used up

Read increment again to improve efficiency, obviously mycat increment and mysql increment meaning is different) and other information.

2. Steps to obtain Sequence:

(1), when using the sequence for the first time, according to the passed sequence name, read current_value from the table in the database, and increment to MyCat, and set the current_value in the database to the original current_ value + increment value (the implementation is based on the subsequent storage function).

(2) MyCat will read current_value+increment as the sequence value to be used this time, and add 1 automatically the next time it is used. After using increment times, perform step 1) the same operation. MyCat is responsible for maintaining this table, which sequence is used, you only need to insert a record in this table. If the sequence read in one time is not used up, the system stops, and the remaining sequence value read this time will no longer be used.

3. Mode of use:

(1) configure Server.xml, where 1 indicates the use of database mode.

one

(2) set sequence_db_conf.properties

Add the following to the sequence_db_conf.properties file in the mycat conf directory:

# sequence stored in datanode

GLOBAL=dn2

DICT=dn2

Dn2: indicates that tables and functions are built on the dn2 node.

Note: GLOBAL and DICT must be uppercase.

Restart MyCat

(3) create tables and store functions in the database corresponding to one of the sharding points

Because what I configured in schema.xml is:

For example, I created it in dn2, and the corresponding database is called db2 (why datanode is involved here, because subsequent sequence_db_conf.properties files will be used).

Note that instead of creating tables and storage functions in mycat, log in to the database to create tables and storage functions.

(3. 1) create table-create MYCAT_SEQUENCE table

DROP TABLE IF EXISTS MYCAT_SEQUENCE

CREATE TABLE MYCAT_SEQUENCE (

Name VARCHAR (50) NOT NULL

Current_value INT NOT NULL

Increment INT NOT NULL DEFAULT 1

Remark varchar (100),-- remark is not required, in order to make each table correspond to a global self-increment, configure the table name corresponding to the self-increment in Remark. Convenient for later maintenance

PRIMARY KEY (name)) ENGINE=InnoDB

(3. 2). Create the storage function 1-get the value of the current sequence (return the current value, increment)

DROP FUNCTION IF EXISTS `mycat_seq_ currval`

DELIMITER

CREATE DEFINER= `root` @ `% `FUNCTION `mycat_seq_ currval` (seq_name VARCHAR (50)) RETURNS varchar (64) CHARSET latin1

DETERMINISTIC

BEGIN

DECLARE retval VARCHAR (64)

SET retval= "- 99999999999"

SELECT concat (CAST (current_value AS CHAR), ",", CAST (increment AS CHAR)) INTO retval FROM MYCAT_SEQUENCE WHERE name = seq_name

RETURN retval

END

DELIMITER

(3.3), create the storage function 2mi-get the next sequence value

DROP FUNCTION IF EXISTS `mycat_seq_ nextval`

DELIMITER

CREATE DEFINER= `root` @ `% `FUNCTION `root` (seq_name VARCHAR (50)) RETURNS varchar (64) 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

(3.4), create the storage function 3Mui-set the sequence value

DROP FUNCTION IF EXISTS `mycat_seq_ setval`

DELIMITER

CREATE DEFINER= `root` @ `% `FUNCTION `root` (seq_name VARCHAR (50), value INTEGER) RETURNS varchar (64) CHARSET latin1

DETERMINISTIC

BEGIN

UPDATE MYCAT_SEQUENCE

SET current_value = value

WHERE name = seq_name

RETURN mycat_seq_currval (seq_name)

END

DELIMITER;

In the table MYCAT_SEQUENCE, where:

-name sequence name

-current_value current value

-increment growth step! It can be understood as how many sequence mycat reads in the database at a time. When these are used up, read them from the database next time.

Note: MYCAT_SEQUENCE must be capitalized.

Create a storage function:

Note: it must be created in the same database, in this case, db2. A total of three storage functions need to be created.

-gets the value of the current sequence (returns the current value in increments).

4. Insert sequence record:

-- insert sequence record

INSERT INTO MYCAT_SEQUENCE (name,current_value,increment,remark) VALUES ('DICT', 1,100 match tbreaddicks')

INSERT INTO MYCAT_SEQUENCE (name,current_value,increment,remark) VALUES ('GLOBAL', 1,100 Globals')

-- represents the insertion of a sequence named mycat with a current value of 1 and a step size of 100.

Mysql > select * from mycat_sequence

+-+

| | name | current_value | increment | remark | |

+-+

| | DICT | 1 | 100 | match:tb_dic | |

| | GLOBAL | 200 | 100 | GLOBAL | |

+-+

At this point, the preparatory work for the database has been completed.

5. Start testing

$mysql-h227.0.0.1-utest-ptest-P8066-DTESTDB

Mysql >

Create table tb_dic

(

Id int not null auto_increment

Dic_name varchar (100) not null comment 'dictionary name'

Dic_value varchar (20) not null comment 'dictionary value'

Dic_type int not null comment 'dictionary type: such as payment method, etc.'

Primary key (id)

);

# then insert the value

Mysql > INSERT into tb_dic (id,dic_name,dic_value,dic_type) VALUES (next value for MYCATSEQ_DICT,' payment method','1 payment method 0)

+-+

| | id | dic_name | dic_value | dic_type | |

+-+

| | 101 | activity form | 2 | 0 | |

| | 102 | form type | 2 | 0 | |

+-+

Error handling:

ERROR 1003 (HY000): mycat sequnce err.org.opencloudb.config.util.ConfigException: can't find definition for sequence: DICT

Since the current mycat is not aware of the modification of sequence_db_conf.properties, you can restart mycat or log in to the 9066 management port to reload @ @ config

At this point, after testing, there are still two key points: MYCAT_SEQUENCE must be capitalized, and DICT=dn2 in the sequence_db_conf.properties file must be uppercase.

3. Local timestamp algorithm

ID= 64-bit binary (42 (milliseconds) + 5 (machine ID) + 5 (business coding) + 12 (repeat accumulation).

Converted to decimal to 18-bit long type, can be concurrently 12-bit binary accumulation per millisecond.

1. Configure server.xml

two

2. Configure sequence_time_conf.properties under mycat

WORKID=0-31 any integer

DATAACENTERID=0-31 any integer

Each mycat is configured with a different WORKID,DATAACENTERID, which forms a unique identity and supports a total of 32 / 32 / 1024 combinations.

ID example: 56763083475511

IV. Summary:

1. Starting from MyCAT 1.3, self-growing primary keys are supported, depending on the global serial number mechanism. It is recommended to use the global serial number in the database mode, and set the step size correctly so as not to affect the actual performance.

First of all, you need to open the global serial number of the database mode. For the tables that need to define the self-growing primary key, establish the corresponding global serial number, which is capitalized with the same name as the table name.

For example, the customer sequence is named CUSTOMER, and then in schema.xml, the attribute autoIncrement value is added to the table element of the customer table to true.

2. How to obtain the self-increasing primary key for the application:

Experience sharing of MyCAT self-increasing fields and returning generated primary key ID

(1) mysql itself for non-self-growing primary key, using last_insert_id () will not return the result, only 0. 0.

(2) mysql only defines a self-growing primary key, and you can return the primary key value with last_insert_id ().

(3) mycat currently provides self-growing primary key function, but if there is no auto_increment defined in the data table on the corresponding mysql node, then calling last_insert_id () in the mycat layer will not return the result.

(4) the correct use of the database is as follows:

Mysql defines a self-increasing primary key

CREATE TABLE `tt2` (

`id` bigINT (10) UNSIGNED NOT NULL AUTO_INCREMENT, / / must be self-increasing

`nm` INT (10) UNSIGNED NOT NULL

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

(4.2), self-increasing definition of mycat

[root@test conf] # vim schema.xml

Select user ()

(4.3), mycat adds corresponding settings for sequence_db_conf.properties, and adds corresponding records in the corresponding mycat_sequence of mycat.

(4. 4. Connect mycat. The 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)

(4.5), about the use of bulk insertion:

A. use ordinary serial number to insert in batch:

Insert (a ~ b ~ ~ c) values (x ~), (x ~)

B. Use the global serial number to insert in batch, you must add comments:

/ *! mycat:catlet=demo.catlets.BatchInsertSequence * /

Insert (a ~ b ~ ~ c) values (x ~), (x ~)

C, is that sharding key must be included in the column enumeration, especially if the primary key is self-increasing, 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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report