Go How MySQL sets the auto-grow sequence SEQUENCE

Original address: 70148516

Solution: Because MySQL does not have sequence, so write a table (EMP_SEQ) that stores sequence, and then manually insert a piece of data and finally customize a function to handle the value to grow.

1. Create a table Emp_seq to hold the sequence value:

Description:

• The Name column is intended to allow the function to perform DML operations better through conditions,
• Start_value, which stores the value at the beginning of the increment sequence,
• Increment_value, which stores the value of the self-increment sequence stepping.

2. Create table Emp_seq (
3. Name varchar () not Null primary key,
4. Start_value int not null,
5. Increment_value int not null default 1
6. );

2. Insert the data manually:

Description:

• ' Empno ' The meaning of the name,
• The first 1, indicating that the sequence starts at 1,
• The second 1 shows that the sequence grows by 1 each time.

insert into emp_seq values(‘empno‘,1,1);

3. Define the function Nextval:

Description:

• DELIMITER//-Define the statement terminator, where//preceded by a space, we usually use semicolons; as a statement terminator. However, the created function, is a combination of multiple statements, each statement is also a semicolon, as a statement terminator, if the statement terminator is not redefined, the database will be considered declare I int; Here the semicolon is Terminator, will be an error. So here we redefine a statement terminator//until end;//ends the entire code.
  
• function custom Functions (user-defined function UDF), which is an extension of the MySQL function,
• Declare declaring variables

4. DELIMITER//
6. Create function nextval (str varchar) returns integer
8. Begin
10. declare i int;
12. Set i= (select Start_value from emp_seq where name=str);
14. Update Emp_seq
16. Set Start_value=i+increment_value
18. where name=str;
20. return i;
22. End
24. //

4. Restore the default statement terminator:

Description:

DELIMITER; --Define the statement terminator (where delimiter is followed by a semicolon), which is the semicolon as the statement terminator. Because the function has already been set up, so here to redefine the Terminator, in order to make it easier to execute SQL statements later, we usually use the SQL statement, used to use a semicolon as a terminator.

1. DELIMITER;

5. For more convenient execution of SQL commands, I copied the code into Notepad and saved the file to the D-Packing directory, renamed Emp_seq.sql 6. Executes an external SQL script command without prompting, stating that the build was successful. How to execute external SQL script commands, sir: MySQL executes commands for external SQL script files 7. After the SQL script is successfully imported, we need to verify, enter MySQL, and enter the database, I am jsd170101

OK, sequential execution of the SQL statement to get the sequence, from a clear view, the result starts from 1, each time you increment 1

8. Here's how to apply a DML statement in my database that already has empno=6 data, here I let start_value grow to 7, and then insert a message: View the results:

Go How MySQL sets the auto-grow sequence SEQUENCE