MySQL to achieve sequence effect

Generally, sequence is used to process primary key fields. There is no sequence in mysql, but MySQL provides an increment to achieve similar purposes, but it is only an increment, and cannot set step size, start index, cycle, etc. the most important thing is that a table can only be used by one field, but sometimes we need two or more fields To realize auto increment (single table and multi field auto increment), MySQL itself can't realize it, but we can use to create a sequence table and use functions to obtain the sequence values

1. Create a new sequence table

1. drop table if exists sequence;     
2. create table sequence (         
3. seq_name        VARCHAR(50) NOT NULL, -- Sequence name         
4. current_val     INT         NOT NULL, -- Current value         
5. increment_val   INT         NOT NULL    DEFAULT 1, -- step(span)         
6. PRIMARY KEY (seq_name)   );   

2. Add a sequence

1. INSERT INTO sequence VALUES ('seq_test1_num1', '0', '1');  
2. INSERT INTO sequence VALUES ('seq_test1_num2', '0', '2');  

3. Create a function to get the current value of the sequence (the value of the V ﹣ SEQ ﹣ name parameter represents the sequence name)

1. create function currval(v_seq_name VARCHAR(50))     
2. returns integer    
3. begin        
4.     declare value integer;         
5.     set value = 0;         
6.     select current_val into value  from sequence where seq_name = v_seq_name;   
7.    return value;   
8. end;  

4. Query the current value

1. select currval('seq_test1_num1');  

5. Create a function to get the next value of the sequence (the value of the V ﹣ SEQ ﹣ name parameter represents the sequence name)

1. create function nextval (v_seq_name VARCHAR(50))  
2.     returns integer  
3. begin  
4.     update sequence set current_val = current_val + increment_val  where seq_name = v_seq_name;  
5.     return currval(v_seq_name);  
6. end;  

6. Query next value

1. select nextval('seq_test1_num1');  

7. create a new table for testing

1. DROP TABLE IF EXISTS `test1`;  
2. CREATE TABLE `test1` (  
3.   `name` varchar(255) NOT NULL,  
4.   `value` double(255,0) DEFAULT NULL,  
5.   `num1` int(11) DEFAULT NULL,  
6.   `num2` int(11) DEFAULT NULL,  
7.   PRIMARY KEY (`name`)  
8. );  

8. Before inserting a new record into a new trigger, assign a value to the auto increment field to achieve the auto increment effect

1. CREATE TRIGGER `TRI_test1_num1` BEFORE INSERT ON `test1` FOR EACH ROW BEGIN  
2. set NEW.num1 = nextval('seq_test1_num1');  
3. set NEW.num2 = nextval('seq_test1_num2');  
4. END  

9. Final test of self increasing effect

1. INSERT INTO test1 (name, value) VALUES ('1', '111');  
2. INSERT INTO test1 (name, value) VALUES ('2', '222');  
3. INSERT INTO test1 (name, value) VALUES ('3', '333');  
4. INSERT INTO test1 (name, value) VALUES ('4', '444');  

10. Results display

1. SELECT * FROM test1;