How to Set automatic growth sequence in mysql (1)

Background: As required by the project, you must use mysql to set the primary key to auto-increment and use a string. After searching online and experimenting, I finally made a set of solutions. Share it with you now! Solution: because mysql does not contain sequence, you need to manually create a table (tb_sequence) for storing sequence, insert a piece of data manually, and finally

Background: As required by the project, you must use mysql to set the primary key to auto-increment and use a string. After searching online and experimenting, I finally made a set of solutions. Share it with you now! Solution: because mysql does not contain sequence, you need to manually create a table (tb_sequence) for storing sequence, insert a piece of data manually, and finally

Background: As required by the project, you must use mysql to set the primary key to auto-increment and use a string. After searching online and experimenting, I finally made a set of solutions. Share it with you now!

Solution: because mysql does not contain sequence, you need to manually create a table (tb_sequence) for storing sequence, and then insert a data record manually, finally, define a function to process the value to be increased.

Do it together:

1. Create a table tb_sequence to store the sequence value:

[SQL]View plaincopyprint?

1. Create table tb_sequence (name varchar (50) not null, current_value int not null, _ increment int not null default 1, primary key (name ));

2. manually insert data:

[SQL]View plaincopyprint?

1. Insert into tb_sequence values ('userid', 100,2 );

3. Define function _ nextval:

[SQL]View plaincopyprint?

1. DELIMITER //
2. Create function _ nextval (n varchar (50) returns integer
3. Begin
4. Declare _ cur int;
5. Set _ cur = (select current_value from tb_sequence where name = n );
6. Update tb_sequence
7. Set current_value = _ cur + _ increment
8. Where name = n;
9. Return _ cur;
10. End;
11. //

Description: delimiter // ----> defines the statement Terminator. See other code for yourself.

4. Restore the default statement Terminator: (this can be omitted, but the Terminator must be //. It should be set back for convenience .)

[SQL]View plaincopyprint?

1. DELIMITER;

5. Inspection Results

Run the following statement multiple times:

[SQL]View plaincopyprint?

1. Select _ nextval ('userid ');

Result:

[SQL]View plaincopyprint?

1. Mysql> select _ nextval ('userid ');
2. + -------------------- +
3. | _ Nextval ('userid') |
4. + -------------------- +
5. | 1, 102 |
6. + -------------------- +
7. 1 row in set (0.00 sec)
9. Mysql> select _ nextval ('userid ');
10. + -------------------- +
11. | _ Nextval ('userid') |
12. + -------------------- +
13. | 1, 104 |
14. + -------------------- +
15. 1 row in set (0.00 sec)
17. Mysql> select _ nextval ('userid ');
18. + -------------------- +
19. | _ Nextval ('userid') |
20. + -------------------- +
21. | 1, 106 |
22. + -------------------- +
23. 1 row in set (0.00 sec)

6. The experiment is over.