客服微信
sequence完全独立与表而存在，不需要作为表的索引第一列；
一个表的不同字段可以使用多个sequence产生序列值；
一个sequence可以给任意多个表使用产生自增序列值，也可以脱离表的insert/update语句而单独使用。
1
Tdsql Sequence 语法：
创建语法：CREATE TDSQL_SEQUENCE [dbname.]sequence_name[start with n][tdsql_minvalue n | nominvalue][maxvalue n | nomaxvalue][tdsql_increment by n][TDSQL_NOCYCLE | TDSQL_CYCLE]说明：START WITH：指定生成的第一个序列号。在升序时，序列可从比最小值大的值开始，缺省值为序列的最小值。对于降序，序列可由比最大值小的值开始，缺省值为序列的最大值。TDSQL_INCREMENT BY：指定序列号之间的间隔，该值可为正的或负的整数，但不可为0。序列为升序。忽略该子句时，缺省值为1。TDSQL_MINVALUE：指定序列的最小值。MAXVALUE：指定序列可生成的最大值。TDSQL_NOCYCLE：一直累加，不循环。
下面我们来测试一下 TDSQL 的 SEQUENCE：
[]/data/tdsql_run/15002/gateway/conf[]"1" />"1" />[]stop router_updateOK: router_update of instance_15002 is stoppedstop proxyOK: MySQL-Proxy of instance_15002 is stoppedrm ../data/instance_15002_video controlsded_mysql* -rfport:15002sh: ./initcgrouphierarchy.sh: No such file or directorypopen file:netstat -apn 2>/dev/null | grep 36000| grep -i LISTEN | awk '{print $4}' | awk -F':' '{print $1}' get buff is emptyhierarchy is empty,after guess is:/sys/fs/cgroupuse ../data as video controlsded database dirstart router_updaterm /dev/shm/0.0.0.0_15002zookeeper timeout:10000 msec,msg timeout 30000 mseckp init successstart proxy[] pid:65639,we are the init masterOK: MySQL-Proxy of instance_15002 is started[]MySQL [(none)]> use huyidb ;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMySQL [huyidb]> select * from mysql.tdsql_sequences ;Empty set (0.01 sec)#创建一个默认参数的sequenceMySQL [huyidb]> create tdsql_sequence huyidb.s1 ;Query OK, 0 rows affected (0.01 sec)MySQL [huyidb]> show create tdsql_sequence huyidb.s1 ;+---------+------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+---------+------------------------------------------------------------------------------------------------------------------------------+| huyidb.s1 | create tdsql_sequence huyidb.s1 start with 1 tdsql_minvalue 1 maxvalue 9223372036854775806 tdsql_increment by 1 tdsql_nocycle |+---------+------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)#创建一个指定参数的sequenceMySQL [huyidb]> create tdsql_sequence huyidb.s2 start with 1 tdsql_minvalue 1 maxvalue 5 tdsql_increment by 1 tdsql_nocycle ;Query OK, 0 rows affected (0.01 sec)#创建一个带缓存的sequenceMySQL [huyidb]> create tdsql_sequence huyidb.s3 start with 1 tdsql_minvalue 1 maxvalue 50 tdsql_increment by 1 cache 10 tdsql_nocycle ;Query OK, 0 rows affected (0.00 sec)#虽然创建成功了，但是从生成的语句来看，并没有cache 10 关键字。MySQL [(none)]> show create tdsql_sequence huyidb.s3;+-----------+---------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-----------+---------------------------------------------------------------------------------------------------------------+| huyidb.s4 | create tdsql_sequence huyidb.s3 start with 1 tdsql_minvalue 1 maxvalue 50 tdsql_increment by 1 tdsql_nocycle |+-----------+---------------------------------------------------------------------------------------------------------------+MySQL [(none)]> create table huyidb.test1 ( a int , b int , c int,primary key (a) ) shardkey=a;Query OK, 0 rows affected (0.14 sec)#一个表同时使用多个sequenceMySQL [(none)]> insert into huyidb.test1 (a,b,c) values (tdsql_nextval(huyidb.s1), tdsql_nextval(huyidb.s2), tdsql_nextval(huyidb.s3)) ;Query OK, 1 row affected (0.03 sec)MySQL [(none)]> select * from huyidb.test1 ;+---+------+------+| a | b | c |+---+------+------+| 1 | 1 | 1 |+---+------+------+1 row in set (0.00 sec)#多个例使用同一个sequenceinsert into huyidb.test1 (a,b,c) values (tdsql_nextval(huyidb.s1), tdsql_nextval(huyidb.s1), tdsql_nextval(huyidb.s1)) ;MySQL [(none)]> select * from huyidb.test1 ;+---+------+------+| a | b | c |+---+------+------+| 1 | 1 | 1 || 2 | 3 | 4 |+---+------+------+2 rows in set (0.00 sec)# nocycle 达到最大值时会报错。MySQL [(none)]> truncate table huyidb.test1 ;MySQL [(none)]> insert into huyidb.test1 (a,b,c) values (tdsql_nextval(huyidb.s2), tdsql_nextval(huyidb.s2), tdsql_nextval(huyidb.s2)) ;ERROR 609 (HY000): Proxy ERROR:get sequence id from db errorMySQL [(none)]> select tdsql_lastval(huyidb.s2);+---+| 4 |+---+| 4 |+---+insert into huyidb.test1 (a,b,c) values (tdsql_nextval(huyidb.s1), tdsql_nextval(huyidb.s1), tdsql_nextval(huyidb.s3)) ;MySQL [(none)]> select tdsql_lastval(huyidb.s3);+---+| 2 |+---+| 2 |+---+ps -ef |grep gateway |grep 15002 | grep -v 'grep'| awk '{print $2}' | xargs kill -9MySQL [(none)]> truncate table huyidb.test1 ;MySQL [(none)]> insert into huyidb.test1 (a,b,c) values (tdsql_nextval(huyidb.s1), tdsql_nextval(huyidb.s1), tdsql_nextval(huyidb.s3)) ;Query OK, 1 row affected (0.01 sec)MySQL [(none)]> select tdsql_lastval(huyidb.s3);+---+| 3 |+---+| 3 |+---+1 row in set (0.00 sec)#我们登录proxy2 上插入 s3序例mysql -uhuyi -phuyi -h10.85.10.52 -P15002 -cMySQL [(none)]> insert into huyidb.test1 (a,b,c) values (tdsql_nextval(huyidb.s1), tdsql_nextval(huyidb.s1), tdsql_nextval(huyidb.s3)) ;Query OK, 1 row affected (0.02 sec)MySQL [(none)]> select tdsql_lastval(huyidb.s3);+---+| 4 |+---+| 4 |+---+
2
SEQUENCE 的查看：
#查看下一个序例值：MySQL [huyi]> select next value for test.s3 ;或MySQL [huyi]> select tdsql_nextval(huyidb.s3);#查看当前序例值：MySQL [huyi]> select tdsql_lastval(huyidb.s3);
其它待确认问题：
创建sequence时，cache 关键字未生效
Sequence 创建成功，mysql.tdsql_sequences无数据
Sequence每次从DB中取值对并发性能的影响
Sequence 的maxvalue通过proxy 限制
