In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.