Shulou(Shulou.com)06/02 Report--

Editor to share with you how to update sort values and stored procedures update sort values in MySQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

The company has tables Task and Question

The Question table is roughly as follows

Id,bigint (20) student_id,bigint (20) task_id,bigint (20) name,varchar (100) ranking,int (11) not null default 999 comment 'sort together according to ranking and id'

As shown above, when querying, you only need sql to add a sort, order by ranking, id, so that you don't have to get the maximum ranking value (or count) when you add it.

Now add new requirements. When adding, deleting and correcting ranking, it is best to reorder the data (from 1 to the end). You need to sort the existing data:

# define a variable ranking first, and add set @ ranking = 0 update task_question set ranking = (@ ranking: = @ ranking+1) where task_id = # {Task table primary key} order by ranking, id when update

But the above sql can only update the data of a certain Task. I need to update all the Task here, so I write a stored procedure:

# delimiter $$? drop procedure if exists test; # if there is a procedure named test, delete create procedure test () # create (the keyword used to create the function is function function name ()) begin declare taskId bigint; declare flag int default 0; # this is the point, define a cursor to record the results of the sql query (here the knowledge points also include the fuzzy query of SQL, see supplement) declare taskList cursor for select id from task # create an exit flag for the following while loop. When the cursor is traversed, set the value of flag to 1 declare continue handler for not found set flag=1; open taskList; # Open the cursor # assign the value in the cursor to the defined variable, and implement the key point of the for loop: fetch taskList into taskId; while flag 1 do # set a local variable ranking set @ ranking = 0 here Update task_question set ranking = (@ ranking: = @ ranking+1) where task_id = taskId order by ranking, id; # cursor moves back fetch taskList into taskId; end while; close taskList; # closes cursor end;# $$# executes stored procedure call test (); the above is all the content of the article "how to update sort values in MySQL and stored procedures update sort values". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.