MySQL implementation sequence (Sequence) effect

MySQL implementation sequence effect

The general sequence (Sequence) is used to process primary key fields, and there is no sequence in MySQL, but MySQL provides self-growth (increment) for similar purposes, but only self-increment, and cannot set step size, start index, loop, etc. The most important thing is that a table can only be used by one field of self-increment, but sometimes we need two or two more fields to achieve self-increment (single-table multi-field self-increment), MySQL itself is not implemented, but we can use to create a sequence table, using the function to get the value of the sequence.

1. Create a new sequence table

drop table if exists  sequence ;   create table sequence (       seq_name         VARCHAR (50)  NOT NULL ,  -- 序列名称       current_val      INT NOT NULL ,  -- 当前值       increment_val    INT NOT NULL DEFAULT 1,  -- 步长(跨度)       PRIMARY KEY (seq_name)   );2. Add a sequence INSERT INTO sequence VALUES ( ‘seq_test1_num1‘ , ‘0‘ , ‘1‘ ); INSERT INTO sequence VALUES ( ‘seq_test1_num2‘ , ‘0‘ , ‘2‘ );3. Create a function to get the current value of the sequence (the V_seq_name parameter value represents the sequence name) create function currval(v_seq_name VARCHAR (50))   returns integer begin      declare value integer ;            set value = 0;            select current_val into value  from sequence where seq_name = v_seq_name;     return value; end ;4. Querying the current value select currval( ‘seq_test1_num1‘ );5. Create a function to get the next value of the sequence (the V_seq_name parameter value represents the sequence name) create function nextval (v_seq_name VARCHAR (50))      returns integer begin      update sequence set current_val = current_val + increment_val  where seq_name = v_seq_name;      return currval(v_seq_name); end ;6. Query the next value select nextval( ‘seq_test1_num1‘ );7. New Table for testing DROP TABLE IF EXISTS `test1`; CREATE TABLE `test1` (    ` name ` varchar (255) NOT NULL ,    `value` double (255,0) DEFAULT NULL ,    `num1` int (11) DEFAULT NULL ,    `num2` int (11) DEFAULT NULL ,    PRIMARY KEY (` name `) );8. Create a new trigger insert the field self-increment effect by assigning a value to the self-increment field before inserting a record CREATE TRIGGER `TRI_test1_num1` BEFORE INSERT ON `test1` FOR EACH ROW BEGIN set NEW.num1 = nextval( ‘seq_test1_num1‘ ); set NEW.num2 = nextval( ‘seq_test1_num2‘ ); END9. Final Test of the self-amplification effect INSERT INTO test1 ( name , value) VALUES ( ‘1‘ , ‘111‘ ); INSERT INTO test1 ( name , value) VALUES ( ‘2‘ , ‘222‘ ); INSERT INTO test1 ( name , value) VALUES ( ‘3‘ , ‘333‘ ); INSERT INTO test1 ( name , value) VALUES ( ‘4‘ , ‘444‘ );10. Results show SELECT * FROM test1;Reprinted from Https://www.2cto.com/database/201508/427101.html

MySQL implementation sequence (Sequence) effect