ALTER SEQUENCE

语法说明

ALTER SEQUENCE 用于修改现有序列。

语法结构

  1. > ALTER SEQUENCE [ IF EXISTS ] SEQUENCE_NAME
  2. [ AS data_type ]
  3. [ INCREMENT [ BY ] increment ]
  4. [ MINVALUE minvalue] [ MAXVALUE maxvalue]
  5. [ START [ WITH ] start ] [ [ NO ] CYCLE ]

语法释义

  • [ IF EXISTS ]：可选的子句，表示如果指定的序列不存在，也不会引发错误。如果使用了此子句，系统将检查序列是否存在，如果不存在，将忽略修改请求。

  • SEQUENCE_NAME：要修改的序列的名称。

  • [ AS data_type ]：可选子句，它允许您为序列指定数据类型。通常，序列的数据类型是整数。

  • [ INCREMENT [ BY ] increment ]：这是指定序列的增量值。序列的增量值是在每次递增或递减时要添加到当前值的数量。如果未指定增量值，通常默认为 1。

  • [ MINVALUE minvalue ]：这是序列的最小值，它指定了序列允许的最小值。如果指定了最小值，序列的当前值不能低于此值。

  • [ MAXVALUE maxvalue ]：这是序列的最大值，它指定了序列允许的最大值。如果指定了最大值，序列的当前值不能超过此值。

  • [ START [ WITH ] start ]：这是序列的起始值，它指定序列的初始值。如果未指定起始值，通常默认为 1。

  • [ [ NO ] CYCLE ]：可选子句，用于指定是否循环使用序列值。如果指定了 NO CYCLE，则在达到最大值或最小值后，序列将停止递增或递减。如果未指定此子句，通常默认为不循环。

示例

  1. -- 创建一个名为 alter_seq_01 的序列，将序列的增量设置为 2，设置序列的最小值为 30，最大值为 100，并启用循环
  2. create sequence alter_seq_01 as smallint increment by 2 minvalue 30 maxvalue 100 cycle;
  4. mysql> show sequences;
  5. +--------------+-----------+
  6. | Names        | Data Type |
  7. +--------------+-----------+
  8. | alter_seq_01 | SMALLINT  |
  9. +--------------+-----------+
  10. 1 row in set (0.00 sec)
  12. mysql> alter sequence alter_seq_01 as bigint;
  13. Query OK, 0 rows affected (0.01 sec)
  15. mysql> show sequences;
  16. +--------------+-----------+
  17. | Names        | Data Type |
  18. +--------------+-----------+
  19. | alter_seq_01 | BIGINT    |
  20. +--------------+-----------+
  21. 1 row in set (0.00 sec)
  23. -- 取消序列 alter_seq_01 的循环
  24. mysql> alter sequence alter_seq_01 no cycle;
  25. Query OK, 0 rows affected (0.01 sec)
  27. mysql> select nextval('alter_seq_01'),currval('alter_seq_01');
  28. +-----------------------+-----------------------+
  29. | nextval(alter_seq_01) | currval(alter_seq_01) |
  30. +-----------------------+-----------------------+
  31. | 30                    | 30                    |
  32. +-----------------------+-----------------------+
  33. 1 row in set (0.01 sec)
  35. mysql> select nextval('alter_seq_01'),currval('alter_seq_01');
  36. +-----------------------+-----------------------+
  37. | nextval(alter_seq_01) | currval(alter_seq_01) |
  38. +-----------------------+-----------------------+
  39. | 32                    | 32                    |
  40. +-----------------------+-----------------------+
  41. 1 row in set (0.00 sec)
  43. -- 将序列 alter_seq_01 的起始值设置为 40
  44. mysql> alter sequence alter_seq_01 start with 40;
  45. Query OK, 0 rows affected (0.01 sec)
  47. mysql> select nextval('alter_seq_01'),currval('alter_seq_01');
  48. +-----------------------+-----------------------+
  49. | nextval(alter_seq_01) | currval(alter_seq_01) |
  50. +-----------------------+-----------------------+
  51. | 40                    | 40                    |
  52. +-----------------------+-----------------------+
  53. 1 row in set (0.01 sec)
  55. mysql> select nextval('alter_seq_01'),currval('alter_seq_01');
  56. +-----------------------+-----------------------+
  57. | nextval(alter_seq_01) | currval(alter_seq_01) |
  58. +-----------------------+-----------------------+
  59. | 42                    | 42                    |
  60. +-----------------------+-----------------------+
  61. 1 row in set (0.00 sec)
  63. -- 将序列 alter_seq_01 的增量值设置为 3
  64. mysql> alter sequence alter_seq_01 increment by 3;
  65. Query OK, 0 rows affected (0.01 sec)
  67. mysql> select nextval('alter_seq_01'),currval('alter_seq_01');
  68. +-----------------------+-----------------------+
  69. | nextval(alter_seq_01) | currval(alter_seq_01) |
  70. +-----------------------+-----------------------+
  71. | 40                    | 40                    |
  72. +-----------------------+-----------------------+
  73. 1 row in set (0.00 sec)
  75. mysql> select nextval('alter_seq_01'),currval('alter_seq_01');
  76. +-----------------------+-----------------------+
  77. | nextval(alter_seq_01) | currval(alter_seq_01) |
  78. +-----------------------+-----------------------+
  79. | 43                    | 43                    |
  80. +-----------------------+-----------------------+
  81. 1 row in set (0.00 sec)