Effective way to update more than 10000 row of data in MYSQL?

Question

I would like to query a table, and update the table data, but the data more than 10000 row, so what is the effective way? My code now:

+----+------+---------+
|id   | rank | newrank |
+----------------------+
|1    | 1    | 0       |
|2..  | 2    | 0       |
|10000| 10000| 0       |
+----------------------+
//I am using a template language, DB::query = mysqli_query
$ss = DB::fetch_all("SELECT *FROM t1 ORDER BY rank ASC");
$x = 1;
foreach($ss as $sr){
  DB::query("UPDATE t1 SET newrank = '".$x."' WHERE id = '".$sr['id']."'");
  $x++;
}

Currently I am using the above code, but if data have to update in huge amount, the server will be crashed. Thank you.

Does this answer your question? [Updating column so that it contains the row position](https://stackoverflow.com/questions/4632696/updating-column-so-that-it-contains-the-row-position) — Andrea Olivato, May 30 '21 at 13:14
What type of `newrank` field? Is it have limited values range you can group rous by rank and use `UPDATE t1 SET newrank = '".$x."' WHERE id IN (.......)` — Slava Rozhnev, May 30 '21 at 13:36

Bill Karwin · Answer 1 · 2021-05-30T14:19:23.063

You don't need to fetch anything to do this, and you don't need to worry about the number of rows.*

Given a test table:

create table t1 (id int primary key, `rank` int);
insert into t1 values (1, null), (2, null), (3, null), (6, null);

Solution for MySQL 5.x:

set @r = 0;
update t1 set `rank` = (@r := @r + 1) order by `rank`, id;

select * from t1;
+----+------+
| id | rank |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  6 |    4 |
+----+------+

Here's a better solution for MySQL 8.0 using a window function. I'll rank them in the descending order, just so we can see that it changed the ranks:

with cte as ( select id, rank() over (order by `rank` desc, id desc) as r from t1 ) 
update cte join t1 using (id) set t1.`rank` = cte.r;

select * from t1;
+----+------+
| id | rank |
+----+------+
|  1 |    4 |
|  2 |    3 |
|  3 |    2 |
|  6 |    1 |
+----+------+

Note: I had to delimit rank with back-ticks because rank is a reserved keyword in MSQL 8.0.

* Eventually, you do need to worry about the number of rows. MySQL has a limit on the total size of a transaction, if the binary log is enabled. The total size of rows modified during a transaction is limited by the option max_binlog_cache_size. Not the number of rows, but the size of the rows in bytes.

Effective way to update more than 10000 row of data in MYSQL?

1 Answers1