mysql的id乱序要重排_基于MySQL自增ID字段增量扫描研究

最新推荐文章于 2023-05-19 20:59:53 发布

原创最新推荐文章于 2023-05-19 20:59:53 发布 · 285 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql的id乱序要重排

这篇博客展示了关于MySQL数据库的一系列操作，包括查询表中记录的数量，使用LIMIT进行分页，按自增ID和唯一索引进行排序，以及更新表中记录的例子。在不同场景下，查询结果的顺序和限制数量对结果集的影响被详细探讨。

// 总共8条记录

MySQL [test]> SELECT COUNT(1) FROM tableA2;

+----------+

| COUNT(1) |

+----------+

| 8 |

+----------+

1 row in set (0.00 sec)

// 按自增ID无序，但按唯一索引有序

MySQL [test]>SELECT * FROM tableA2;

+----+----+----+----+

| id | af | bf | cf |

+----+----+----+----+

| 1 |1| 2 | 1 |

| 2 | 2 | 1 | 2 |

| 3 |3| 3 | 3 |

| 12 | 11 | 12 | 12 |

| 11 |12| 11 | 11 |

| 13 | 13 | 16 | 13 |

| 14 |14| 17 | 14 |

| 5 | 15 | 5 | 5 |

+----+----+----+----+

8 rows in set (0.00 sec)

// 按自增ID无序，但按唯一索引有序

MySQL [test]>SELECT * FROM tableA2 WHERE id>=1 LIMIT 10;

+----+----+----+----+

| id | af | bf | cf |

+----+----+----+----+

| 1 |1| 2 | 1 |

| 2 | 2 | 1 | 2 |

| 3 |3| 3 | 3 |

| 12 | 11 | 12 | 12 |

| 11 |12| 11 | 11 |

| 13 | 13 | 16 | 13 |

| 14 |14| 17 | 14 |

| 5 | 15 | 5 | 5 |

+----+----+----+----+

8 rows in set (0.00 sec)

// 按自增ID无序，但按唯一索引有序

MySQL [test]>SELECT * FROM tableA2 WHERE id>=2 LIMIT 10;

+----+----+----+----+

| id | af | bf | cf |

+----+----+----+----+

| 2 |2| 1 | 2 |

| 3 | 3 | 3 | 3 |

| 12 |11| 12 | 12 |

| 11 | 12 | 11 | 11 |

| 13 |13| 16 | 13 |

| 14 | 14 | 17 | 14 |

| 5 |15| 5 | 5 |

+----+----+----+----+

7 rows in set (0.00 sec)

// 按自增ID有序，但按唯一索引无序(LIMIT数小于表总记录数)

MySQL [test]>SELECT * FROM tableA2 WHERE id>=2 LIMIT 5;

+----+----+----+----+

| id | af | bf | cf |

+----+----+----+----+

|2| 2 | 1 | 2 |

| 3 | 3 | 3 | 3 |

|5| 15 | 5 | 5 |

| 11 | 12 | 11 | 11 |

|12| 11 | 12 | 12 |

+----+----+----+----+

5 rows in set (0.00 sec)

// 按自增ID有序，但按唯一索引无序(LIMIT数小于表总记录数)

MySQL [test]>SELECT * FROM tableA2 WHERE id>=1 LIMIT 7;

+----+----+----+----+

| id | af | bf | cf |

+----+----+----+----+

|1| 1 | 2 | 1 |

| 2 | 2 | 1 | 2 |

|3| 3 | 3 | 3 |

| 5 | 15 | 5 | 5 |

|11| 12 | 11 | 11 |

| 12 | 11 | 12 | 12 |

|13| 13 | 16 | 13 |

+----+----+----+----+

7 rows in set (0.00 sec)

// 更新一条记录

MySQL [test]>UPDATE tableA2 SET id=15 WHERE id=12;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

// 按自增ID是无序的

MySQL [test]>SELECT * FROM tableA2 LIMIT 7;

+----+----+----+----+

| id | af | bf | cf |

+----+----+----+----+

| 1 | 1 | 2 | 1 |

| 2 | 2 | 1 | 2 |

| 3 | 3 | 3 | 3 |

| 15 | 11 | 12 | 12 |

| 11 | 12 | 11 | 11 |

| 13 | 13 | 16 | 13 |

| 14 | 14 | 17 | 14 |

+----+----+----+----+

7 rows in set (0.00 sec)

// 按自增ID是有序的(LIMIT数小于表记录数)

// 按唯一自增ID无序

MySQL [test]>SELECT * FROM tableA2 WHERE id>=1 LIMIT 7;

+----+----+----+----+

| id | af | bf | cf |

+----+----+----+----+

|1| 1 | 2 | 1 |

| 2 | 2 | 1 | 2 |

|3| 3 | 3 | 3 |

| 5 | 15 | 5 | 5 |

|11| 12 | 11 | 11 |

| 13 | 13 | 16 | 13 |

|14| 14 | 17 | 14 |

+----+----+----+----+

7 rows in set (0.00 sec)

// 按自增ID是无序的(LIMIT数等于或大于表记录数)

// 按唯一自增ID有序

MySQL [test]>SELECT * FROM tableA2 WHERE id>=1 LIMIT 8;

+----+----+----+----+

| id | af | bf | cf |

+----+----+----+----+

|1| 1 | 2 | 1 |

| 2 | 2 | 1 | 2 |

|3| 3 | 3 | 3 |

| 15 | 11 | 12 | 12 |

|11| 12 | 11 | 11 |

| 13 | 13 | 16 | 13 |

|14| 14 | 17 | 14 |

| 5 | 15 | 5 | 5 |

+----+----+----+----+

8 rows in set (0.00 sec)