Trang chủ » MySQL » Hướng dẫn sử dụng MySQL generated columns để lưu dữ liệu được tính toán từ 1 biểu thức

MySQL

Hướng dẫn sử dụng MySQL generated columns để lưu dữ liệu được tính toán từ 1 biểu thức

09/12/2023

Bài viết này chúng ta sẽ tìm hiểu cách sử dụng MySQL generated columns để lưu dữ liệu được tính toán từ 1 biểu thức hoặc các cột khác các bạn nhé.

Giới thiệu về MySQL generated column

Khi bạn tạo mới 1 bảng, bạn chỉ rõ các cột của bảng trong lệnh CREATE TABLE. Sau đó dùng các lệnh INSERT, UPDATE, và DELETE để sửa đổi trực tiếp dữ liệu trong từng cột của bảng.

Trong MySQL 5.7 giới thiệu 1 tính năng mới gọi là generated column. Lý do tên là thế vì dữ liệu trong cột được tính toán dựa vào các biểu thức được định nghĩa từ trước hoặc từ các cột khác.

Ví dụ bảng customers với cấu trúc như sau:


CREATE TABLE IF NOT EXISTS customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

Sử dụng hàm CONCAT() lấy tên đầy đủ:


SELECT 
    id, CONCAT(first_name, ' ', last_name), email
FROM
    customers;

Lệnh này vẫn chưa phải là tốt nhất.

Lệnh sau sử dụng generated column để tạo bảng customers:


DROP TABLE IF EXISTS customers;
 
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    fullname varchar(101) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name)),
    email VARCHAR(100) NOT NULL
);

Trong đó GENERATED ALWAYS AS (expression) là cú pháp để tạo ra 1 generated column.

Test lại cột fullname


INSERT INTO customers(first_name,last_name, email)
VALUES('viet','nguyen','toilamitdotcom@gmail.com');

SELECT 
    *
FROM
    customers;

Như ở trên thì giá trị cột fullname được tính toán dựa trên truy vấn từ bảng customers.

Có 2 loại generated columns: storedvirtual.

  • stored: được tính toán và lưu vào bộ nhớ vật lý và chỉ tính toán lại khi dữ liệu cập nhật.
  • virtual: luôn tính toán khi dữ liệu được đọc.

Ở ví dụ trên thì cột fullname là cột virtual

Cú pháp của MySQL generated column


column_name data_type [GENERATED ALWAYS] AS (expression)
   [VIRTUAL | STORED] [UNIQUE [KEY]]

MySQL mặc định kiểu generated columnVIRTUAL

expression có thể gồm literals, built-in functions with no parameters, operators, hoặc tham chiếu tới bất kỳ cột nào trong cùng 1 bảng. Nếu là function thì bắt buộc là scalardeterministic.

Nếu generated column được lưu thì bạn có thể xác định 1 unique constraint cho nó.

Ví dụ về MySQL stored column

Chúng ta sẽ sử dụng bảng products trong database mẫu để làm ví dụ.

Dữ liệu từ 2 cột quantityInStockbuyPrice dùng để tính tiền hàng bằng biểu thức.


quantityInStock * buyPrice

Tuy nhiên bạn có thể thêm 1 stored generated column với tên là stock_value vào bảng products sử dụng lệnh ALTER TABLE ...ADD COLUMN:


ALTER TABLE products
ADD COLUMN stockValue DOUBLE 
GENERATED ALWAYS AS (buyprice*quantityinstock) STORED;

Thông thường, lệnh ALTER TABLE sẽ rebuild lại toàn bộ bảng, do đó sẽ tốn thời gian nếu bạn thay đổi bảng lớn. Tuy nhiên thì đây không phải là trường hợp dành cho virtual column.

Bây giờ bạn có thể truy vấn tiền hàng trực tiếp từ bảng products.


SELECT 
    productName, ROUND(stockValue, 2) AS stock_value
FROM
    products;

Như vậy với generated column sẽ giúp bạn tạo ra các dữ liệu cần thiết mà không cần phải sử dụng nhiều câu query. Hy vọng bài viết hữu ích cho các bạn.

 

Bài viết liên quan


Có nhiều loại dữ liệu khác nhau được hỗ trợ trong MySQL. Trong số đó đôi khi chúng ta cần dùng đến kiểu dữ liệu DATE để lưu trữ giá trị dữ liệu. Kiểu dữ liệu DATE được sử dụng cho các giá trị có phần ngày nhưng không có phần thời gian.

22:38 . 10/01/2024

Hàm WEEKDAY() trong MySQL được sử dụng để tìm giá trị ngày trong tuần cho một ngày cụ thể. Nếu ngày là NULL thì hàm WEEKDAY() sẽ trả về NULL. Ngược lại, nó trả về chỉ mục cho một ngày, tức là 0 cho Thứ Hai, 1 cho Thứ Ba,... 6 cho Chủ Nhật.

22:38 . 30/12/2023

Hàm SEC_TO_TIME() trong MySQL được sử dụng để chuyển đổi một giá trị thời gian (trong đơn vị giây) thành dạng chuỗi thời gian.

22:33 . 30/12/2023

Hàm YEARWEEK() trong MySQL được sử dụng để trả về một số nguyên đại diện cho tuần trong năm, dựa trên một giá trị ngày hoặc ngày/tháng cung cấp

22:30 . 30/12/2023

Hàm YEAR() trong MySQL được sử dụng để trích xuất phần năm từ một giá trị ngày hoặc ngày/tháng

22:10 . 30/12/2023

Hàm LTRIM() trong MySQL được sử dụng để loại bỏ các ký tự khoảng trắng từ phía bên trái (bắt đầu) của một chuỗi ký tự.

22:03 . 30/12/2023

Hàm DAYOFWEEK() trong MySQL được sử dụng để trả về một số nguyên đại diện cho ngày trong tuần (từ 1 đến 7), với Chủ nhật là ngày đầu tiên (1) và Thứ Bảy là ngày cuối cùng (7).

09:01 . 30/12/2023

Hàm DATE_SUB() trong MySQL được sử dụng để trừ một khoảng thời gian cụ thể từ một giá trị ngày/tháng cung cấp.

08:52 . 30/12/2023

Hàm DATE_FORMAT() trong MySQL được sử dụng để định dạng một ngày cụ thể theo giá trị định dạng đã cho, tức là, một ngày sẽ được cung cấp và hàm này sẽ định dạng ngày đó theo các tham số định dạng đã chỉ định.

 

0988 542 856
0988 542 856