SQL 中使用说明 自定义序列号的标识为:MYCATSEQ_XXX ,其中XXX为具体定义的sequence的名称,应用举例如下: 使用默认的全局sequence : insert into tb1(id,name) values(next value for MYCATSEQ_GLOBAL,'tb1'); 使用自定义的 sequence: insert into tb2(id,name) values(next value for MYCATSEQ_MY1,'tb2'); 获取最新的值 select next value for MYCATSEQ_xxx
[root@testA conf]# mysql -uroot -p123456 -P8066 -h 127.0.0.1 test mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
[root@testA conf]# mysql -uroot -p123456 -P8066 -h 127.0.0.1 test mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from id_local_time; +--------------------+---------------+ | id | nm | +--------------------+---------------+ | 922641363168792576 | id_local_time | | 922641424359493632 | id_local_time | +--------------------+---------------+ 2 rows in set (0.06 sec)
mysql> select next value for MYCATSEQ_GLOBAL; +--------------------+ | 922641542101995520 | +--------------------+ | 922641542101995520 | +--------------------+ 1 row in set (0.00 sec)
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 100, PRIMARY KEY (NAME) ) ENGINE = INNODB ;
INSERT INTO mycat_sequence(name,current_value,increment) VALUES ('GLOBAL', 100000, 100);
DROP FUNCTION IF EXISTS `mycat_seq_currval`; DELIMITER ;; CREATE FUNCTION `mycat_seq_currval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8 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_name; RETURN retval ; END ;; DELIMITER ;
DROP FUNCTION IF EXISTS `mycat_seq_nextval`; DELIMITER ;; CREATE FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8 DETERMINISTIC BEGIN UPDATE mycat_sequence SET current_value = current_value + increment WHERE name = seq_name; RETURN mycat_seq_currval(seq_name); END ;; DELIMITER ;
DROP FUNCTION IF EXISTS `mycat_seq_setval`; DELIMITER ;; CREATE FUNCTION `mycat_seq_setval`(seq_name VARCHAR(50), value INTEGER) RETURNS varchar(64) CHARSET utf8 DETERMINISTIC BEGIN UPDATE mycat_sequence SET current_value = value WHERE name = seq_name; RETURN mycat_seq_currval(seq_name); END ;; DELIMITER ;
[root@testA mycat]# mysql -uroot -p123456 test mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 102 Server version: 5.7.19-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> DROP TABLE IF EXISTS mycat_sequence; Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE mycat_sequence ( -> NAME VARCHAR (50) NOT NULL, -> current_value INT NOT NULL, -> increment INT NOT NULL DEFAULT 100, -> PRIMARY KEY (NAME) -> ) ENGINE = INNODB ;
INSERT INTO mycat_sequence(name,current_value,increment) VALUES ('GLOBAL', 100000, 100);
[root@testA mycat]# mysql -uroot -p123456 -P8066 -h127.0.0.1 test mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; +----------+ | DATABASE | +----------+ | test | +----------+ 1 row in set (0.00 sec)
mysql> show tables; +----------------+ | Tables in test | +----------------+ | id_db | | mycat_sequence | +----------------+ 2 rows in set (0.00 sec)
mysql> drop id_db; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id_db' at line 1 mysql> drop table id_db; Query OK, 0 rows affected (0.08 sec)