[SQL] Note 1: GROUP BY, create-update multiple records, Config MySQL

Note lại 1 vài tips khi làm việc với MySQL nào!!

GROUP BY….

Chắc hẳn khi làm việc với MySQL, chúng ta gặp rất nhiều GROUP BY và đôi khi gặp lỗi với nó.

Câu lệnh này nhóm các cột giống giá trị với nhau vào trong cột chung, tóm gọn lại, như “tìm kiếm số lượng khách hàng cho mỗi đất nước”

Lệnh này cũng hay được sử dụng với các phép toán gộp lại như COUNT, MAX, MIN, SUM, AVG để nhóm kết quả của tập kết quả khi group thành 1 hoặc nhiều cột

Cú pháp

Cú pháp chung của cú pháp này được w3schools giới thiệu như sau:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Như cú pháp chung ở trên chúng ta có thể thấy những cột được group thì mới có thể SELECT. Điều này cũng dễ hiểu, ví dụ như bạn thực hiện lấy ra tên của khách hàng đó trong 1 nhóm khách hàng của mỗi quốc gia, tên họ khác nhau, câu hỏi đặt ra là chúng ta lấy tên của ai?

ONLY_FULL_GROUP_BY mode

Theo trang chủ mysql, chế độ này từ chối các truy vấn cái mà có danh sách select, điều kiện HAVING hoặc ORDER BY có các cột kết hợp từ nhóm (nonaggregated columns) không trong GROUP BY hoặc không trong các hàm (để xác định duy nhất giá trị) bởi GROUP BY. Vì lúc đó, bạn lấy tên khách hàng nhưng nó sẽ không biết lấy theo tên nào trong cùng 1 group vì nó quá nhiều tên khác nhau.

Điều đó có nghĩa khi bạn truy vấn như sau sẽ bị báo lỗi:

Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'posts.id' 
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Chế độ này được mặc định bật kể từ MySQL 5.7, giúp truy vấn của bạn chính xác hơn, và tốt hơn hết bạn cũng nên bật nó nếu chưa bật.

Túm cái váy lại, cú pháp chuẩn của GROUP BY là khi SELECT, ORDER BY, HAVING thì các cột cần được trong GROUP BY hoặc các hàm để xác định cột duy nhất, do vậy chúng ta nên tuân theo.

/* 
Select, having, order by các cột trong GROUP BY cần phải thỏa mãn 1 trong 2 điều kiện
- Các cột được GROUP BY
- Các cột trong các hàm "gộp dữ liệu" thành 1 dữ liệu duy nhất: COUNT, MAX, MIN, SUM, AVG
*/
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

CREATE OR UPDATE 1 bản ghi

Nếu bạn muốn tạo 1 bản ghi khi khóa chưa tồn tại hoặc cập nhật nó nếu khóa đã tồn tại, bạn có thể sử dụng

INSERT ... ON DUPLICATE KEY UPDATE Statement

Ref:

• https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
• https://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update/

Ví dụ:

INSERT INTO 
   devices(name) 
VALUES 
   ('Printer') 
ON DUPLICATE KEY UPDATE name = 'Printer';

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

UPDATE nhiều bản ghi cùng 1 lúc với key khác nhau

Thông thường khi update 10 users với id khác nhau, chúng ta sẽ nghĩ ngay đến việc chạy 10 câu lệnh UPDATE

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

Chúng ta thấy câu lệnh này chỉ có thể cập nhật 1 bản ghi với điều kiện ID 1 lúc. Hmm, vậy liệu có cách nào thực hiện 1 câu lệnh mà cập nhật được 10 bản ghi.

Câu trả lời là có, bạn có thể sử dụng WHEN và CASE để làm việc này:

UPDATE config 
SET 
    config_value = CASE config_name
        WHEN 'name1' THEN 'value'
        WHEN 'name2' THEN 'value2'
        ELSE config_value
    END
WHERE
    config_name IN ('name1' , 'name2');

Trong Laravel, bạn có thể cụ thể hóa nó bởi hàm sau

public static function updateValues(array $values)
{
    $table = MyModel::getModel()->getTable();

    $cases = [];
    $ids = [];
    $params = [];

    foreach ($values as $id => $value) {
        $id = (int) $id;
        $cases[] = "WHEN {$id} then ?";
        $params[] = $value;
        $ids[] = $id;
    }

    $ids = implode(',', $ids);
    $cases = implode(' ', $cases);
    $params[] = Carbon::now();

    return \DB::update("UPDATE `{$table}` SET `value` = CASE `id` {$cases} END, `updated_at` = ? WHERE `id` in ({$ids})", $params);
}

Hoặc cài package

Ref: https://github.com/laravel/ideas/issues/575#issuecomment-300731748

Vậy khi tôi update 1k bản ghi hay 1M bản ghi, tôi cũng có thể sử dụng lệnh này. Câu trả lời là không. Câu truy vấn là một phần dữ liệu được truyền lại giữa client và máy chủ SQL, và lượng dữ liệu này được giới hạn bởi tham số max_allowed_packet trong config. Nếu bạn sử dụng 1M bản ghi, độ dài câu truy vấn trên rõ ràng rất nhiều dẫn đến lượng dữ liệu truyền giữa client và máy chủ SQL tăng vượt quá max_allowed_packet, kết quả truy vấn sẽ là quang tèo.

Thôi thì ta cứ cập nhật 10 hoặc 100 bản ghi 1 lúc thôi, nhiều hơn thì ta lại chia ra để update nhiều lần vậy :D

Điều chỉnh config mysql

Để điều chỉnh config MySQL như set các mode, điều chỉnh các tham số, bạn có thể cd vào thư mục /etc

cd /etc/mysql
vi mysql.cnf

Thư mục này chính là các cấu hình của MySQL. Mở nó ra xem có gì nào:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

Phần comment giới thiệu khá chi tiết nhỉ =)). Phần includedir là đường dẫn thư mục con cấu hình. Di chuyển vào đó và thay đổi các cấu hình bạn muốn thôi:

vi mysql.conf.d/mysqld.cnf

Nếu không thích điều chỉnh trực tiếp vào file config, bạn cũng có thể điều chỉnh qua phiên làm việc thôi, bằng cách sử dụng các hàm trong SQL

Ví dụ sau là điều chỉnh mode nè

/* Xem các mode trong SQL */
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

/* Điều chỉnh các mode trong SQL */
SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

Lấy N bản ghi mỗi nhóm khi sử dụng GROUP BY

Với SQL 8+, đã hỗ trợ DENSE_RANK

Với SQL 8- bạn có thể sử dụng SET biến

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT score,ID,rank FROM
(
    SELECT AA.*,BB.ID,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    (@curscore:=score) newscore
    FROM
    (
        SELECT * FROM
        (SELECT COUNT(1) scorecount,score
        FROM scores GROUP BY score
    ) AAA
    ORDER BY score DESC
) AA LEFT JOIN scores BB USING (score)) A;

Chi tiết: https://dba.stackexchange.com/a/198699

Nguồn tham khảo

• Đồng đội …..