分享3個(gè)MySQL查詢?nèi)菀撞鹊目?/h1>

 更新時(shí)間：2022年11月07日 09:03:50   作者：言淦  

無論你是技術(shù)大佬,還是剛?cè)胄械男“?時(shí)不時(shí)都會(huì)踩到Mysql數(shù)據(jù)庫的坑這篇文章主要給大家介紹了關(guān)于3個(gè)MySQL查詢?nèi)菀撞鹊目?文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

目錄

• 前言
• 1、無 Order by 的隨機(jī)問題
• 2、Order by + Limit 的隨機(jī)問題
• 3、聚合函數(shù) + Limit的不準(zhǔn)確問題
• 總結(jié)

前言

在使用MySQL的過程中，相比大家都踩過不少坑，下面我將列舉日常生活中比較高頻的三個(gè)”坑“，也歡迎大家踴躍分享自己的心路歷程 ??！

1、無 Order by 的隨機(jī)問題

簡單來說，就是在沒有 Order By 的情況下，如果SELECT的字段不同，返回的記錄順序是隨機(jī)的，不一定一樣。

因?yàn)榉祷氐挠涗涰樞蚋?strong>插入順序、主鍵順序、以及字段上是否建立了索引等因素都有關(guān)聯(lián)，可以通過下面的例子進(jìn)行驗(yàn)證：

假設(shè)有這樣的一張用戶點(diǎn)擊表：

create table t_user_click
(
    id         int(11) auto_increment primary key,
    obj        varchar(64) default ''                not null comment '點(diǎn)擊對象',
    click      smallint    default 0                 not null comment '點(diǎn)擊數(shù)',
    remark     varchar(64) default ''                not null comment '備注',
    created_at timestamp   default CURRENT_TIMESTAMP not null comment '創(chuàng)建時(shí)間',
    updated_at timestamp   default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新時(shí)間'
)
    comment '用戶點(diǎn)擊表';

插入幾條測試數(shù)據(jù)：

INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (1, 'aaa', 10, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57');
INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (2, 'bbb', 20, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57');
INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (3, 'ccc', 30, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57');
INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (4, 'ddd', 40, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57');
INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (5, 'eee', 50, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57');
INSERT INTO t_user_click (id, obj, click, remark, created_at, updated_at) VALUES (6, 'fff', 60, '', '2022-10-18 14:44:57', '2022-10-18 14:44:57');

首先來看插入順序帶來的影響，在順序插入幾條順序后，不管查詢多少次，獲取的結(jié)果都與插入順序一致：

# 全部字段
mysql> select * from t_user_click where click > 0;
+----+-----+-------+--------+---------------------+---------------------+
| id | obj | click | remark | created_at          | updated_at          |
+----+-----+-------+--------+---------------------+---------------------+
|  1 | aaa |    10 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  2 | bbb |    20 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  3 | ccc |    30 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  4 | ddd |    40 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
...

# 與上述獲取全部字段的SQL結(jié)果一致
mysql> select id, click from t_user_click where click > 0;
...

其次來看主鍵順序這個(gè)因素，當(dāng)該表進(jìn)行過 DELETE/REPLACE/UPDATE 操作時(shí)，不會(huì)再按照插入順序排序了，而是會(huì)按照主鍵ID進(jìn)行排序。

# 進(jìn)行update操作
UPDATE t_user_click SET id=7 WHERE id=3;

# 此時(shí)按照主鍵排序，obj=ccc 變成最后一條，而不是第3條
mysql> select * from t_user_click where click > 0;
+----+-----+-------+--------+---------------------+---------------------+
| id | obj | click | remark | created_at          | updated_at          |
+----+-----+-------+--------+---------------------+---------------------+
|  1 | aaa |    10 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  2 | bbb |    20 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  4 | ddd |    40 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  5 | eee |    50 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  6 | fff |    60 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  7 | ccc |    30 |        | 2022-10-18 14:44:57 | 2022-10-18 14:46:20 |
+----+-----+-------+--------+---------------------+---------------------+

# 與上述獲取全部字段的SQL結(jié)果一致
mysql> select id, click from t_user_click where click > 0;
...

最后來看有無索引這個(gè)因素，當(dāng)我們在 click 字段上建立索引時(shí)，結(jié)果又不一樣了：

# 建索引
create index t_user_click_click_index on t_user_click (click);

# id=7 在最后一條
mysql> select * from t_user_click where click > 0;
+----+-----+-------+--------+---------------------+---------------------+
| id | obj | click | remark | created_at          | updated_at          |
+----+-----+-------+--------+---------------------+---------------------+
|  1 | aaa |    10 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  2 | bbb |    20 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  4 | ddd |    40 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  5 | eee |    50 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  6 | fff |    60 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  7 | ccc |    30 |        | 2022-10-18 14:44:57 | 2022-10-18 14:46:20 |
+----+-----+-------+--------+---------------------+---------------------+
6 rows in set (0.00 sec)

# id=7 在第3條
mysql> select id, click from t_user_click where click > 0;
+----+-------+
| id | click |
+----+-------+
|  1 |    10 |
|  2 |    20 |
|  7 |    30 |
|  4 |    40 |
|  5 |    50 |
|  6 |    60 |
+----+-------+

# 我們可以通過 explain 命令來看兩種查詢方式的不同：
# 兩種方式一個(gè)沒走索引，一個(gè)走了索引，從而導(dǎo)致返回結(jié)果的不同
mysql> explain select * from t_user_click where click > 0;
type：ALL
possible_keys：t_user_click_click_index
key：NULL
Extra：Using where

mysql> explain select id, click from t_user_click where click > 0;
type：index
possible_keys：t_user_click_click_index
key：t_user_click_click_index
Extra：Using where; Using index

2、Order by + Limit 的隨機(jī)問題

簡單來說就是進(jìn)行**Order by的字段如果不唯一，則MySQL返回的記錄是隨機(jī)的，常見的表現(xiàn)就是數(shù)據(jù)分頁后出現(xiàn)重復(fù)**。

不過，這種隨機(jī)也不是隨機(jī)算法那種打亂的隨機(jī)，它跟數(shù)據(jù)的插入順序，以及索引的建立也有一定關(guān)系，可以看看下面的例子。

假設(shè)有一張這樣的用戶表：

create table t_user_list
(
    id         int(11) auto_increment primary key,
    name       varchar(64) default ''                not null comment '名稱',
    age        smallint    default 0                 not null comment '年齡',
    created_at timestamp   default CURRENT_TIMESTAMP not null comment '創(chuàng)建時(shí)間',
    updated_at timestamp   default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新時(shí)間'
)
    comment '用戶表';

當(dāng)你按順序插入測試數(shù)據(jù)，然后再去分頁查詢，你可能會(huì)發(fā)現(xiàn)每次返回的結(jié)果都是固定的，也不會(huì)出現(xiàn)隨機(jī)的情況（這時(shí)候跟插入順序有關(guān)）。

# 插入測試數(shù)據(jù)
INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (1, 'aaa', 1, '2022-10-18 12:55:19', '2022-10-18 12:55:18');
INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (2, 'bbb', 2, '2022-10-18 12:55:18', '2022-10-18 12:55:18');
INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (3, 'ccc', 3, '2022-10-18 12:55:18', '2022-10-18 12:55:18');
INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (4, 'ddd', 4, '2022-10-18 12:55:18', '2022-10-18 12:55:18');
INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (5, 'eee', 5, '2022-10-18 12:55:18', '2022-10-18 12:55:18');
INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (6, 'ggg', 6, '2022-10-18 12:55:19', '2022-10-18 12:55:19');
INSERT INTO t_user_list (id, name, age, created_at, updated_at) VALUES (7, 'iii', 7, '2022-10-18 12:55:17', '2022-10-18 12:55:19');

# 分頁查詢
mysql> select * from t_user_list order by created_at limit 0, 3;
+----+------+-----+---------------------+---------------------+
| id | name | age | created_at          | updated_at          |
+----+------+-----+---------------------+---------------------+
|  7 | iii  |   7 | 2022-10-18 12:55:17 | 2022-10-18 12:55:19 |
|  2 | bbb  |   2 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
|  3 | ccc  |   3 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
+----+------+-----+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from t_user_list order by created_at limit 3, 3;
+----+------+-----+---------------------+---------------------+
| id | name | age | created_at          | updated_at          |
+----+------+-----+---------------------+---------------------+
|  4 | ddd  |   4 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
|  5 | eee  |   5 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
|  1 | aaa  |   1 | 2022-10-18 12:55:19 | 2022-10-18 12:55:18 |
+----+------+-----+---------------------+---------------------+
3 rows in set (0.00 sec)

而當(dāng)你這時(shí)候新建一個(gè) created_at 索引，并重新插入新的數(shù)據(jù)時(shí)，如果你再次查詢，你會(huì)驚奇地發(fā)現(xiàn)數(shù)據(jù)重復(fù)了。

# 新建索引
create index t_user_list_created_at_index on t_user_list (created_at);

# 插入新的測試數(shù)據(jù)
INSERT INTO db_article.t_user_list (id, name, age, created_at, updated_at) VALUES (8, 'jjj', 8, '2022-10-18 12:55:18', '2022-10-18 12:55:19');
INSERT INTO db_article.t_user_list (id, name, age, created_at, updated_at) VALUES (9, 'kkk', 9, '2022-10-18 12:55:18', '2022-10-18 12:55:19');
INSERT INTO db_article.t_user_list (id, name, age, created_at, updated_at) VALUES (10, 'mmm', 10, '2022-10-18 12:55:18', '2022-10-18 12:55:19');

# 再次分頁查詢 
mysql> select * from t_user_list order by created_at limit 0, 3;
+----+------+-----+---------------------+---------------------+
| id | name | age | created_at          | updated_at          |
+----+------+-----+---------------------+---------------------+
|  7 | iii  |   7 | 2022-10-18 12:55:17 | 2022-10-18 12:55:19 |
|  2 | bbb  |   2 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
|  3 | ccc  |   3 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
+----+------+-----+---------------------+---------------------+
3 rows in set (0.00 sec)

# id=3 的記錄重復(fù)了
mysql> select * from t_user_list order by created_at limit 3, 3;
+----+------+-----+---------------------+---------------------+
| id | name | age | created_at          | updated_at          |
+----+------+-----+---------------------+---------------------+
|  3 | ccc  |   3 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
|  4 | ddd  |   4 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
|  5 | eee  |   5 | 2022-10-18 12:55:18 | 2022-10-18 12:55:18 |
+----+------+-----+---------------------+---------------------+

關(guān)于記錄重復(fù)的問題，個(gè)人的猜想是一開始沒有 created_at 索引，此時(shí)磁盤的數(shù)據(jù)塊的順序與插入順序一致，所以返回的結(jié)果一直是固定的；但是加了索引后，并且增加了 created_at 一致的幾條數(shù)據(jù)，導(dǎo)致索引重建數(shù)據(jù)塊順序發(fā)生變化，從而記錄重復(fù)（瞎猜的，大家可以一起討論下）

3、聚合函數(shù) + Limit的不準(zhǔn)確問題

簡單來說，就是在對數(shù)據(jù)進(jìn)行 Limit分頁時(shí)，同時(shí)使用聚合函數(shù)（比如SUM、COUNT等）對當(dāng)前分頁的結(jié)果進(jìn)行聚合，則最終得到的聚合結(jié)果是不準(zhǔn)確的。

還是以第一點(diǎn)的例子和數(shù)據(jù)為例：

mysql> select * from t_user_click;
+----+-----+-------+--------+---------------------+---------------------+
| id | obj | click | remark | created_at          | updated_at          |
+----+-----+-------+--------+---------------------+---------------------+
|  1 | aaa |    10 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  2 | bbb |    20 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  4 | ddd |    40 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  5 | eee |    50 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  6 | fff |    60 |        | 2022-10-18 14:44:57 | 2022-10-18 14:44:57 |
|  7 | ccc |    30 |        | 2022-10-18 14:44:57 | 2022-10-18 14:46:20 |
+----+-----+-------+--------+---------------------+---------------------+

# 沒分頁的總數(shù)
mysql> select sum(click)  from t_user_click;
+------------+
| sum(click) |
+------------+
|        210 |
+------------+
1 row in set (0.00 sec)

# 分頁后的總數(shù)
mysql> select sum(click)  from t_user_click limit 3;
+------------+
| sum(click) |
+------------+
|        210 |
+------------+

# 可以通過子查詢解決
select sum(tmp.click)  from (select click from t_user_click limit 3) as tmp;

可以看到，在有Limit的情況下，得到的聚合結(jié)果其實(shí)是所有記錄的總和，并不是三條記錄的總和。原因就是SELECT語句執(zhí)行時(shí)有一定順序，分別是 FROM、ON、JOIN、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、ORDER BY和LIMIT。

在上述SQL中，SELECT優(yōu)先執(zhí)行，即先執(zhí)行 select sum(click) from t_user_click，這時(shí)已經(jīng)得到結(jié)果為210，最終再執(zhí)行 limit 3剔除不符合要求的記錄。

總結(jié)

最后小結(jié)一下，本文主要介紹了MySQL查詢中三個(gè)比較容易踩的坑，從中我們可以得到：

1、SELECT返回的順序跟多種因素有關(guān)，如插入順序、主鍵ID順序、索引順序等，如果你返回的結(jié)果要求有順序，則記得加上 order by。

2、加上了 order by 也不要高興地太早，還需要檢查 order by 的字段是否唯一，如果不唯一，返回的結(jié)果也有可能是隨機(jī)的。

3、在進(jìn)行聚合查詢時(shí)，切記看有無 LIMIT 子句，有的話記得加上子查詢避免查詢的結(jié)果不準(zhǔn)確。

到此這篇關(guān)于3個(gè)MySQL查詢?nèi)菀撞瓤拥奈恼戮徒榻B到這了,更多相關(guān)MySQL查詢易踩的坑內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

• MySQL聯(lián)表查詢基本操作之left-join常見的坑
• mysql timestamp比較查詢遇到的坑及解決
• MySQL慢查詢的坑
• 關(guān)于mysql?left?join?查詢慢時(shí)間長的踩坑總結(jié)

• mysql
• 查詢
• 踩坑

相關(guān)文章

• 

  MySQL修煉之聯(lián)結(jié)與集合淺析

  在mysql中,最重要的就是查詢了,下面這篇文章主要給大家介紹了關(guān)于MySQL修煉之聯(lián)結(jié)與集合的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),需要的朋友可以參考下

  2021-09-09
• 

  基于mysql 默認(rèn)排序規(guī)則的坑

  這篇文章主要介紹了解決mysql 默認(rèn)排序規(guī)則的坑，具有很好的參考價(jià)值，希望對大家有所幫助。一起跟隨小編過來看不看

  2021-02-02
• 

  MySql5.7.18字符集配置圖文詳解

  本文通過圖文并茂的形式給大家介紹了mysql5.7.18字符集配置教程，非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友參考下吧

  2017-06-06
• 

  mybatis-plus如何使用sql的date_format()函數(shù)查詢數(shù)據(jù)

  這篇文章主要給大家介紹了關(guān)于mybatis-plus如何使用sql的date_format()函數(shù)查詢數(shù)據(jù)的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下

  2023-02-02
• 

  MySQL觸發(fā)器簡單用法示例

  這篇文章主要介紹了MySQL觸發(fā)器簡單用法,結(jié)合實(shí)例形式分析了mysql觸發(fā)器的創(chuàng)建、執(zhí)行、查看、刪除等相關(guān)操作技巧,需要的朋友可以參考下

  2018-03-03
• 

  mysql中怎樣使用合適的字段和字段長度

  這篇文章主要介紹了mysql中怎樣使用合適的字段和字段長度問題,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

  2023-11-11
• 

  利用MySQL函數(shù)實(shí)現(xiàn)判斷視頻擴(kuò)展名的代碼

  MySQL擁有強(qiáng)大的自定義函數(shù)功能，如下，我寫了一個(gè)用MySQL函數(shù) 判斷視頻地址是否可以手機(jī)端播放

  2012-02-02
• 

  MySQL 壓縮的使用場景和解決方案

  數(shù)據(jù)分布特點(diǎn)，決定了空間壓縮的效率，如果存入的數(shù)據(jù)的重復(fù)率較高，其壓縮率就會(huì)較高；通常情況下字符類型數(shù)據(jù)(CHAR, VARCHAR, TEXT or BLOB )具有較高的壓縮率，而一些二進(jìn)制數(shù)據(jù)或者一些已經(jīng)壓縮過的數(shù)據(jù)的壓縮率不會(huì)很好

  2017-06-06
• 

  深入解析MySQL中的Redo Log、Undo Log和Binlog

  本文詳細(xì)介紹了MySQL中的RedoLog、UndoLog和Binlog的背景、業(yè)務(wù)場景、功能、底層實(shí)現(xiàn)原理以及使用措施,通過Java代碼示例展示了如何與這些日志進(jìn)行交互,進(jìn)一步深化了對MySQL日志系統(tǒng)的理解,理解并合理使用這些日志,可以有效地提升數(shù)據(jù)庫的性能和可靠性

  2024-10-10
• 

  mysql 全文檢索中文解決方法及實(shí)例代碼

  這篇文章主要介紹了mysql 全文檢索中文解決方法及實(shí)例代碼的相關(guān)資料,需要的朋友可以參考下

  2017-02-02

最新評(píng)論