MYSQL nâng cao

Ngày đăng: 2024-07-27 15:32:14

Mục lục:

1. View trong MySQL 2. Stored Procedure 3. Index trong Mysql 4. FULL TEXT SEARCH 5. Trigger trong MySQL 6. Các hàm nâng cao khác trong Mysql

1. View trong Mysql

A. View trong MySQL là gì?

Về mặt ngữ nghĩa thì View giống như là xem hoặc nhìn, nhưng ở khía cạnh hệ quản trị cơ sở dữ liệu thì View có tác dụng giống như tạo một Table ảo với các Fields và Records mà ta có thể tự định nghĩa và khác hoàn tòa với Table gốc.

View có đầy đủ các tính chất của một Table nên ta có thể truy vấn bằng cách sửu dụng các lệnh T-SQL trên nó, không những thế bạn có thể delete và tạo mới View thông qua một câu lệnh đơn giản.

Khi ta tạo một View từ một table thì nếu table đó đổi dữ liệu thì mặc nhiên View cũng sẽ thay đổi theo. Điều này thật tuyệt vời vì ta không mất nhiều công sức để cập nhật lại dữ liệu.

Để rõ hơn thì mình lấy một ví dụ thế này: Giả sử trang Frontend có một Block gọi là hiển thị 10 tin mới nhất, như vậy ta sẽ truy vấn lấy 10 tin và sắp xếp giảm dần theo ID. Nhưng bạn biết trong SQL nó sẽ duyệt toàn bộ bảng rồi mới trả về kết quả và điều này làm cho truy vấn trở nên chậm chạm.

Để giải quyết nó thì ta sẽ tạo một View gồm 10 tin mới nhất và lúc hiển thị ra chỉ cần lấy trong View nên tốc độ sẽ nhanh hơn rất nhiều lần.

+ Ưu điểm khi sử dụng View trong MySQL

Sau đây chúng ta sẽ thảo luận về những lợi ích khi sử dụng View nhé.

Khi sử dụng Database View sẽ giúp bạn đơn giản hóa các câu truy vấn phức tạp bởi vì một View được tạo ra bởi một câu truy vấn SQL, nên dữ liệu mà nó lưu trữ ta có thể gọi là thành phẩm của một công đoạn truy vấn nên giải quyết được nhiều vấn đề về tốc độ, đặc biệt là những câu truy vấn JOIN. Bạn có thể sử dụng View để che giấu đi sự phức tạp của mô hình dữ liệu trong hệ thống bởi những gì mà họ thấy chỉ là môt View rất đơn giản.

• Database View giúp giới hạn dữ liệu cho người sử dụng, nghĩa là một View sẽ có bấy nhiêu dữ liệu thôi và người dùng chỉ được thấy con số bấy nhiêu đó. Ngoài ra khi tạo View bạn có thể gán quyền truy cập sử dụng cho một nhóm nào đó. Tôi lấy một ví dụ thực tế như sau, giả sử hệ thống của bạn có phần danh sách hóa đơn và bạn muốn những nhân viên trong bộ phận Kế Toán được phép xem các hóa đơn trong tháng này thôi. Như vậy bạn sẽ tạo một View và dữ liệu của nó là danh sách hóa đơn của tháng hiện tại, sau đó gán quyền vào nhóm Kế Toán này.

• Database View giúp tăng tính bảo mật hơn bởi vì View chi đọc mà không ghi được (Read Only) nên việc hacker tấn công cập nhật dữ liệu là điều không thể.

• Database View cho phép tăng hoặc giảm các Fields tùy theo yêu cầu sử dụng bởi vì nó được tạo từ một câu truy vấn SELECT nên bạn có thể JOIN nhiều Table lại với nhau và lưu vào View.

• Database View tăng khả năng phát triển lại ứng dụng hoặc tương thích với nhiều ứng dụng chạy chung một CSDL. Ví dụ như bạn có một hệ thống dữ liệu khách hàng và một này nào đó bạn cần đổi cấu trúc thì lúc này trong Source của bạn phải đổi theo. Nhưng nếu sử dụng View thì ta có thể hạn chế được việc này.

+ Nhược điểm khi sử dụng View trong MYSQL

Ưu điểm có thì đương nhiên nhược điểm cũng phải có. Và sau đây là những nhược điểm của Database View.

• Khi truy vấn trong View có thể sẽ chậm hơn trong table

• Bị phụ thuộc vào Table gốc, nếu Table gốc thay đổi cấu trúc thì đòi hỏi View cũng phải thiết kế lại cho phù hợp

+ Database View trong MySql

MYSQL có hỗ trợ View nhưng từ phiên bản 5.X mới có và hầu hết những đặc tính của nó theo chuẩn View của năm năm 2003.

MYSQL sẽ lưu file lưu trữ View với tên là view_name.frm-00001, sau này bạn cập nhật lại bố cục của nó thì nó sẽ lưu lại là view_name.frm-00002 và cứ tăng dần như thế.

B. Đặt vấn đề về View trong MySQL

Hãy bắt đầu bằng một ví dụ, điều này sẽ giúp bạn hiểu rõ vấn đề hơn.

Giả sử ta có 2 table gồm:

• payments: Lưu lịch sử thanh toán của khách hàng

• customers: Lưu danh sách khách hàng

Cấu trúc như hình sau:

Bây giờ hãy viết câu truy vấn lấy ra tất cả khách hàng và thông tin lịch sử thanh toán của khách hàng đó. Rất đơn giản, ta chỉ việc sử dụng lệnh Inner Join hai bảng.

SELECT     customerName,     checkNumber,     paymentDate,     amount FROM     customers INNER JOIN     payments USING (customerNumber);

Giả sử kết quả như sau:

Câu truy vấn đã trả về kết quả thành công. Nhưng vào một ngày kia ta cần lấy lại thông tin này thì bạn sẽ phải viết lại câu truy vấn này một lần nữa. Câu hỏi đặt ra là có giải pháp nào tối ưu cho những lần sau không?

Có một cách truyền thống đó là bạn lưu dữ liệu vào file word hoặc txt, thậm chí là file sql. Tuy nhiên như vậy thì kích thước file quá lớn, và ta rất khó để sàn lọc dữ liệu.

Cách tốt hơn đó là sử dụng View trong MySQL, đây là một table ảo nhưng có đầy đủ tính năng của một table thật. Hãy xem cú pháp tạo View ho trường hợp trên như sau:

CREATE VIEW customerPayments AS SELECT     customerName,     checkNumber,     paymentDate,     amount FROM     customers INNER JOIN     payments USING (customerNumber);

Sau khi chạy câu truy vấn này thì trên hệ thống database xuất hiện thêm một View có tên là customerPayments. Đây là một table ảo nên bạn co thể viết câu truy vấn trên này một cách bình thường.

Vì vậy ở những lần sau nếu bạn muốn lấy dữ liệu thì chỉ việc chạy câu SQL này.

SELECT * FROM customerPayments;

Thật tuyệt vời phải không các bạn! Những lần sau này truy vấn sẽ rất đơn giản.

Vì một View sẽ được lưu trữ trên ổ đĩa vật lý nên thực chất nó cũng là một table, vì vậy bạn có thể thực hiện truy vấn với câu Select. Tuy nhiên với lệnh Delete hoặc Update thì không thực hiện trên View được.

Áp dụng phương pháp này ta có thể tạo ra một View chứa dữ liệu từ nhiều bảng khác nhau. Hãy xem hình minh họa dưới đây:

Trong hình này ta tạo ra một view chứa liệu của cả 3 bảng bằng cách sử dụng lệnh Join để kết hợp đồng bộ dữ liệu giữa chúng.

Một ví dụ khác: Giả sử ta cần tạo ra một View chứa tất cả các ngày trong tuần thì viết như sau:

CREATE VIEW daysofweek (day) AS     SELECT 'Mon'     UNION     SELECT 'Tue'     UNION     SELECT 'Web'     UNION     SELECT 'Thu'     UNION     SELECT 'Fri'     UNION     SELECT 'Sat'     UNION     SELECT 'Sun';

Sau đó muốn lấy dữ liệu trong tuần thì chạy câu SQL sau:

SELECT * FROM daysofweek;

Kết quả:

 

+ Tại sao nên sử dụng View trong MySQL?

Nếu sử dụng View trong quản lý database ta sẽ có những ưu thế sau.

- Đơn giản hóa truy vấn phức tạp

View giúp đơn giản hóa những câu truy vấn phức tạp cho những lần sử dụng sau, bở khi dữ liệu ở bảng chính thay đổi thì trong View cũng sẽ được thay đổi theo nên vấn đề đồng bộ dữ liệu rất chính xác.

- Làm giảm độ phức tạp tính toán

Giả sử bạn phải viết cùng một công thức cho mọi truy vấn. Hoặc bạn có một truy vấn có logic phức tạp. Để làm cho logic này nhất quán trên các truy vấn thì có thể sử dụng View để lưu trữ phép tính.

- Phân quyền và bảo mật

Với tính chất chỉ Xem chứ không được thay đổi dữ liệu nên khi muốn cho ai đó xem thì bạn có thể cho họ xem View thay vì xem Table.

Ngoài ra nó là một table nên bạn có thể giới hạn phân quyền cho table một cách dễ dàng.

Giả sử thông tin thanh toán của khách hàng thì chỉ có bộ phận kế toán thấy mà thôi, vì vậy ta có thể tạo ra một view chứa thông tin này và cấp quyền cho bộ phận kết toán.

C. Lệnh Create View trong MySQL

Lệnh Create View sẽ tạo ra một View mới trong Database, sau đây là cú pháp:

CREATE [OR REPLACE] VIEW [db_name.]view_name [(column_list)] AS   select-statement;

Ý nghĩa của các từ khóa như sau:

Từ khóa Create View cho thấy bạn đang muốn tạo một View mới có tên là view_name, năm trong database db_name.

Nếu bạn dùng thêm từ khóa Or Replace thay thì nó sẽ thay thế View có tên là view_name bằng nội dung SQL mới. Nếu view_name chưa tồn tại thì nó sẽ tạo view mới.

Column_list là danh sách các cột sẽ được đặt tên lại. Nếu bạn không truyền vào thì nó sẽ lấy tên cột mặc định của các table.

Select-statement chính là câu lệnh Select trả về danh sách dữ liệu sẽ được lưu vào trong View. Bạn có thể sử dụng kết hợp các lệnh Join, Order By, Limit, ... miễn là nó trả về kết quả là danh sách dữ liệu.

Theo mặc định thì lệnh Create View sẽ tạo mới một view nằm trong DB hiện tại đang kết nối. Nhưng nếu bạn muốn chỉ định rõ DB nào thì sẽ đặt nó ở vị trí của db_name.

Cách sử dụng lệnh Create View 

Hãy làm một vài ví dụ để hiểu rõ hơn lệnh này.

Ví dụ 1: Tạo một view đơn giản

Hãy xem cấu trúc của table orderdetails như trong hình dưới đây.

Bây giờ mình sẽ viết một View lưu trữ doanh thu của mỗi đơn hàng, bằng cách sử dụng lệnh Select kết hợp Group By.

CREATE VIEW salePerOrder AS     SELECT         orderNumber,         SUM(quantityOrdered * priceEach) total     FROM         orderDetails     GROUP by orderNumber     ORDER BY total DESC;

Để kiểm tra View đã xuất hiện chưa thì hãy sử dụng lệnh sau:

SHOW TABLES;

Lệnh này sẽ hiển thị danh sách tất cả các table đang có trong DB hiện tai. Vì View cũng là table nên cũng sẽ xuất hiện trong đó.

Ngoài ra, để xem chi tiết đâu là table và đâu là view thì bạn sử dụng lệnh sau:

SHOW FULL TABLES;

Kết quả lệnh này như sau:

Bây giờ nếu bạn muốn xem tổng doanh thu của mỗi đơn hàng thì chỉ cần truy vấn trong View SalePerOrder mà đã tạo ở trên.

SELECT * FROM salePerOrder;

Kết quả dạng như sau:

Ví dụ 2: Tạo mới một View từ một View khác

Nghe có vẻ kì lạ phải không? Thực tế bạn có thể tạo mới một view với dữ liệu được lấy từ một View khác.

Giả sử mình muốn lấy danh sách những đơn hàng có giá trị hơn 6000, lúc này chỉ cần lấy trong view salesPerOrder mà ta đã tạo ở trên là được.

CREATE VIEW bigSalesOrder AS     SELECT         orderNumber,         ROUND(total,2) as total     FROM         salePerOrder     WHERE         total > 60000;

Bây giờ dữ liệu trong view bigSalesOrder chính là những đơn hàng có doanh thu hơn 6000. Hãy thử chạy một câu truy vấn xem sao.

SELECT     orderNumber,     total FROM     bigSalesOrder;

Ví dụ 3: Tạo View với lệnh Join

Hãy xem ví dụ dưới đây, mình tạo ra một view có tên là customerOrders để lưu trữ thông tin khách hàng và tổng số tiền mà khách hàng đã mua. Mình đã JOIN 3 bảng gồm: orderDetails, orders và customers

CREATE OR REPLACE VIEW customerOrders AS SELECT     orderNumber,     customerName,     SUM(quantityOrdered * priceEach) total FROM     orderDetails INNER JOIN orders o USING (orderNumber) INNER JOIN customers USING (customerNumber) GROUP BY orderNumber;

Bây giờ sử dụng lệnh Select trong view customerOrders là được.

SELECT * FROM customerOrders ORDER BY total DESC;

Kết quả tham khảo:

Ví dụ 4: Tạo View với truy vấn con

Trong ví dụ dưới đây mình có sử dụng cấu trúc truy vấn con để tạo ra View.

CREATE VIEW aboveAvgProducts AS     SELECT         productCode,         productName,         buyPrice     FROM         products     WHERE         buyPrice > (             SELECT                 AVG(buyPrice)             FROM                 products)     ORDER BY buyPrice DESC;

D. Lệnh Drop View 

Lệnh Drop View sẽ xóa một view ra khỏi database, sau khi thực hiện bạn sẽ không thể phục hồi lại view đó được.

Cú pháp như sau:

DROP VIEW [IF EXISTS] view_name;

Trong đó:

• Từ khóa DROP VIEW cho biết là bạn đang muốn xóa một view nào đó.
• Từ khóa IF EXISTS có thể có hoặc không. Nếu có thì sẽ không bị lỗi khi view không tồn tai, ngược lại sẽ bị lỗi.

Để xóa nhiều view cùng một lúc thì ta sử dụng cú pháp sau:

DROP VIEW [IF EXISTS] view_name1 [,view_name2]...;

Trong lệnh này nếu bạn không sử dụng từ khóa IF EXISTS thì chỉ cần một view không tồn tại là kết quả lênh SQL không được thực hiện, đồng nghĩa với việc không có view nào được xóa.

* Lưu ý: Phiên bản MySQL 5.7 trở về trước thì lại khác, nó trả về lỗi các view không tồn tại nhưng vẫn xóa những view tồn tai.

 Cách dùng Drop View trong MySQL để xóa View

Hãy làm một vài ví dụ về xóa view để hiểu rõ hơn câu lệnh này.

Vi dụ 1: Xóa một view

Giả sử dùng lệnh sau để tạo view customerPayments.

CREATE VIEW customerPayments AS     SELECT         customerName,         SUM(amount) payment     FROM         customers     INNER JOIN payments         USING (customerNumber)     GROUP BY         customerName;

Sau đó mình dùng lệnh Drop View để xóa view vừa tạo đó.

DROP VIEW IF EXISTS customerPayments;

Ví dụ 2: Xóa nhiều view cùng một lệnh

Cho hai table có cấu trúc như sau:

Bây giờ tạo mới một view có tên là employeeOffices.

CREATE VIEW employeeOffices AS     SELECT         firstName, lastName, addressLine1, city     FROM         employees             INNER JOIN         offices USING (officeCode);

do chỉ mới tạo một view thôi, nên câu lệnh xóa 2 view dưới đây sẽ bị lỗi.

DROP VIEW employeeOffices, eOffices;

Lỗi thu được đó là:

Error Code: 1051. Unknown table 'classicmodels.eoffices'

Nhưng nếu ta thêm từ khóa IF EXISTS :

DROP VIEW IF EXISTS employeeOffices, eOffices;

Kết quả thu được:

1 warning(s): 1051 Unknown table 'classicmodels.eoffices'

Như vậy câu lệnh vẫn chạy thành công, chỉ là xuất hiện một cảnh báo lỗi.

Giả sử ta tạo thêm một view nữa dựa trên hai bảng sau:

CREATE VIEW productCatalogs AS     SELECT         productLine, productName, msrp     FROM         products             INNER JOIN         productLines USING (productLine);

Bây giờ chạy lênh Drop View để xóa hai views: productCatalogs và employeeOffices quá đơn giản.

DROP VIEW employeeOffices, productCatalogs;

Kết quả trả về thành công!

E. Rename Table đổi tên View

Bởi vì View và Table có chung cấu trúc và đặc tính nên bạn hoàn toàn có thể sử dụng lệnh RENAME TABLE để thay đổi tên cho View.

Dưới đây là cú pháp đơn giản để đổi tên View:

RENAME TABLE original_view_name TO new_view_name;

Ví dụ: Tạo view có tên là productLineSales

CREATE VIEW productLineSales AS SELECT     productLine,     SUM(quantityOrdered) totalQtyOrdered FROM     productLines         INNER JOIN     products USING (productLine)         INNER JOIN     orderdetails USING (productCode) GROUP BY productLine;

Bây giờ mình muốn đổi tên từ productLineSales sang productLineQtySales thì cách làm như sau:

RENAME TABLE productLineSales TO productLineQtySales;

Muốn kiểm tra thì hãy dùng lệnh SHOW FULL TABLES để xem tên đã đổi chưa nhé.

SHOW FULL TABLES WHERE table_type = 'VIEW';

Kết hợp Drop View và Create View để đổi tên View

Có một cách khác để đổi tên View trong MySQL đó là sử dụng lệnh Drop View để xóa view đó, sau đó sử dụng Create View để tạo lại View mới. Cách này nhìn chung hơi phiền hà, nhưng bạn có thể di chuyển view từ DB này sang DB khác.

Giả sử muốn đổi tên view từ productLineQtySales sang categorySales thì thực hiện các bước như sau:

Bước 1: Dùng lệnh SHOW CREATE VIEW để xem cấu trúc SQL của View đó.

SHOW CREATE VIEW productLineQtySales;

Ta thu được kết quả như sau:

Hãy copy lại nội dung SQL ở cột Create View đã khoanh tròn và lưu vào file txt nhé.

Bước 2: Dùng lệnh Drop View để xóa view.

DROP VIEW productLineQtySales;

Bước 3: Dùng lệnh Create View để tạo view mới với nội dung đã lưu trong file txt .

CREATE VIEW categorySales AS SELECT     productLine,     SUM(quantityOrdered) totalQtyOrdered FROM     productLines         INNER JOIN     products USING (productLine)         INNER JOIN     orderDetails USING (productCode) GROUP BY productLine;

2. Stored Procedure

A. Mysql Stored Procedure là gì? Ưu điểm và nhược điểm.

Chắc hẳn bạn đã biết được khái niệm về Hàm trong PHP rồi nhỉ? Trong Mysql Procedure cũng có ý nghĩa tương tự như vậy, nghĩa là chúng ta sẽ tạo những hàm (Procedure) để thực hiện những dòng lệnh liên quan trong đó, ví dụ như thao tác Update hay Insert.

Thông thường một hàm có thể gọi lại chính nó và ta gọi là đệ quy (tham khảo đệ quy trong php). Nhưng trong MYSQL thì bạn hãy kiểm tra Version của bạn có hỗ trợ không nhé. Nếu có thì mình cũng nghĩ là không nên sử dụng bởi vì bản chất MYSQL không sử dụng để tính toán nhiều.

Với MYSQL thì khái niệm Stored Procedure chỉ được thêm vào kể từ Version 5 với mục đích là giúp việc xử lý dữ liệu linh hoạt và mạnh mẽ hơn.

+ Ưu điểm của Stored Proccedure

Thông thường chúng ta sử dụng Procedure để tăng hiệu xuất xử lý của ứng dụng, sau khi được tạo ra thì tất cả các thủ tục được lưu trong hệ quản trị cơ sở dữ liệu. Tuy nhiên trong MYSQL việc lưu trữ các hàm này lại hơi khác nhau, khi được tạo ra thì thủ tục này sẽ được lưu trữ trong một bộ nhớ đệm (cache). Nếu trong một ứng dụng sử dụng gọi tới một Procedure nhiều lần trong một chuỗi kết nối thì lúc này sẽ coi nó như một chương trình cần biên dịch, nếu không thì sẽ coi như như là một câu truy vấn bình thường.

Stored Procedure giúp giảm thời gian giao tiếp giữa các ứng dụng với hệ quản trị MYSQL, bởi vì thay vì gửi nhiều câu lệnh dài thì ta chỉ cần gọi tới một thủ tục và trong thủ tục này sẽ thực hiện nhiều câu lệnh SQL.

Stored Procudure sẽ giúp các ứng dụng nhìn minh bạch hơn, nghĩa là khi ta định nghĩa các thao tác xử lý vào một Stored thì công việc của các ngôn ngữ lập trình khác chỉ quan tâm đến tên thủ tục, các tham số truyenf vào chứ không cần biết nó thực hiện như thế nào. Điều này giúp các team làm việc tốt hơn, ta sẽ phân ra bộ phận Coder riêng và bộ phận viết thủ tục riêng.

Mỗi thủ tục sẽ có các mức độ truy cập, nghĩa là ta có thể cấp quyền sử dụng cho một Uesr nào đó trong hệ quản trị (Lưu ý là user trong hệ quản trị chứ không phải là admin của ứng dụng website).

+ Nhược điểm của Stored Procedure

Nếu bạn tạo ra quá nhiều Procedure thì hệ quản trị sẽ sử dụng bộ nhớ để lưu trữ các thủ tục này khá nhiều. Ngoài ra nếu bạn thực hiện quá nhiều xử lý trong mỗi thủ tục thì đồng nghĩa với việc CPU sẽ làm việc nặng hơn, điều này không tốt chút nào.

Nếu sử dụng thủ tục thì sẽ rất khó phát triển trong ứng dụng, gây khó khăn ở mức logic business.

Một số hệ quản trị CSDL có những tool hỗ trợ Debug Store nhưng MYSQL thì không có.

Để phát triển ứng dụng thì bạn phải đòi hỏi có một kỹ năng thật siêu đăng mà không phải nhà thiết kế cơ sở dữ liệu nào cũng có. Điều này dễ bị khó cho vấn đề bảo trì và nâng cấp.

B. Tạo bảng Products để viết Procedure

Bạn tạo một Database mới với tên bất kì, sau đó dán đoạn code sau vào trình chạy câu truy vấn để tạo bảng products.

CREATE TABLE IF NOT EXISTS `products` (   `id` INT(11) NOT NULL AUTO_INCREMENT,   `title` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL,   `content` TEXT COLLATE utf8_unicode_ci,   PRIMARY KEY (`id`) ) ENGINE=INNODB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;   -- -- Contenu de la table `products` -- INSERT INTO `products` (`id`, `title`, `content`) VALUES (1, 'Học lập trình online tại freetuts.net', 'Gioi thieu website Học lập trình online tại freetuts.net'), (2, 'Tutorials học Stored Procedure', 'Website Tutorials học Stored Procedure');

Câu lệnh này sẽ tạo mới một bảng và thêm vào hai record để ta test. Bây giờ chúng ta sẽ bắt đầu tìm hiểu cú pháp khai báo và sử dụng Stored Procedure trong MYSQL.

+ Tạo Mysql Stored Procedure đầu tiên

Trong tài liệu này sử dụng IDE SqlYog để quản lý CSDL , nên nếu muốn học thuận tiện thì bạn nên cài đặt và sử dụng phần mềm này luôn.

- Tạo Stored Procedure

VD: viết một Procedure với tên là GetAllProducts(), nhiệm vụ của thủ tục này là truy vấn lấy danh sách tất cả sản phẩm nằm trong bảng Products. Nhưng trước tiên chúng ta tìm hiểu cú pháp khai báo tạo mới một Procedure như sau:

DELIMITER $$ CREATE PROCEDURE procedureName() BEGIN    /*Xu ly*/ END; $$ DELIMITER ;

Trong đó:

• Dòng đầu tiên DELIMITER $$ dùng để phân cách bộ nhớ lưu trữ thủ tục Cache và mở ra một ô lưu trữ mới. Đây là cú pháp nên bắt buộc bạn phải nhập như vậy
• Dòng CREATE PROCEDURE procedureName() dùng để khai báo tạo một Procedure mới, trong đó procedureName chính là tên thủ tục còn hai từ đầu là từ khóa.
• BEGIN và END; $$ dùng để khai báo bắt đầu của Procedure và kết thúc Procedure
• Cuối cùng là đóng lại ô lưu trữ DELIMITER ;

Lưu ý là  phải tuân theo cú pháp như trên nhé, ban chỉ việc thay đổi tên của thủ tục là ok.

Bây giờ để tạo mới một Procedure với tên là GetAllProduct thì chúng ta sẽ làm như sau:

DELIMITER $$ CREATE PROCEDURE GetAllProducts() BEGIN    /*Xu ly*/ END; $$ DELIMITER ;

Sau đó bạn chạy câu SQL này và nó báo thành công tức là bạn đã tạo mới một thủ tục với tên là GetAllProduct rồi đấy. Nếu như bạn sử dụng SqlYog thì bạn vào table Products và vào mục Stored Procs sẽ thấy một thủ tục vừa được tạo:

 

- Gọi Stored Procedure

Tạo xong rồi bây giờ làm thế nào để gọi đến Store này? Đơn giản để gọi tới Store nào thì ta chỉ cần dùng cú pháp như sau:

CALL storeName();

Như vậy để gọi tới Procedure tên là GetAllProducts thì ta làm như sau:

CALL GetAllProducts();

Chạy câu truy vấn này và bạn sẽ thấy kết quả như sau, đây chính là hai records mà ta đã thêm ở phần 1:

Xem danh sách Stored Procedure trong hệ thống

Nếu bạn không sử dụng SqlYog thì rất khó để quản lý Procedure vì không nhìn thấy được nó. Yên tâm vì trong MYSQL có hỗ trợ một số lệnh hiển thị dánh sách Stored Procedure bằng cách chạy lệnh sau:

show procedure status;

Sau khi chạy lên bạn sẽ nhận được kết quả dạng như sau:

Đây chính là Procedure mà ta đã tạo ở trên.

- Sửa Stored Procedure đã tạo

Trong Mysql không cung cấp lệnh sửa Stored nên thông thường chúng ta sẽ chạy lệnh tạo mới. Tuy nhiên có một lưu ý đó là nếu như bạn đã chạy lệnh tạo Procedure một lần rồi, sau đó bạn edit và chạy lại thì ngay lập tức sẽ bị báo lỗi ngay vì trùng tên. Để giải quyết vấn đề này thì chúng ta sẽ dùng lệnh Drop để xóa đi Procedure đó và tạo lại:

DELIMITER $$ DROP PROCEDURE IF EXISTS `GetAllProducts`$$ CREATE PROCEDURE `GetAllProducts`() BEGIN    SELECT *  FROM products; END$$ DELIMITER ;

Và một lưu ý nữa là khi bạn dùng với quyền User nào thì Store đó sẽ có quyền thực hiện trong phạm vu của User đó. Ví dụ bạn không có quyền edit mà bạn tạo mới một Procedure Edit thì khi chạy sẽ bị báo lỗi ngay. Chính vì vậy thông thường khi edit bạn phải thêm người định nghĩa nó như sau:

DELIMITER $$ DROP PROCEDURE IF EXISTS `GetAllProducts`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `GetAllProducts`() BEGIN    SELECT *  FROM products; END$$ DELIMITER ;

Dòng chữ DEFINER=`root`@`localhost` chính là tên người đã tạo ra nó.

C. Khai báo biến trong MySql Stored Procedure

Để định nghĩa một biến mới ta dùng cú pháp :

DECLARE variable_name datatype(size) DEFAULT default_value

Trong đó:

• DECLARE: là từ khóa tạo biến
• variable_name là tên biến
• datatype(size) là kiểu dữ liệu của biến và kích thước của nó
• DEFAULT default_value: là gán giá trị mặc định cho biến

Ví dụ:

DECLARE product_title VARCHAR(255) DEFAULT 'No Name';

+ Gán giá trị cho biến 

Tạo biến rồi thì phải gán giá trị cho nó chứ đúng không nào? Để gán giá trị cho nó thì chúng ta sử dụng tư khóa SET:

SET variable_name = 'value';

Ví dụ: Định nghĩa biến age và gán giá trị 20 cho nó.

DECLARE age INT(11) DEFAULT 0   SET age = 12

Ví dụ: Gán giá trị thông qua lệnh SELECT

DECLARE total_products INT DEFAULT 0 SELECT COUNT(*) INTO total_products FROM products

Trong ví dụ này thì trước tiên nó sẽ thực hiện câu truy vấn SQL đếm tổng số record và sau đó gán vào biến total_products bằng lệnh (COUNT(*) INTO total_products).

+ Phạm vi hoạt động của biến

Nếu như bạn định nghĩa một biến bên trong phần thân của Procedure (giữa BEGIN và END) thì đó ta gọi là biến cục bộ của Procedure. Bạn có thể định nghĩa nhiều biến trong một Procedure.

Ví dụ:

DELIMITER $$ DROP PROCEDURE IF EXISTS tinhTong $$ CREATE PROCEDURE tinhTong() BEGIN     DECLARE a INT (11) DEFAULT 0;     DECLARE b INT (11) DEFAULT 0;     DECLARE tong INT (11) DEFAULT 0;      SET a = 200;     SET b = 300;     SET tong = a + b;         SELECT tong;      END; $$ DELIMITER;

Ở procedure này tôi đã định nghĩa các biến a,b,tong và tính toán trên đó. Bạn chạy câu sql trên và sau đó gọi nó bằng lệnh call tinhTong thì nó sẽ cho kết quả là 500.

Nếu một biến được khai báo bên ngoài Procedure thì bên trong Procedure sẽ không nhận được và sẽ thông báo lỗi.

Ví dụ:

DELIMITER $$ DECLARE tong INT (11) DEFAULT 0; DROP PROCEDURE IF EXISTS tinhTong $$ CREATE PROCEDURE tinhTong() BEGIN     DECLARE a INT (11) DEFAULT 0;     DECLARE b INT (11) DEFAULT 0;             SET a = 200;     SET b = 300;     SET tong = a + b;         SELECT tong;      END; $$ DELIMITER;

Chương trình này lỗi vì biến tong không tồn tại trong Procedure.

D. Truyền tham số vào biến

+ Tạo Cơ sở dữ liệu Products

Bạn tạo mới một Database và chạy lệnh SQL tạo bảng sau lệnh này sẽ tạo một bảng products và thêm hai record:

CREATE TABLE IF NOT EXISTS `products` (   `id` INT(11) NOT NULL AUTO_INCREMENT,   `title` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL,   `content` TEXT COLLATE utf8_unicode_ci,   PRIMARY KEY (`id`) ) ENGINE=INNODB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;   -- -- Contenu de la table `products` --  INSERT INTO `products` (`id`, `title`, `content`) VALUES (1, 'Học lập trình online tại titoe.net', 'Gioi thieu website Học lập trình online tại titoe.net'), (2, 'Tutorials học Stored Procedure', 'Website Tutorials học Stored Procedure');

+ Các loại tham số trong Mysql Stored Procedure

Chúng ta có hai loại tham số chính trong Procedure đó là:

• IN: Đây là chế độ mặc định (nghĩa là nếu bạn không định nghĩa loại nào thì nó sẽ hiểu là IN). Khi bạn sử dụng mức này thì nó sẽ được bảo vệ an toàn, có nghĩa là sẽ không bị thay đổi nếu như trong Procedure có tác động đến
• OUT: Chế độ này nếu như trong Procedure có tác động thay đổi thì nó sẽ thay đổi theo. Nhưng có điều đặc biệt là dù trước khi truyền vào mà bạn gán giá trị cho biến đó thì vẫn sẽ không nhận được vì mặc định nó luôn hiểu giá trị truyền vào là NULL.
• INOUT: Đây là sự kết hợp giữa IN và OUT. Nghĩa là có thể gán giá trị trước và có thể bị thay đổi nếu trong Procedure có tác động tới

Ví dụ:

DELIMITER $$ CREATE PROCEDURE getById(IN id INT(11)) BEGIN     /*Code*/ END; $$ DELIMITER;

Nếu muốn truyền vào nhiều hơn một tham số thì ta sẽ ngăn cách nó bởi dấu phẩy. Ví dụ:

DELIMITER $$ DROP PROCEDURE IF EXISTS getById $$ CREATE PROCEDURE getById(IN id INT(11), IN title VARCHAR(255)) BEGIN     /*Code*/ END; $$ DELIMITER;

Thông thường chúng ta viết các tham số xuống hàng để nhìn đẹp hơn. Ví dụ:

DELIMITER $$ DROP PROCEDURE IF EXISTS getById $$ CREATE PROCEDURE getById(     IN id INT(11),     IN title VARCHAR(255) ) BEGIN     /*Code*/ END; $$ DELIMITER;

- Tham số loại IN trong Mysql Stored Procedure

Như trình bày ở trên tham số này sẽ được bảo vệ và không bị thay đổi trong quá trình sử dụng trong Procedure.

Ví dụ: Viết Store lấy chi tiết sản phẩm theo ID

DELIMITER $$ DROP PROCEDURE IF EXISTS getById $$ CREATE PROCEDURE getById(IN idVal INT(11)) BEGIN     SELECT * FROM products WHERE id = idVal; END; $$ DELIMITER;

Chạy Procedure này:

CALL getById(1);

Và ta có giao diện kết quả trả về:

- Tham số loại OUT trong Mysql Stored Procedure

Loại out nếu trong quá trình thực thi mà Procedure có tác động đến tham số này thì bên ngoài nó ảnh hưởng theo. Khi nhận tham số này thì Procedure sẽ hiểu đó là giá trị NULL nên dù bạn có gán giá trị cho biến trước khi truyền vào nó vẫn lấy NULL.

• Biến truyền vào phải có chữ @ đằng trước, ví dụ @title

Ví dụ: Truyền tham số title kiểu OUT vào Procedure và đổi giá trị cho nó, sau đó bên ngoài Procedure hiển thị giá trị của title.

DELIMITER $$ DROP PROCEDURE IF EXISTS changeTitle $$ CREATE PROCEDURE changeTitle(OUT title VARCHAR(255)) BEGIN     SET title = 'Hoc lap trinh online tai freetuts.net'; END; $$ DELIMITER;

Bây giờ ta gọi Procedure này như sau:

CALL changeTitle(@title); SELECT @title;

Thì kết quả sẽ như sau:

 Như vậy ra rút ra kết luận như sau:

• Khi truyền tham số dạng OUT mục đích là lấy dữ liệu trong Proedure và sử dụng ở bên ngoài.
• Khi truyền tham số vào dạng OUT phải có chữ @ đằng trước biến
• Hoạt động giống tham chiếu nên biến truyền vào dạng OUT không cần định nghĩa trước, chính vì vậy khởi đầu nó có giá trị NULL

- Tham số dạng INOUT trong Mysql Stored Procedure

INOUT là sự kết hợp giữa IN và OUT, nghĩa là:

• Nó có thể được định nghĩa trước và gán gia trị trước rồi truyền vào Procedure, điều này với dạng OUT thì không thể được nhưng IN thì được.
• Sau khi thực thi xong nếu trong Procedure có tác động đến thì ảnh hưởng theo. Điêu này dạng IN không được nhưng OUT thì không được.

Ví dụ: Tạo Procedure

DELIMITER $$ DROP PROCEDURE IF EXISTS counter $$ CREATE PROCEDURE counter(INOUT number INT(11)) BEGIN     SET number = number + 1; END; $$ DELIMITER;

Gọi sử dụng:

SET @counter = 1; CALL counter(@counter); SELECT @counter;

Và kết quả là 2.

Nhưng nếu ta dùng dạng OUT thì kết quả sẽ là NULL. Lý do là bên trong có tăng lên 1 nhưng nó lấy giá trị truyền vào dạng OUT là NULL nên 1 + NULL sẽ là NULL.

E. Mệnh đề if else trong MySql Stored Procedure

+ Tìm hiểu mệnh đề if else trong MySql

Mệnh đề if cho phép bạn tạo luồng xử lý rẻ nhánh, nếu đúng thì thực thì và ngược lại mệnh đề sai thì nó sẽ không thực thi. Thông thường chúng ta kết hợp các toán tử, toán hạng và biến trong mysql để tạo ra các mệnh đề đúng sai trong điều kiện của lệnh IF. Không những chỉ có IF mà ta có thể sử dụng mệnh đề IF ELSE trong MYSQL cũng được.

Cú pháp:

IF if_expression THEN     commands    ELSEIF elseif_expression THEN     commands    ELSE     commands END IF;

Luồng đi như sau:

• Nếu if_expression đúng thì nó sẽ thực thi câu lệnh bên dưới nó, ngược lại nó bỏ qua và nhảy xuống IFELSE
• Nó kiểm tra mệnh đề IFELSE, nếu mệnh đề này đúng thì nó xử lệnh bên dưới, ngươc lại thì nó bỏ qua và nhảy tiếp xuống dưới.
• Ở dưới nó nhận thấy chỉ còn có ELSE nên thực thi luôn chứ không cần kiểm tra điều kiện nữa.

Lưu ý với bạn là ta có thể có nhiều IFELSE chứ không phải chỉ 1 cái như trong ví dụ trên.

+ Ví dụ mềnh đề if else trong MySql Stored Procedure

Bây giờ ta sẽ làm một ví dụ cho bạn dễ hiểu hơn. Trước tiên ta cần tạo một bảng thành viên và insert một số thông tin Username và Password. Bạn chạy lệnh sau để tạo bảng:

CREATE TABLE IF NOT EXISTS `members` (   `us_id` INT(11) NOT NULL AUTO_INCREMENT,   `us_username` VARCHAR(30) COLLATE utf8_unicode_ci DEFAULT NULL,   `us_password` VARCHAR(32) COLLATE utf8_unicode_ci DEFAULT NULL,   `us_level` TINYINT(1) DEFAULT '0',   PRIMARY KEY (`us_id`) ) ENGINE=INNODB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ; -- -- Contenu de la table `members` -- INSERT INTO `members` (`us_id`, `us_username`, `us_password`, `us_level`) VALUES (1, 'admin', '57e34a1be668ebd6e40d430806beb099', 1), (2, 'member', '57e34a1be668ebd6e40d430806beb099', 2), (3, 'banded', '57e34a1be668ebd6e40d430806beb099', 0);

Trong bảng này ta cần chú ý đến field us_level như sau:

• Nếu us_level = 0 => tài khoản bị khóa
• Nếu us_level = 1 => admin
• Nếu us_level = 2 => member

Bây giờ ta viết Procedure đăng nhập với yêu cầu như sau:

• Nếu us_level = 0 => tài khoản bị khóa
• Nếu us_level = 1 => là admin
• Nếu us_level = 2 => là member
• Nếu không tồn tại => đăng nhập sai

Ý tưởng:

• Tạo Procedure với tham số truyền vào là gồm username và password thuộc loại IN, còn result thuộc loại OUT để lấy sử dụng. 
• Ta sẽ tạo một biến flag để lưu trữ us_level của người dùng, giá trị khởi tạo của nó là -1. Sau khi thực hiện lệnh SELECT nếu giá trị flag = -1 tức là không tồn tại username và password trong CSDL, ngược lại thì ta sẽ check flag để trả về kết quả tương ứng.

Bài giải:

DELIMITER $$ DROP PROCEDURE IF EXISTS `checkLogin`$$ CREATE PROCEDURE `checkLogin`(     IN input_username VARCHAR(255),     IN input_password VARCHAR(255),     OUT result VARCHAR(255) ) BEGIN     /*Bien flag luu tru level. Mac dinh la -1*/     DECLARE flag INT(11) DEFAULT -1;         /*Thuc hien truy van gan level vao bien flag*/     SELECT us_level INTO flag FROM members     WHERE us_username = input_username AND us_password = MD5(input_password);     /*Sau khi thuc hien lenh select nay ma ko co du lieu thi       luc nay flag se khong thay doi. Chinh vi the neu flag = -1 tuc la sai thong tin     */     IF (flag <= 0) THEN             SET result = 'Thong tin dang nhap sai';         ELSEIF (flag = 0) THEN             SET result = 'Tai khoan bi khoa';         ELSEIF (flag = 1) THEN             SET result = 'Tai khoan admin';         ELSE             SET result = 'Tai khoan member';     END IF; END$$ DELIMITER ;

Sử dụng:

CALL checkLogin('admin', 'vancuong', @result); SELECT @result; -- hoặc CALL checkLogin('member', 'vancuong', @result); SELECT @result; -- hoặc CALL checkLogin('banded', 'vancuong', @result); SELECT @result;

F. Lệnh Case trong Mysql

+ Cú pháp:

CASE  case_expression    WHEN when_expression_1 THEN commands    WHEN when_expression_2 THEN commands    ...    ELSE commands END CASE;

Trong đó:

• WHEN dịch tiếng anh nghĩa là KHI, tức là KHI MỆNH ĐỀ ĐIỀU KIỆN ĐÚNG thì sẽ thực thi câu lệnh bên trong nó.
• ELSE chính là trường hợp cuối cùng nếu như các điều kiện WHEN ở trên không đúng thì nó sẽ được chạy. Nếu để ý kĩ thì bạn thấy đây chính là lệnh default trong Switch Case của các ngôn ngữ lập trình khác.

+ Ví dụ lệnh CASE trong Stored Procedure

Lệnh CASE được ứng dụng rất nhiều nơi nhưng điển hình vẫn là trong Procedure. Tuy nhiên nó vẫn không bằng lệnh IF ELSE bởi tính linh hoạt của nó không cao. Để các bạn rõ hơn thì ta sẽ làm một ví dụ như sau:

Ví dụ: Viết Stored Procedure nhập vào một số từ 0 tới 9 và đọc số đó thành chữ. Yêu cầu sử dụng lệnh CASE trong MYSQL

DELIMITER $$ DROP PROCEDURE IF EXISTS `docSo`$$ CREATE PROCEDURE `docSo`(IN a INT(11)) BEGIN     DECLARE message VARCHAR(255);         CASE a         WHEN 0 THEN             SET message = 'KHONG';         WHEN 1 THEN             SET message = 'MOT';         WHEN 2 THEN             SET message = 'HAI';         WHEN 3 THEN             SET message = 'BA';         WHEN 4 THEN             SET message = 'BON';         WHEN 5 THEN             SET message = 'NAM';         WHEN 6 THEN             SET message = 'SAU';         WHEN 7 THEN             SET message = 'BAY';         WHEN 8 THEN             SET message = 'TAM';         WHEN 9 THEN             SET message = 'CHIN';         ELSE             SET message = 'KHONG TIM THAY';                      END CASE;          SELECT message;      END$$ DELIMITER ;

Chạy thủ tục:

CALL docSo(1); -- MOT CALL docSo(2); -- HAI CALL docSo(3); -- BA CALL docSo(4); -- BON CALL docSo(5); -- NAM CALL docSo(6); -- SAU CALL docSo(7); -- BAY CALL docSo(8); -- TAM CALL docSo(9); -- CHIN CALL docSo('tum lum'); -- KHONG TIM THAY

+  Lệnh CASE trong câu SELECT

Ngoài cách sử dụng ở phần 2 thì lệnh CASE còn thường hay sử dụng trong câu lệnh SELECT. Ví dụ khi bạn truy vấn lấy danh sách khách hàng và bạn muốn nếu field gender = 1 thì kết quả là nam và ngược lại kết quả là nữ. Để làm được thì ta phải sử dụng lệnh CASE trong MYSQL. Để dễ hiểu hơn bây giờ ta làm một bài tập nhỏ như sau:

Giả sử ta có bảng member như sau:

CREATE TABLE IF NOT EXISTS `members` (   `us_id` INT(11) NOT NULL AUTO_INCREMENT,   `us_gender` TINYINT(1) DEFAULT '0',   `us_username` VARCHAR(30) COLLATE utf8_unicode_ci DEFAULT NULL,   `us_password` VARCHAR(32) COLLATE utf8_unicode_ci DEFAULT NULL,   `us_level` TINYINT(1) DEFAULT '0',   PRIMARY KEY (`us_id`) ) ENGINE=INNODB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ; -- -- Contenu de la table `members` -- INSERT INTO `members` (`us_id`, `us_gender`, `us_username`, `us_password`, `us_level`) VALUES (1, 0, 'admin', '57e34a1be668ebd6e40d430806beb099', 1), (2, 1, 'member', '57e34a1be668ebd6e40d430806beb099', 2), (3, 0, 'banded', '57e34a1be668ebd6e40d430806beb099', 0);

Trong đó bạn cần chú ý đến field us_gender như sau:

• Nếu us_gender = 1 => Nam
• Các trường hợp khác => Nữ

Ok, câu lệnh chúng ta như sau:

SELECT     us_username,     CASE us_gender         WHEN 1 THEN 'Nam'         ELSE 'Nu'     END AS gender FROM members

Và kết quả như hình:

Như vậy lệnh CASE trong câu SELECT hơi khác chút xíu đó là chỉ có lệnh END thay vì END CASE như trong Procedure.Các bạ lưu ý điểm này để viết câu truy vấn cho đúng nhé.

G. Vòng lặp while trong Mysql

+ Cú pháp vòng lặp while trong MYSQL

Ta có cú pháp vòng lặp while như sau:

WHILE expression DO    Statements END WHILE;

Nếu biểu thức expression có giá trị là sai thì vòng lặp sẽ dừng, ngược lại thì vòng lặp sẽ được thực hiện. Chính vì vậy khi sử dụng bạn cẩn thận nếu như bị lặp vô hạn thì ưng dụng của bạn sẽ bị chết ngay lập tức.

+ Ví dụ vòng lặp While trong MYSQL

Ví dụ: Viết chương trình Stored Procedure in ra màn hình các số từ a tới b bằng vòng lặp While.

Trong ví dụ này mình có sử dụng hàm CONCAT, đây là một hàm nỗi chuỗi trong MYSQL và tham số truyền vào của nó là danh sách các chuỗi cần nối cách nhau bởi dấu phẩy. Hàm này không giới hạn tham số truyền vào. Nếu như bạn sử dụng cú pháp dấu + để nối chuỗi thì sai nhé, trong MYSQL không hỗ trợ phép toan này đối với chuỗi mà chỉ dành cho number.

DELIMITER $$ DROP PROCEDURE IF EXISTS loopWhile$$ CREATE PROCEDURE loopWhile(     IN a INT(11),     IN b INT(11) ) BEGIN     -- Chuoi in ra man hinh--         DECLARE str  VARCHAR(255) DEFAULT '';             WHILE (a <= b) DO         SET  str = CONCAT(str,a,',');                 SET  a = a + 1;         END WHILE;                  SELECT str; END$$ DELIMITER ;

Chạy thử:

CALL loopWhile(1,10);

Kết quả:

 

3. Index trong Mysql

A. Index trong Mysql là gì và cách tạo Index

+ MySQL Index là gì?

Index là dữ liệu có cấu trúc như B-Tree giúp cải thiện tốc độ tìm kiếm trên một bảng, làm giảm chi phí thực hiện truy vấn. Việc tối ưu hóa chỉ mục sẽ giúp xác định được vị trí của dữ liệu cần tìm thay vì phải dò theo thứ tự hàng triệu record trong bảng.

Khi bạn tạo một khóa chính thì đồng nghĩa với việc bạn đã tạo một chỉ mục duy nhất, các hàng không được trùng dữ liệu với nhau, tên gọi của nó là PRIMARY, thuộc thể loai clustered index.

Những chỉ mục khác khong phải là PRIMARY thì ta gọi là chỉ mục phụ secondary index hoặc non-clustered indexes.

+ Đặt vấn đề về Index trong MySQL

Giả sử bạn có một danh bạ điện thoại chứa tất cả tên và số điện thoại của người dân trong thành phố. Bây giờ bạn muốn tìm số điện thoại của Bob Cat. Vì các tên được sắp xếp theo thứ tự abc nên trước tiên bạn tìm trang có tên cuối cùng là Cat, sau đó bạn tìm Bob và số điện thoại của anh ấy.

Nếu tên trong danh bạ điện thoại không được sắp xếp theo thứ tự abc, bạn sẽ cần phải đi qua tất cả các trang, đọc mọi tên trên đó cho đến khi bạn tìm thấy Bob Cat. Điều này được gọi là [tìm kiếm tuần tự]. Bạn đi qua tất cả các hàng cho đến khi bạn tìm thấy người có số điện thoại mà bạn đang tìm kiếm.

Liên hệ danh bạ điện thoại với bảng cơ sở dữ liệu, nếu bạn có bảng phonebooks và bạn phải tìm số điện thoại của Bob Cat, bạn sẽ thực hiện truy vấn sau:

SELECT     phone_number FROM     phonebooks WHERE     first_name = 'Bob' AND     last_name = 'Cat';

Truy vấn thực hiện khá nhanh trong trường hợp table có một vài ngàn dữ liệu. Nhưng nếu bảng có hàng triệu record và không được đánh dấu chỉ mục thì truy vấn này sẽ mất khá nhiều thời gian xử lý.

+ Lệnh CREATE INDEX trong MySQL

Thông thường ta sẽ tạo chỉ mục ngay lúc tạo bảng bằng lệnh Create Table. Ví dụ dưới đây mình đã tạo một bảng và thiết lập chỉ mục cho nó luôn.

CREATE TABLE t(    c1 INT PRIMARY KEY,    c2 INT NOT NULL,    c3 INT NOT NULL,    c4 VARCHAR(10),    INDEX (c2,c3) );

Ngoài ra, để thêm một chỉ mục vào bảng có sẵn thì ta sử dụng lệnh CREATE INDEX, cú pháp như sau:

CREATE INDEX index_name ON table_name (column_list)

Để tạo chỉ mục cho một cột hoặc nhiều cột thì tốt nhất ta đặt cho nó một cái tên, phía sau là danh sách các cột.

CREATE INDEX idx_c4 ON t(c4);

Theo mặc định, MySQL tạo chỉ mục B-Tree nếu bạn không chỉ định loại chỉ mục. Sau đây cho thấy loại chỉ mục cho phép dựa trên kiểu lưu trữ của bảng:

Storage Engine	Allowed Index Types
InnoDB	BTREE
MyISAM	BTREE
MEMORY/HEAP	HASH, BTREE

Lưu ý rằng câu lệnh CREATE INDEX ở trên là phiên bản đơn giản hóa của câu lệnh CREATE INDEX được giới thiệu bởi MySQL. Chúng ta sẽ học cách sử dụng nó nhiều hơn ở các ví dụ tiếp theo.

+ Dùng CREATE INDEX tạo Index trong MySQL

Câu lệnh SQL sau đây sẽ tìm các nhân viên có chức danh là Sales Rep:

SELECT     employeeNumber,     lastName,     firstName FROM     employees WHERE     jobTitle = 'Sales Rep';

Kết quả như sau:

Kết quả trả về 10 nhân viên đang làm việc với chức danh là Sales Rep. Và để xem chi tiết câu truy vấn này thì ta thêm lệnh EXPLAIN ngay đầu lệnh Select thì sẽ thu được kết quả như sau:

Như bạn thấy, câu truy vấn đã duyệt qua tổng cộng là 23 rows để trả về 10 kết quả như trên.

Bây giờ mình sẽ tạo một chỉ mục cho cột jobTitle như sau:

CREATE INDEX jobTitle ON employees(jobTitle);

Và chạy lại câu truy vấn thêm một lần nữa:

EXPLAIN SELECT     employeeNumber,     lastName,     firstName FROM     employees WHERE     jobTitle = 'Sales Rep';

Kêt quả thu được như sau:

Thật tuyệt vời, MySQL chỉ duyệt qua 17 rows thay vì 23 rows như ở trên. Điều này chính là nhờ vào kỹ thuật tạo index mà mình đã làm.

Để xem tất cả những index thuộc một table nào đó thì ta sử dụng lệnh SHOW INDEXES.

SHOW INDEXES FROM employees;

Kết quả như hình dưới đây là một ví dụ khá chi tiết, nó hiển thị thông tin và kiểu index rõ ràng.

B. Lệnh Xóa Index

+ Cách sử dụng DROP INDEX trong MySQL

Để xóa một Index đã có sẵn trong table thì ta sử dụng câu lệnh DROP INDEX, cú pháp như sau:

DROP INDEX index_name ON table_name [algorithm_option | lock_option];

Trong đó:

• DROP INDEX là câu lệnh khai báo bắt đầu yêu cầu xóa index
• index_name là tên index mà bạn muốn xóa
• table_name là table bạn muốn xóa
• algorithm_option và lock_option là hai thông số tùy chọn, mình sẽ nói về nó ở phần tiếp theo.

Algorithm

Thông số algorithm_option là thuật toán àm bạn muốn sử dụng để xóa chỉ mục index, cú pháp của nó như sau:

ALGORITHM [=] {DEFAULT|INPLACE|COPY}

Để xóa chỉ mục, các thuật toán sau được hỗ trợ:

• COPY: Table sẽ được copy sang một bảng mới, sau đó thực hiện xóa index trên table gốc, tất cả các câu lệnh khác như INSERT / UPDATE trên table này sẽ bị khóa không được thực hiện tại thời điểm đó.
• INPLACE: Table được xây dựng lại tai chỗ thay vì sao chép sang bảng mới. Thuật toán này cho phép các câu lệnh khác thực hiện trong quá trình xóa.

Lưu ý rằng câu lệnh ALGORITHM là tùy chọn, nếu bạn không thiết lập thì nó sẽ sử dụng thuật toán INPLACE. Trong trường hợp INPLACE không hỗ trợ thì nó sẽ sử dụng COPY.

Nếu bạn sử dụng DEFAULT đồng nghĩa bạn bỏ qua mệnh đề ALGORITHM này.

Lock

Thông số Lock_option kiểm soát mức độ đọc và ghi đồng thời trên bảng trong khi chỉ mục đang bị xóa.

Sau đây cho thấy cú pháp của lock_option:

LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

Các chế độ lock đuọc hỗ trợ như sau:

• DEFAULT: Cho phép bạn có mức độ đồng thời tối đa cho một thuật toán nhất định. Đầu tiên nó cho phép đọc và ghi đồng thời nếu được hỗ trợ. Nếu không nó cho phép đọc đồng thời nếu được hỗ trợ. Nếu không thì thực thi truy cập độc quyền.
• NONE: nếu NONE được hỗ trợ, bạn có thể đọc và ghi đồng thời. Nếu không MySQL phát sinh lỗi.
• SHARED: Nếu SHARED được hỗ trợ thì bạn có thể đọc đồng thời nhưng không ghi. MySQL phát sinh lỗi nếu các lần đọc đồng thời không được hỗ trợ.
• EXCLUSIVE: điều này thực thi quyền truy cập độc quyền.

+Sử dụng lệnh DROP INDEX để xóa chỉ mục trong MySQL

Trước tiên hãy tạo một table leads có cấu trúc như sau:

CREATE TABLE leads(     lead_id INT AUTO_INCREMENT,     first_name VARCHAR(100) NOT NULL,     last_name VARCHAR(100) NOT NULL,     email VARCHAR(255) NOT NULL,     information_source VARCHAR(255),     INDEX name(first_name,last_name),     UNIQUE email(email),     PRIMARY KEY(lead_id) );

Câu lệnh dưới đây xóa chỉ mục name ra khỏi table leads.

DROP INDEX name ON leads;

Câu lệnh sau sẽ loại bỏ chỉ mục email từ leads với một thuật toán và khóa cụ thể:

DROP INDEX email ON leads ALGORITHM = INPLACE LOCK = DEFAULT;

+ Xóa khóa chính Primary Key ra khỏi table

Chỉ mục Primary Key là loại đặc biệt có tên gọi là PRIMARY, vì vậy bạn sử dụng nó để áp dụng trong lệnh Drop Index.

DROP INDEX `PRIMARY` ON table_name;

Ví dụ: Tạo mới một table như sau.

CREATE TABLE t(     pk INT PRIMARY KEY,     c VARCHAR(10) );

Và đây là câu lệnh xóa khóa chính:

DROP INDEX `PRIMARY` ON t;

C. Prefix Index trong MySQL

+ Giới thiệu Prefix Index trong MySQL

Khi bạn tạo một chỉ mục phụ (secondary index) cho một column thì MySQL sẽ lưu các giá trị của các column trong một cấu trúc dữ liệu riêng biệt, ví dụ: B-Tree và Hash.

Trong trường hợp các column có kiểu dữ liệu chuỗi thì chỉ mục sẽ tiêu tốn rất nhiều dung lượng đĩa và có khả năng làm chậm các hoạt động INSERT. Để giải quyết vấn đề này, MySQL cho phép bạn tạo chỉ mục chỉ cho phần đầu giá trị của column.

Ví dụ bạn lưu trữ 1 triệu bài viết và muốn đặt chỉ mục cho field post_title để giúp việc truy vấn nhanh hơn. Lúc này nếu sử dụng index bình thường thì sẽ không tốt cho việc lưu trữ, vì vậy ta cần tìm giải pháp là chỉ index bao nhiêu ký tự đầu tiên thôi, miễn là chiều dài đủ để các tiêu đề là duy nhất.

Cú pháp như sau:

column_name(length)

Ví dụ: câu lệnh sau đây tạo prefix index ngay tại thời điểm tạo table.

CREATE TABLE table_name(     column_list,     INDEX(column_name(length)) );

Hoặc thêm từ một bảng đã tồn tại sẵn.

CREATE INDEX index_name ON table_name(column_name(length));

Trong cú pháp này thì length chính là độ dài của các loại chuỗi không phải nhị phân như CHAR, VARCHAR và TEXT, và là số byte của các kiểu dữ liệu như BINARY, VARBINARY và BLOB.

MySQL cho phép bạn tùy ý tạo column prefix cho các cột kiểu CHAR, VARCHAR, BINARY và VARBINARY. Nếu bạn tạo chỉ mục cho các cột BLOB và TEXT thì bạn phải chỉ định các phần chính của column prefix.

Lưu ý rằng độ dài của tiền tố phụ thuộc vào công cụ lưu trữ. Đối với các bảng InnoDB có định dạng hàng REDUNDANT hoặc COMPACT có độ dài tiền tố tối đa là 767 byte. Tuy nhiên, đối với các bảng InnoDB có định dạng hng DYNAMIC hoặc COMPRESSED sẽ độ dài tiền tố là 3.072 byte. Các bảng MyISAM có độ dài tiền tố lên tới 1.000 byte.

+ Các ví dụ tạo Prefix Index trong MySQL

Giả sử chúng ta có table products gồm các field như sau:

Truy vấn sau đây tìm các sản phẩm có tên bắt đầu bằng chuỗi 1970.

SELECT     productName,     buyPrice,     msrp FROM     products WHERE     productName LIKE '1970%';

Do không có chỉ mục cho cột ProductName nên truy vấn phải quét tất cả các hàng để trả về kết quả như trong hình dưới đây. Mình sử dụng lệnh EXPLAIN để xem chi tiết câu SELECT.

EXPLAIN SELECT     productName,     buyPrice,     msrp FROM     products WHERE     productName LIKE '1970%';

Theo như trong hình thì có tổng cộng 110 rows. Nếu bạn thường xuyên tìm kiếm trên cột này thì hãy tạo chỉ mục cho nó, điều này giúp tối ưu hóa thời gian tìm kiếm.

Giả sử kích thước của cột ProductName tối đa 70 ký tự, là kiểu chuỗi nên ta có thể áp dụng trong Prefix Index.

Câu hỏi tiếp theo là làm thế nào để bạn chọn độ dài của Prefix Index? Để làm điều này thì dựa vào dữ liệu hiện có, mục tiêu là tìm được con số chính xác giúp các record trong bảng là duy nhất. Hãy nhớ là ta có tổng công 110 record nhé.

Để làm điều này, bạn làm theo các bước sau:

Bước 1. Tìm số lượng hàng trong bảng:

SELECT    COUNT(*) FROM    products;

Theo như kết quả ở trên thì có 110 records.

Bước 2. Sử dụng câu lệnh dưới đây để tìm kiếm độ dài khúc đầu của chuỗi là bao nhiêu sẽ tạo ra được tính duy nhất cho các record. Bạn có thể thay con số 20 bằng con số bất kì để test.

SELECT    COUNT(DISTINCT LEFT(productName, 20)) unique_rows FROM    products;

Theo như hình thì 20 là độ dài tiền tố tốt trong trường hợp này bởi vì nếu chúng ta sử dụng 20 ký tự đầu tiên của tên sản phẩm cho chỉ mục thì tất cả các tên sản phẩm là duy nhất.

Hãy để tạo một chỉ mục với độ dài tiền tố 20 cho cột ProductName:

CREATE INDEX idx_productname ON products(productName(20));

Và thực hiện truy vấn tìm các sản phẩm có tên bắt đầu bằng chuỗi 1970 một lần nữa:

EXPLAIN SELECT     productName,     buyPrice,     msrp FROM     products WHERE     productName LIKE '1970%';

Bây giờ câu truy vấn chạy nhanh hơn và hiệu quả hơn nhiều so với trước đây.

D. Tạo Index cho nhiều cột

+ Khi nào tạo chỉ mục tổng hợp nhiều cột?

Chỉ mục tổng hợp là một chỉ mục trên nhiều cột. MySQL cho phép bạn tạo một chỉ mục tổng hợp bao gồm tối đa 16 cột. Việc tạo chỉ mục này giúp các câu truy vấn được xử lý tối ưu hơn.

Ví dụ bạn muốn truy vấn tìm danh sách sinh viên thuộc khoa CNTT và lớp CN01 thì có thể tạo chỉ mục cho 2 field (khoa_id, lop_id). Với cách làm này sẽ giúp tất cả những câu truy vấn có sử dụng cặp điều kiện này đều chạy nhanh hơn.

Để tạo một chỉ mục tổng hợp tại thời điểm tạo bảng thì ta sử dụng câu lệnh sau:

CREATE TABLE table_name (     c1 data_type PRIMARY KEY,     c2 data_type,     c3 data_type,     c4 data_type,     INDEX index_name (c2,c3,c4) );

Trong ví dụ này thì mình đã tạo chỉ mục có tên là index_name gồm ba cột (c2, c3 ,c4).

Hoặc bạn cũng có thể sử dụng lệnh Create Index để tạo chỉ mục.

CREATE INDEX index_name ON table_name(c2,c3,c4);

Lưu ý rằng nếu bạn tạo chỉ mục cho n cột thì những câu truy vấn có số cột ít hơn đều được tối ưu hóa, và phải tuân theo thứ tự từ trái sang phải. Như trong ví dụ trên thì những trường hợp sau:

(c1) (c1,c2) (c1,c2,c3)

Ví dụ:

SELECT     * FROM     table_name WHERE     c1 = v1;   SELECT     * FROM     table_name WHERE     c1 = v1 AND     c2 = v2;   SELECT      * FROM     table_name WHERE     c1 = v1 AND     c2 = v2 AND     c3 = v3;

Trình tối ưu hóa truy vấn bắt buộc phải theo thứ tự từ trái qua phải, nếu không thì index không có tác dụng gì cả. Như trong ví dụ trên thì nếu bạn thiết lập where:

• c1 = v1 and c3 = v3 => sai
• c1 = v1 and c2 = v2 => đúng
• c2 = v2 => sai

+ Một ví dụ đặt chỉ mục nhiều cột trong MySQL

Giả sử chúng ta có bảng employees có cấu trúc như sau:

Sau đó mình tạo index gồm hai cột firstName và lastName.

CREATE INDEX name ON employees(lastName, firstName);

Như vậy chúng ta chỉ có thể truy vấn cho hai trường hợp:

• Chỉ mỗi lastName
• Cả lastName và firstName

Trường hợp chỉ mỗi lastName

Hãy tìm kiếm nhân viên có tên là "Patterson".

SELECT     firstName,     lastName,     email FROM     employees WHERE     lastName = 'Patterson';

Câu truy vấn này có áp dụng index ở trên bởi vì lệnh where đầu tiên chính là lastName. Hãy đặt thêm lệnh EXPLAIN đằng trước câu Select để xem kết quả.

EXPLAIN SELECT     firstName,     lastName,     email FROM     employees WHERE     lastName = 'Patterson';

Kết quả:

Như vậy câu SQL trên đã duyệt qua 3 rows.

Trường hợp có cả lastName và firstName:

SELECT     firstName,     lastName,     email FROM     employees WHERE     lastName = 'Patterson' AND     firstName = 'Steve';

Tương tự trường hợp này vẫn được áp dụng index ở trên, bởi vì theo thứ tự index là lastName -> fistName.

Như trong hình là câu SQL đã duyệt qua 1 rows.

Trường hợp truy vấn mỗi firstName:

SELECT     firstName,     lastName,     email FROM     employees WHERE     firstName = 'Leslie';

Trường hợp này index trên không có tác dụng gì cả.

Như vậy là mình đã hướng dẫn xong cách tạo ra index trên nhiều column, cũng như nguyên tắc áp dung index vào nhiều trường hợp khác nhau.

E. Descending Index trong MySQL

+ Giới thiệu Descending Index

Descending Index còn gọi là chỉ mục giảm dần, lưu trữ các giá trị theo thứ tự giảm dần. Các phiên bản MySQL 8.0 trở về trước bạn có thể chỉ định DESC trong khai báo chỉ mục, tuy nhiên trong phiên bản MySQL này đã bỏ qua nó.

Thực tế MySQL cũng thể quét chỉ mục theo thứ tự giảm dần nhưng chi phí thực hiện quá cao, hay nói cách khác là không tối ưu.

Câu lệnh SQL dưới đây tạo ra một table và một index trong table đó.

CREATE TABLE t(     a INT NOT NULL,     b INT NOT NULL,     INDEX a_asc_b_desc (a ASC, b DESC) );

Khi bạn sử dụng lệnh SHOW CREATE TABLE trong MySQL 5.7, bạn sẽ thấy rằng DESC bị bỏ qua như dưới đây:

mysql> SHOW CREATE TABLE t\G; *************************** 1. row ***************************        Table: t Create Table: CREATE TABLE `t` (   `a` int(11) NOT NULL,   `b` int(11) NOT NULL,   KEY `a_asc_b_desc` (`a`,`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

Bắt đầu từ MySQL 8.0 thì các giá trị được lưu trữ theo thứ tự giảm dần nếu bạn sử dụng từ khóa DESC để tạo chỉ mục.

Sau đây cho thấy cấu trúc bảng trong MySQL 8.0:

mysql> SHOW CREATE TABLE t\G; *************************** 1. row ***************************        Table: t Create Table: CREATE TABLE `t` (   `a` int(11) NOT NULL,   `b` int(11) NOT NULL,   KEY `a_asc_b_desc` (`a`,`b` DESC) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)

+ Ví dụ Descending Index trong MySQL

Đầu tiên hãy tạo ra bảng t và nhiều index khác nhau như sau:

DROP TABLE t;   CREATE TABLE t (     a INT,     b INT,     INDEX a_asc_b_asc (a ASC , b ASC),     INDEX a_asc_b_desc (a ASC , b DESC),     INDEX a_desc_b_asc (a DESC , b ASC),     INDEX a_desc_b_desc (a DESC , b DESC) );

Tiếp theo hãy sử dụng đoạn code SQL Procedure dưới đây để insert 10000 dòng dữ liệu vào table này.

CREATE PROCEDURE insertSampleData(     IN rowCount INT,     IN low INT,     IN high INT ) BEGIN     DECLARE counter INT DEFAULT 0;     REPEAT         SET counter := counter + 1;         -- insert data         INSERT INTO t(a,b)         VALUES(             ROUND((RAND() * (high-low))+high),             ROUND((RAND() * (high-low))+high)         );     UNTIL counter >= rowCount     END REPEAT; END$$

Chạy lệnh sau để insert:

CALL insertSampleData(10000,1,1000);

Bây giờ mình có một vài yêu cầu cụ thể như sau:

Yêu cầu 1: Sắp xếp giảm dần theo hai cột a và b

EXPLAIN SELECT     * FROM     t ORDER BY a , b; -- use index a_asc_b_asc

Kết quả:

Yêu cầu 2: Sắp xếp cột a tăng dần, cột b giảm dần

EXPLAIN SELECT     * FROM     t ORDER BY a , b DESC; -- use index a_asc_b_desc

Kêt quả:

Yêu cầu 3: Sắp xếp cột a giảm dần, cột b tăn dần.

EXPLAIN SELECT     * FROM     t ORDER BY a DESC , b; -- use index a_desc_b_asc

Kết quả:

F. Invisible Index trong Mysql

+  Invisible Index là gì?

Invisible Index là trạng thái chỉ mục ẩn, tức là những chỉ mục nào được thiết lập trạng thái này thì sẽ không được sử dụng. Muốn bật lại thì thay đổi trạng thái từ INVISIBLE sang VISIBLE.

Để tạo invisible index thì ta sử dụng cú pháp sau:

CREATE INDEX index_name ON table_name( c1, c2, ...) INVISIBLE;

Trong đó lệnh CREATE INDEX thông báo cho MySQL tạo ra một index có tên là index_name nằm trong table table_name và xác định chỉ mục ẩn bằng từ khóa INVISIBLE.

Như trong ví dụ dưới đây mình đã tạo ra một chỉ mục ẩn có tên là extension nằm trong bảng employees.

CREATE INDEX extension ON employees(extension) INVISIBLE;

Để thay đổi trạng thái của một chỉ mục từ INVISIBLE sang VISIBLE và ngược lại thì ta dùng cú pháp sau:

ALTER TABLE table_name ALTER INDEX index_name [VISIBLE | INVISIBLE];

Ví dụ sau đổi trạng tháu của chỉ mục extension từ INVISIBLE sang VISIBLE.

ALTER TABLE employees ALTER INDEX extension VISIBLE;

Bạn có thể kiểm tra trạng thái của chỉ mục bằng cách sử dụng lệnh select truy vấn vào table statistics nằm trong information_schema của MySQL.

SELECT     index_name,     is_visible FROM     information_schema.statistics WHERE     table_schema = 'classicmodels'         AND table_name = 'employees';

Kết quả dạng như sau:

Một cách khác, bạn cũng có thể dùng lệnh SHOW INDEXES và nhận kết quả tương đương.

SHOW INDEXES FROM employees;

Như mình đã nói ở đầu bài, trình tối ưu hóa sẽ không sử dụng những chỉ mục bị ẩn (INVISIBLE). Vậy câu hỏi đặt ra là tại sao MySQL lại đưa ra trạng thái chỉ mục này? Hãy suy nghĩ rằng có một số trường hợp bạn muốn tắt index tạm thời thay vì xóa chúng để xem tốc độ có tối ưu hơn không, sau đó bật lại để so sánh.

+ MySQL invisible index và primary key

Lưu ý rằng với chỉ mục primary key thì bạn không thể thiết lập chúng là invisible được, bởi đây là loại đặc biệt và duy nhất trong mỗi table, nó ảnh hưởng đến ràng buộc toàn vẹn của dữ liệu.

Giả sử bạn không muốn tạo khóa chính và thay vào đó là dùng UNIQUE, lúc này liệu có thay đổi được chỉ mục này sang trạng thái ẩn?

Câu trả lời là MySQL sẽ tự động hiểu chỉ mục UNIQUE đó là primary key nhé, mặc dù bạn chưa tạo primary key. Hãy xem ví dụ dưới đây để hiểu rõ hơn.

Trước tiên hãy tạo một table discounts có cấu trúc như sau:

CREATE TABLE discounts (     discount_id INT NOT NULL,     name VARCHAR(50) NOT NULL,     valid_from DATE NOT NULL,     valid_to DATE NOT NULL,     amount DEC(5 , 2 ) NOT NULL DEFAULT 0,     UNIQUE discount_id(discount_id) );

Mình đã không tạo khóa chính, thay vao fđó tạo UNIQUE. Bây giờ bạn hãy thử đổi trạng thái của key UNIQUE này sang invisible xem thế nào nhé.

ALTER TABLE discounts ALTER INDEX discount_id INVISIBLE;

Chạy câu SQL này bạn sẽ gặp lỗi như sau:

Error Code: 3522. A primary key index cannot be invisible

4. FULL TEXT SEARCH

A. Full Text Search là gì? Tại sao nên sử dụng?

Full Text Search là một kỹ thuật tìm kiếm các tài liệu không phù hợp với tiêu chí tìm kiếm. Tài liệu ở đây có thể là một mô tả sản phẩm, một bài viết được lưu trữ trong Database của MySQL.

Không phù hợp với tiêu chí tìm kiếm là như thế nào? Ví dụ bạn có 1000 bài viết và muốn tìm kiếm từ khóa "học lập trình miễn phí", nhưng trong đoạn văn đó không có từ khóa này, vì vậy cần phải tách nhỏ từ khóa đó ra để tìm kiếm đến bài có chứa các từ phù hợp nhất.

Thực tế bạn có thể sử dụng từ khóa Like trong MySQL đẻ tìm kiếm bằng cú pháp "%word1%word2%word3word1..." nhưng về tốc độ thì nó tốn khá nhiều tài nguyên, điều này xảy ra tình trạng overload nếu dữ liệu quá dài và quá nhiều.

+ Nên sử dụng Full Text Search trong MySQL?

Để trả lời câu hỏi này thì a hay cùng phân tích đến một vài đặc điểm.

Hiệu suất

MySQL phải quét toàn bộ bảng để tìm văn bản chính xác dựa trên một mẫu trong câu lệnh LIKE, nhưng full text search là một thể loại index nên sẽ cho tốc độ tốt hơn nhiều.

Sự linh hoạt

Tìm kiếm nội dung trọng từ khóa rất linh hoạt, không cần phải xuất hiện nguyên từ khóa mà nó sẽ phân nhỏ từng từ ra để tìm kiếm.

Xếp hàng tìm kiếm

Thực tế thì MySQL không trả về một dãy dự liệu có xếp hạng, vì vậy bạn không thể biết được đâu là bài có nội dung phù hợp nhất.

Tuy nhiên kể từ phiên bản 5.6 trở đi thì bạn có thể sử dụng order by để sắp xếp. Chi tiết thế nào thì mình sẽ trình bày ở các bài tiếp theo.

+ Những đặc điểm của Full Text Search trong MySQL

Sau đây là một số tính năng quan trọng của full-text-search trong MySQL:

- Hoạt động giống lệnh Like:

MySQL cung cấp câu LIKE để thực hiện tìm kiếm toàn văn. Bạn có thể áp dụng nó như Full Text Serch.

- Tự động cập nhật Index:

MySQL tự động cập nhật chỉ mục của cột văn bản bất cứ khi nào dữ liệu của cột đó thay đổi.

- Kích thước index vừa phải

Kích thước của chỉ số FULLTEXT tương đối nhỏ.

- Tốc độ

Cuối cùng nhưng không kém phần quan trọng, full-text-search cho tốc độ tìm kiếm nhanh hơn những câu truy vấn thông thường, bởi nó đã được lập chỉ mục khi mới thêm / cập nhật data.

+ Một số thao tác với Full Text Search

Dưới đây là một số thao tác với full text search.

• Tạo Full Text Search
• Match và Against trong MySQL
• Boolean Full-Text Searches
• Query Expansion Search

* Lưu ý: Không phải tất cả các kiểu định dạng của table đều có thể áp dụng full text search. Trong phiên bản MySQL 5.6 trở lên, chỉ có các table thiết lập kiểu MyISAM và InnoDB là có hỗ trợ.

B. Tạo Full Text Search

+ Tạo Full Text Search ngay trong lúc tạo bảng

Trong lệnh Create Table bạn dễ dàng thêm index full text search bằng cách sử dụng từ khóa FULLTEXT. Cú pháp như sau:

CREATE TABLE table_name(     column_list,     ...,     FULLTEXT (column1,column2,..) );

Như vậy, để tạo ra nhiều index thì bạn hãy thêm nó và cahs nhau bởi dấu phẩy. Xem ví dụ dưới đây, mình đã thêm full text search cho field post_content nằm trong table posts khi vừa mới khởi tạo chúng.

CREATE TABLE posts (   id INT NOT NULL AUTO_INCREMENT,   title VARCHAR(255) NOT NULL,   body TEXT,   PRIMARY KEY (id),   FULLTEXT (post_content ) );

+ Tạo Full Text Search trong lệnh Alter Table

Cú pháp dưới đây giúp bạn tạo được index full text search bằng cách sử dụng lệnh alter table trong MySQL.

ALTER TABLE table_name  ADD FULLTEXT(column_name1, column_name2,…)

Nếu bạn xem kỹ thì thực ra nó cũng không khác gì việc tạo các loại index khác, chỉ có điều kỹ thuật index mỗi loại mỗi khác.

Như phần 1, bạn có thể thêm nhiều column và cách nhau bởi dấu phâỷ.

Như trong ví dụ dưới đây mình đã thêm index cho 2 field productDescription và productLine.

ALTER TABLE products  ADD FULLTEXT(productDescription,productLine)

+ Tạo Full Text Search bằng CREATE INDEX trong MySQL

Một cách khác cũng khá đơn giản đó là sử dụng lệnh CREATE INDEX để tạo index full text search cho một table bất kì.

CREATE FULLTEXT INDEX index_name ON table_name(idx_column_name,...)

Ví dụ: Tạo chỉ mục full text cho hai field (addressLine1, addressLine2) nằm trong table offices.

CREATE FULLTEXT INDEX address ON offices(addressLine1,addressLine2)

Lưu ý rằng với những table đã có lượng dữ liệu lớn thì lệnh này sẽ chạy chậm hơn bình thường,bởi MySQL sẽ mất thời gian lập chỉ mục cho những record có sẵn đó. Vì vậy bạn nên thiết kế dữ liệu thuật chuẩn và tạo chỉ mục cẩn thận thì sẽ giúp MySQL hoạt động tốt hơn.

C. Xóa Index Full Text Search?

Nếu bạn lỡ tay tạo rồi nhưng sau muốn xóa index full text search thì sử dụng lệnh ALTER TABLE DROP INDEX nhé.

ALTER TABLE table_name DROP INDEX index_name;

Theo như cú pháp này thì bạn phải đặt tên cho Index thì mới xóa được. Việc đặt tên cũng là cách tốt nhất để giúp bạn quản lý các chỉ mục tốt hơn.

Ví dụ dưới đây mình đã xóa Index có tên là Address từ bảng offices.

ALTER TABLE offices DROP INDEX address;

D. Hàm MATCH () và AGAINST () trong Mysql

Trong kỹ thuật tìm kiếm Full Text Search, MySQL tìm kiếm các hàng hoặc tài liệu có liên quan đến truy vấn ngôn ngữ tự nhiên của văn bản một cách tự do. MySQL tính toán mức độ liên quan dựa trên các yếu tố khác nhau bao gồm số lượng từ trong tài liệu, số lượng từ duy nhất trong tài liệu, tổng số từ trong collection và số lượng tài liệu (hàng) có chứa một từ cụ thể.

Để thực hiện tìm kiếm toàn văn bản bằng tìm kiếm tự nhiên ta sử dụng các hàm MATCH() và AGAINST(). Hàm MATCH () chỉ định cột nơi bạn muốn tìm kiếm và hàm AGAINST() xác định biểu thức tìm kiếm sẽ được sử dụng.

+ Cách sử dụng hàm MATCH () và AGAINST ()

Trước khi bắt đầu thì ta hãy tạo một table có cấu trúc như sau:

Sau đó mình tạo Index Full Text Search cho cột productLine.

ALTER TABLE products ADD FULLTEXT(productline);

Tiếp theo, để truy vấn tìm kiếm cho cột này bằng kỹ thuật Full Text Search thì ta sử dụng hai hàm trên như sau:

SELECT     productName,     productLine FROM products WHERE     MATCH(productLine)     AGAINST('Classic');

Kết quả trả về là những record mà cột productLine có xuất hiện từ Classic.

Để tìm theo hai từ Classic hoặc Vintage thì ta sẽ ngăn cách nó bằng dấu phẩy.

SELECT     productName,     productLine FROM products WHERE     MATCH(productline)     AGAINST('Classic,Vintage') ORDER BY productName;

Kết quả:

Hàm AGAINST() theo mặc định sẽ nằm ở chế độ IN NATURAL LANGUAGE MODE.

E. Boolean Full-Text Searches trong Mysql

+ MySQL Boolean Full-Text Searches là gì?

Đây là các chế độ tìm kiếm khi sử dụng để truy vấn cho những column có sử dụng chỉ mục full text search. Theo tìm kiếm tự nhiên thì trong văn bản chỉ cần xuất hiện một trong số những từ mà ta đặt nó ở đầu vào là sẽ trả kết quả về. Tuy nhiên có một số trường hợp mình muốn phải xuất hiện ít nhất 2 từ nào đó thì lúc này phải sử dụng các chế độ MODE.

Ví dụ: Tìm kiếm từ khóa Truck trong chế độ bắt buộc phải có (IN BOOLEAN MODE)

SELECT productName, productline FROM products WHERE MATCH(productName)       AGAINST('Truck' IN BOOLEAN MODE )

Kết quả:

Hai sản phẩm trả về do có chứa từ Truck.

Để tìm kiếm các record xuất hiện từ Truck nhưng không được xuất hiện từ Pickup thì bạn sử dụng dấu trừ - đằng trước cụm từ không muốn xuất hiện.

SELECT productName, productline FROM products WHERE MATCH(productName) AGAINST('Truck -Pickup' IN BOOLEAN MODE )

+ Các toán tử trong Boolean Full-Text Searches

Dưới đây là danh sách những toán tử giúp bạn có thể xư lý tìm kiếm một cách linh động hơn.

Toán tử	Mô tả
+	Từ phải xuất hiện
–	Từ không được xuất hiện
>	Bao gồm từ này, và tăng giá trị xếp hạng
<	Bao gồm từ này, và giảm giá trị xếp hạng
()	Nhóm các từ thành các biểu thức con (cho phép chúng được bao gồm, loại trừ, xếp hạng, v.v. như một nhóm).
~	Phủ định một từ được xếp hạng
*	Ký tự đại diện ở cuối từ
“”	Xác định một cụm từ (trái ngược với danh sách các từ riêng lẻ, toàn bộ cụm từ được khớp để đưa vào hoặc loại trừ).

Các ví dụ sau minh họa cách sử dụng các toán tử toàn văn bản boolean trong truy vấn tìm kiếm:

Ví dụ 1: Để tìm kiếm các hàng có ít nhất một trong hai từ: mysql hoặc tutorial.

‘mysql tutorial’

Ví dụ 2: Để tìm các hàng xuất hiện cả hai từ mysql và tutorial.

‘+mysql +tutorial’

Ví dụ 3: Để tìm các hàng bắt buộc có từ mysql nhưng từ tutorial thì có thể có hoặc không.

‘+mysql tutorial’

Ví dụ 4: Để tìm các hàng xuất hiện từ mysql nhưng không được xuất hiện từ tutorial.

‘+mysql -tutorial’

Ví dụ 5: Để tìm kiếm các hàng có chứa từ mysql và xếp hạng hàng thấp hơn nếu nó chứa từ tutorial.

‘+mysql ~tutorial’

Ví dụ 6: Để tìm kiếm các hàng có chứa các từ mysql và tutorial, hoặc tutorial và training, nhưng đặt các hàng có chứa mysql tutorial cao hơn so với mysql training.

‘+mysql +(>tutorial <training)’

Ví dụ 7: Để tìm kiếm các từ bắt đầu bằng chữ my, ví dụ mysql, myadmin.

‘my*’

+ Các tính chất của Boolean Full-Text Searches

• MySQL không tự động sắp xếp các hàng theo mức độ liên quan theo thứ tự giảm dần trong kỹ thuật Boolean full text search.
• Để thực hiện các truy vấn Boolean, các bảng InnoDB yêu cầu tất cả các cột của biểu thức MATCH phải có chỉ mục FULLTEXT. Lưu ý rằng các bảng MyISAM không yêu cầu điều này, mặc dù tìm kiếm khá chậm.
• MySQL không hỗ trợ nhiều toán tử Boolean trên truy vấn tìm kiếm trên các bảng InnoDB. Ví dụ từ '++ mysql' sẽ trả về một lỗi. Tuy nhiên, MyISAM thì lai khác, nó bỏ qua các toán tử khác và sử dụng toán tử gần nhất. Ví dụ từ '+ -mysql' sẽ trở thành ‘ -mysql'.
• Full Text Search của InnoDB không hỗ trợ dấu cộng (+) hoặc dấu trừ (-) trong từ khóa tìm kiếm, nó chỉ hỗ trợ nằm ở hàng đầu vì đó là các toán tử boolean. MySQL sẽ báo lỗi nếu bạn tìm kiếm từ là 'mysql +', hoặc 'mysql-'.
• Ngưỡng 50% có nghĩa là nếu một từ xuất hiện hơn 50% số hàng, MySQL sẽ bỏ qua từ đó trong kết quả tìm kiếm.

F. Query Expansion Full Text Search trong MySQL

 Query Expansion Full Text Search là cách mở rộng truy vấn tìm kiếm trong Full Text Search MySQL, dựa trên phản hồi liên quan tự nhiên.

+ Hiểu hơn về query expansion trong Full Text Search

Thông thường, người dùng tìm kiếm thông tin dựa trên kiến ​​thức của họ. Họ sử dụng kinh nghiệm của mình để đưa ra các từ khóa để tìm kiếm thông tin và đôi khi, những từ khóa này quá ngắn.

Để giúp người dùng tìm thông tin dựa trên những từ khóa quá ngắn này, công cụ tìm kiếm toàn văn bản MySQL giới thiệu một khái niệm gọi là mở rộng truy vấn.

Việc mở rộng truy vấn được sử dụng để mở rộng kết quả tìm kiếm của các full text search dựa trên phản hồi liên quan (hoặc mở rộng truy vấn mù). Về mặt kỹ thuật, MySQL full text search thực hiện các bước sau khi mở rộng truy vấn được sử dụng:

• Đầu tiên, tìm kiếm tất cả các hàng khớp với truy vấn tìm kiếm.
• Thứ hai, tìm các từ có liên quan trong tất cả các hàng từ kết quả tìm kiếm.
• Thứ ba, tìm kiếm lại dựa trên các từ có liên quan thay vì các từ khóa ban đầu được chỉ định bởi người dùng.

Bạn có thể sử dụng mở rộng truy vấn khi kết quả tìm kiếm quá ít. Bạn thực hiện lại các tìm kiếm với việc mở rộng truy vấn nhằm cung cấp cho người dùng nhiều thông tin liên quan và phù hợp với những gì họ đang tìm kiếm.

Để sử dụng query expansion thì ta sử dụng từ khóa WITH QUERY EXPANSION đặt trong hàm AGAINST (). Dưới đây là minh họa cú pháp của truy vấn bằng cách sử dụng WITH QUERY EXPANSION.

SELECT column1, column2 FROM table1 WHERE MATCH(column1,column2)       AGAINST('keyword',WITH QUERY EXPANSION);

Ví dụ query expansion trong MySQL

Hãy xem một vài ví dụ sẽ giúp bạn hiểu rõ hơn là đống lý thuyết cồng kềnh phía trên.

Trong ví dụ này chúng ta sẽ sử dụng cột ProductName của bảng products để thử nghiệm tính năng mở rộng truy vấn.

Bước 1: Hãy tạo một full text search vào cột productName.

ALTER TABLE products ADD FULLTEXT(productName);

Bước 2: Tìm kiếm với từ khóa 1992, bước này chưa sử dụng query expansion.

SELECT     productName FROM     products WHERE     MATCH (productName)     AGAINST ('1992' );

Bạn sẽ nhận được kết quả như sau:

Như bạn thấy trong kết quả trả về có 2 record bởi vì chỉ có hai dòng này là xuất hiện từ 1992.

Bước 3: Hãy thử sử dụng Query Expansion xem thế nào nhé.

SELECT     productName FROM     products WHERE     MATCH(productName)     AGAINST('1992' WITH QUERY EXPANSION);

Kết quả trả về nhiều hơn 2 hàng bởi vì ta sử dụng mở rộng tìm kiếm. Hai hàng đầu tiên là tương quan giống nhất nên được xếp lên đầu, các hàng khác chính là phần mở rộng bởi nó chứa những từ nằm trong hai hàng đầu tiên. Ví dụ từ "Ferrari".

5. Trigger trong MySQL

A. Trigger là gì?

Trigger là trình kích hoạt, sẽ được kích hoạt khi một tác động nào đó vào nó.

Trong MySQL Trigger có nghĩa là một đoạn code SQL xử lý một chức năng nào đó, nó hoạt động ngầm và được chạy khi một trong các tác động như Insert / Update / Delete vào table.

Ví dụ: Bạn muốn khi thêm một nhân viên mới thì sẽ gửi 1 tin nhắn đến người dùng. Như vậy bạn sẽ viết trigger vào table employee, khi sự kiện insert xảy ra thì đồng thời nó sẽ thêm luôn một dòng dữ liệu vào bảng message.

B. Cách dùng lệnh Create Trigger trong MySQL

Lênh CREATE TRIGGER sẽ giúp bạn tạo ra một trigger mới, bằng cách sử dụng cú pháp sau:

CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE| DELETE } ON table_name FOR EACH ROW trigger_body;

Trong đó:

• trigger_name là tên của trigger mà bạn muốn đặt.
• INSERT | UPDATE| DELETE: Mỗi trigger sẽ được gắn cho một trong ba hành động này.
• BEFORE | AFTER: Nếu bạn chọn Before thì trigger sẽ chạy trước khi hành động xảy ra, ngược lại After là sau hành động xảy ra.
• ON table_name là table sẽ được gắn trigger này.
• FOR EACH ROW là sẽ duyệt qua từng row.

Phần trigger_body chính là nội dung xử lý khi xảy ra sự kiện. Ta sẽ dựa vào hai biến tạm là OLD và NEW để biết đâu là dữ liệu cũ va đâu là dữ liệu mới.

Ví dụ: Khi cập nhật thông tin khách hàng thì lưu thông tin cũ vào một table backup. Lúc này để lấy thông tin cũ thì bạn sử dụng OLD.ten_field, còn thông tin mới chuẩn bị update là NEW.ten_field.

Tham khảo bảng mô tả dưới đây để biết được khi nào thì hai biến đó tồn tại.

Trigger Event	OLD	NEW
INSERT	No	Yes
UPDATE	Yes	Yes
DELETE	Yes	No

Ví dụ tạo Trigger trong MySQL

Trigger nói chung không khó nếu bạn hiểu bản chất của nó. Và đương nhiên để học được thì bạn phải tìm hiểu qua Procedure.

Bước 1: Hãy tạo một table employees có cấu trúc như sau.

Sau đó bạn tạo thêm một table tên là employees_audit, table này chứa thông tin cũ mà bạn đã cập nhật cho khách hàng.

CREATE TABLE employees_audit (     id INT AUTO_INCREMENT PRIMARY KEY,     employeeNumber INT NOT NULL,     lastname VARCHAR(50) NOT NULL,     changedat DATETIME DEFAULT NULL,     action VARCHAR(50) DEFAULT NULL );

Bây giờ mình sẽ tạo một Trigger như sau:

• Đặt tên là before_employee_update.
• Chọn cách hoạt động là BEFORE UPDATE.
• Gắn vào bảng employees.

EATE TRIGGER before_employee_update     BEFORE UPDATE ON employees     FOR EACH ROW  INSERT INTO employees_audit  SET action = 'update',      employeeNumber = OLD.employeeNumber,      lastname = OLD.lastname,      changedat = NOW();

Như bạn thấy, mình sử dụng OLD.lastname và OLD.employeeNumber để lấy thông tin cũ trong table employees.

Chạy lệnh này xong mình dùng lệnh SHOW TRIGGERS để xem trong hệ thóng có không.

SHOW TRIGGERS;

Ngoài ra, nếu bạn xem lược đồ bằng MySQL Workbench thì chọn employees > triggers bạn sẽ thấy trigger before_employee_update như trong ảnh chụp màn hình bên dưới:

Sau đó hãy thử cập nhật một row bất kì trong table employees.

UPDATE employees SET     lastName = 'Phan' WHERE     employeeNumber = 1056;

Cuối cùng, truy vấn bảng employees_audit để kiểm tra xem kích hoạt có được kích hoạt bởi câu lệnh UPDATE không:

SELECT * FROM employees_audit;

Kết quả đã xuất hiện một dòng dữ liệu trong bảng này, mặc dù mình không hề chạy lệnh insert trên nó.

C. Xóa Trigger

+ Cú pháp lệnh Drop Trigger

Lệnh Drop Trigger khá đơn giản, bạn chỉ việc sử dụng theo cú pháp sau:

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

Trong đó trigger_name chính là tên trigger mà bạn đã tạo. schema_name chính là schema mà trigger này thuộc về, mặc định nó là schema hiện tai mà bạn đang kết nối.

Lệnh IF EXISTS sẽ giúp việc xóa trigger được an toàn hơn, nghĩa là nó chỉ xóa chỉ khi nào trigger đó tồn tại. Nếu bạn không có dòng này thì sẽ xuất hiện lỗi trong trường hợp không có trigger nào tên là trigger_name.

Bạn phải lưu ý rằng một khi đã xóa trigger thì nó sẽ mất vĩnh viễn, ko còn việc gọi tự động nào kể từ lúc đó về sau.

+ Ví dụ xóa trigger với lệnh Drop Trigger

Chúng ta sẽ làm một bài tập thực hành. Trước tiên hãy tạo một table có tên là billings với cấu trúc như lệnh SQL sau:

CREATE TABLE billings (     billingNo INT AUTO_INCREMENT,     customerNo INT,     billingDate DATE,     amount DEC(10 , 2 ),     PRIMARY KEY (billingNo) );

Tiếp theo tạo một trigger có tên là BEFORE UPDATE và gán vào table billings.

DELIMITER $$ CREATE TRIGGER before_billing_update     BEFORE UPDATE     ON billings FOR EACH ROW BEGIN     IF new.amount > old.amount * 10 THEN         SIGNAL SQLSTATE '45000'             SET MESSAGE_TEXT = 'New amount cannot be 10 times greater than the current amount.';     END IF; END$$    DELIMITER ;

Nếu bạn không biết dòng DELIMITER là gì thì hãy đọc lại kiến thức Store Procedure.

Trigger sẽ được kích hoạt trước khi chạy lệnh update. Nội dung của nó là hiển thị lỗi nếu giá mới lớn hơn gấp 10 lần giá cũ.

Tiếp theo hãy dùng lệnh SHOW TRIGGERS; để xem tồn tại không nhé.

SHOW TRIGGERS;

Bây giờ hãy chạy lệnh Drop trigger xem thế nào nhé.

DROP TRIGGER before_billing_update

Chạy lại lệnh SHOW TRIGGERS; để xem có gì khác biệt không.

SHOW TRIGGERS;

Và kết quả là nó đã bị xóa ra khỏi database.

D. Tìm hiểu Before Insert Trigger trong MySQL

Giống như tên gọi của nó, đây là loại trigger sẽ được gọi trước khi insert dữ liệu vào table, điều này khác hoàn toàn với After Trigger.

Cú pháp dưới đây giúp bạn tạo ra loại trigger này:

CREATE TRIGGER trigger_name     BEFORE INSERT     ON table_name FOR EACH ROW trigger_body;

Về cú pháp cơ bản thì chúng ta đã được học ở bài trước, riêng bài này thì công thức xác định rõ là loại BEFORE INSERT.

Nếu trong trigger của bạn có nhiều câu lệnh SQL thì phải đặt nó trong cặp BEGIN .. END nhé.

DELIMITER $$   CREATE TRIGGER trigger_name     BEFORE INSERT     ON table_name FOR EACH ROW BEGIN     -- statements END$$    DELIMITER ;

Lưu ý rằng trong loại trigger này bạn có thể truy cập lấy dữ liệu mới, nhưng riêng dữ liệu cũ là không được bởi vì bản chất nó chưa đụng gì tới dữ liệu cũ nên không được lưu vết. Tức là dùng NEW.field_name thì được, OLD.field_name là không được.

Ví dụ Before Insert Trigger trong MySQL

Chúng ta sẽ tạo một before insert trigger để ...

-Tạo database:

Đầu tiên mình tạo một table có tên là WorkCenters như sau:

DROP TABLE IF EXISTS WorkCenters;   CREATE TABLE WorkCenters (     id INT AUTO_INCREMENT PRIMARY KEY,     name VARCHAR(100) NOT NULL,     capacity INT NOT NULL );

Tiếp theo tạo một bảng WorkCenterStats để lưu trữ tổng công suất làm việc.

DROP TABLE IF EXISTS WorkCenterStats;   CREATE TABLE WorkCenterStats(     totalCapacity INT NOT NULL );

-Tạo Trigger:

Bây giờ tạo một before insert trigger, khi thêm một dữ liệu mới vào table WorkCenterStats thì sẽ cập nhật tổng công suất làm việc.

DELIMITER $$ CREATE TRIGGER before_workcenters_insert BEFORE INSERT ON WorkCenters FOR EACH ROW BEGIN     DECLARE rowcount INT;          SELECT COUNT(*)     INTO rowcount     FROM WorkCenterStats;           IF rowcount > 0 THEN         UPDATE WorkCenterStats         SET totalCapacity = totalCapacity + new.capacity;     ELSE         INSERT INTO WorkCenterStats(totalCapacity)         VALUES(new.capacity);     END IF;   END $$   DELIMITER ;

Đoạn code trên xảy ra hai trường hợp:

Nếu thêm vào lần đầu tiên thì ta phải sử dụng lệnh insert, lúc này capacity chính là giá trị nhập vào luôn.

INSERT INTO WorkCenterStats(totalCapacity) VALUES(new.capacity);

Nếu thêm vào các lần tiếp theo thì ta update cộng dồn capacity lên.

UPDATE WorkCenterStats SET totalCapacity = totalCapacity + new.capacity;

-Test trigger:

Bây giờ hãy thử insert một vài dòng dữ liệu vào để xem trigger có hoạt động không nhé.

Đầu tiên chạy lệnh thêm dữ liệu như sau:

INSERT INTO WorkCenters(name, capacity) VALUES('Mold Machine',100);

Tiếp theo truy vấn vào table WorkCenterStats xem có gì trong đó không.

SELECT * FROM WorkCenterStats;

 Dữ liệu đã tự động thêm vào.

Hãy thử insert thêm một dòng dữ liệu nữa.

INSERT INTO WorkCenters(name, capacity) VALUES('Packing',200);

Chạy câu lệnh này xong thì totalCapacity phải có giá trị 300. Và đúng như thế thật.

Như vậy là trigger đã hoạt động đúng. Đây là các bước test quan trọng để kiểm tra trigger có hoạt động tốt hay không.

E. After Insert Trigger trong MySQL

Đây là loại trigger được gọi sau khi dữ liệu được insert vào table.

+ Cú pháp After Insert Trigger trong MySQL

After Insert Trigger sẽ được tự động gọi sau khi sự kiện insert xảy ra và hoàn thành.

Cú pháp dưới đây sẽ giúp bạn tạo được After Insert Trigger.

CREATE TRIGGER trigger_name     AFTER INSERT     ON table_name FOR EACH ROW         trigger_body

Trong cú pháp này thì bạn chú ý lệnh AFTER INSERT, nó giúp MySQL phân biệt được đây là loại After Insert Trigger.

Nếu bạn có nhiều dòng lệnh trong phần body thì hãy đặt nó trong cặp BEGIN .. BODY.

DELIMITER $$ CREATE TRIGGER trigger_name     AFTER INSERT     ON table_name FOR EACH ROW BEGIN     -- statements END$$    DELIMITER ;

Với loại trigger này bạn có thể truy xuất được giá trị của NEW nhưng không thay đổi được chúng, bởi bản chất nó đã insert xong rồi. Riêng đối với OLD thì không được vì nó không hỗ trợ trong loại INSERT Trigger.

+ Ví dụ After Insert Trigger trong MySQL

Chúng ta sẽ làm các bước tương tự như bài trước, nhưng sẽ tạo một một số table mới và thao tác trên đó.

-Tạo database:

Bạn hãy chạy lệnh SQL dưới đây để tạo bảng members.

DROP TABLE IF EXISTS members; CREATE TABLE members (     id INT AUTO_INCREMENT,     name VARCHAR(100) NOT NULL,     email VARCHAR(255),     birthDate DATE,     PRIMARY KEY (id) );

Tiếp theo tạo thêm bảng reminders để lưu trữ tin nhắn nhắc nhở cho các thành viên.

DROP TABLE IF EXISTS reminders; CREATE TABLE reminders (     id INT AUTO_INCREMENT,     memberId INT,     message VARCHAR(255) NOT NULL,     PRIMARY KEY (id , memberId) );

-Tạo trigger:

Đoạn code trigger dưới đây sẽ thêm một tin nhắn vào bảng reminders nếu người dùng không nhập dữ liệu cho field birthDate.

DELIMITER $$ CREATE TRIGGER after_members_insert AFTER INSERT ON members FOR EACH ROW BEGIN     IF NEW.birthDate IS NULL THEN         INSERT INTO reminders(memberId, message)         VALUES(new.id,CONCAT('Hi ', NEW.name, ', please update your date of birth.'));     END IF; END$$ DELIMITER ;

Ở trên sử dụng lệnh if else để kiểm tra giá trị NEW.birthDate có null hay không, nếu null thì thêm một dòng tin nhắn cho member đó.

-Test Trigger:

Ok, bước cuối cùng và quan trọng nhất là test xem trigger hoạt động có ổn định hay không. Khá đơn giản, ta chỉ cần thêm một vài dòng dữ liệu và xem data của 2 table là được.

Thêm 2 dòng dữ liệu, trong đó có 1 dòng ngày sinh có giá trị null.

INSERT INTO members(name, email, birthDate) VALUES     ('John Doe', 'john.doe@example.com', NULL),     ('Jane Doe', 'jane.doe@example.com','2000-01-01');

Dữ liệu lúc này trong bảng members sẽ như sau:

Nếu trigger hoạt động thì trong bảng reminders phải sẽ được thêm một record tin nhắn, và đó chính là dòng đầu tiên.

SELECT * FROM reminders;

Dữ liệu trả về là:

Như vậy là trigger đã hoạt động đúng.

F. Before Update Trigger 

Đây là loại trigger có tác dụng trước khi bạn update một row nào đó trong database.

+ Cú pháp Before Update Trigger 

MySQL BEFORE UPDATE Trigger sẽ được tự động gọi khi bạn thực hiện một hành động update các row trong table. Trigger sẽ chạy trước rồi mới update nên bạn hoàn toàn có thể thay đổi dữ liệu truyền vào.

Cú pháp của nó như sau:

CREATE TRIGGER trigger_name BEFORE UPDATE ON table_name FOR EACH ROW trigger_body

Trong đó lệnh BEFORE UPDATE thông báo cho MySQL biết là đang muốn tạo trigger kiểu BEFORE UPDATE.

Về phần trigger body nếu bạn có nhiều câu lệnh trong đó thì phải đặt trong cặp BEGIN .. END nhé.

DELIMITER $$ CREATE TRIGGER trigger_name     BEFORE UPDATE     ON table_name FOR EACH ROW BEGIN     -- statements END$$    DELIMITER ;

Trong loại BEFORE UPDATE trigger bạn có thể thay đổi dữ liệu cho NEW nhưng không thể thay đổi dữ liệu của OLD nhé.

+ Ví dụ Before Update Trigger 

Không có gì dễ hiểu bằng một ví dụ. Hãy cùng mình làm một chức năng nhỏ nhé.

- Tạo database:

Đầu tiên hãy tạo một table có tên là sales, cấu trúc như sau:

DROP TABLE IF EXISTS sales; CREATE TABLE sales (     id INT AUTO_INCREMENT,     product VARCHAR(100) NOT NULL,     quantity INT NOT NULL DEFAULT 0,     fiscalYear SMALLINT NOT NULL,     fiscalMonth TINYINT NOT NULL,     CHECK(fiscalMonth >= 1 AND fiscalMonth <= 12),     CHECK(fiscalYear BETWEEN 2000 and 2050),     CHECK (quantity >=0),     UNIQUE(product, fiscalYear, fiscalMonth),     PRIMARY KEY(id) );

Tiếp theo mình sẽ thêm một vài dòng dữ liệu để sau này thử nghiệm trigger bằng lệnh update.

INSERT INTO sales(product, quantity, fiscalYear, fiscalMonth) VALUES     ('2003 Harley-Davidson Eagle Drag Bike',120, 2020,1),     ('1969 Corvair Monza', 150,2020,1),     ('1970 Plymouth Hemi Cuda', 200,2020,1);

Để chắc ăn thì hãy kiểm tra trong table sales đã có dữ liệu chưa nhé.

SELECT * FROM sales;

- Tạo Before Update trigger:

Mình sẽ viết một trigger bắt sự kiện update, kiểm tra nếu quantity update lớn gấp 3 lần hiện tại thì thông báo lỗi "cannot be 3 times greater than the current quantity".

DELIMITER $$ CREATE TRIGGER before_sales_update BEFORE UPDATE ON sales FOR EACH ROW BEGIN     DECLARE errorMessage VARCHAR(255);     SET errorMessage = CONCAT('The new quantity ',                         NEW.quantity,                         ' cannot be 3 times greater than the current quantity ',                         OLD.quantity);                             IF new.quantity > old.quantity * 3 THEN         SIGNAL SQLSTATE '45000'             SET MESSAGE_TEXT = errorMessage;     END IF; END $$ DELIMITER ;

Trong lệnh SQL trên thì bạn chú ý một số điểm như sau:

Mình đã đặt tên cho trigger là before_sales_update, gắn vào table sales. Vì có nhiều lệnh bên trong nên đặt trong cặp BEGIN .. END.

Đoạn code dưới đây là khai báo một biến thông báo lỗi.

SET errorMessage = CONCAT('The new quantity ',   NEW.quantity,   ' cannot be 3 times greater than the current quantity ',   OLD.quantity);

Đoạn code dưới đây kiểm tra nếu giá trị nhập vào (NEW) gấp 3 lần hiện tại (OLD) thì gán lỗi và trả kết quả về.

IF new.quantity > old.quantity * 3 THEN    SIGNAL SQLSTATE '45000'    SET MESSAGE_TEXT = errorMessage;

- Thử nghiệm Trigger:

Bây giờ hãy thử nghiệm xem trigger hoạt động có đúng không nhé.

Bạn hãy để ý dòng dữ liệu thứ nhất có quanlity là 150. Bây giờ mình thử update nó lên 500 xem có xuất hiện lỗi không nhé.

UPDATE sales SET quantity = 500 WHERE id = 1;

Bạn sẽ nhận được thông báo lỗi như sau:

Error Code: 1644. The new quantity 500 cannot be 3 times greater than the current quantity 150

G. After Update Trigger trong MySQL

Đây là loại trigger được gọi sau khi lệnh update hoàn thành, vì vậy bạn có thể truy cập OLD và NEW nhưng không thay đổi được giá trị của chúng.

+ Cú pháp After Update Trigger 

Như thường lệ, bạn phải hiểu rằng đây là loại trigger hoạt động sau khi lệnh update thực hiện xong.

Cú pháp của nó như sau:

CREATE TRIGGER trigger_name AFTER UPDATE ON table_name FOR EACH ROW trigger_body

MySQL nhận biết đây là loại AFTER UPDATE trigger thông qua từ khóa này.

Về phần trigger body bạn nên đặt trong thẻ BEGIN .. END để tường minh, vì dù sao body nếu có nhiều lệnh SQL thì đó là điều bắt buộc.

DELIMITER $$ CREATE TRIGGER trigger_name     AFTER UPDATE     ON table_name FOR EACH ROW BEGIN     -- statements END$$    DELIMITER ;

Như mình đã nói ở trên, bạn có thể truy xuất đến giá trị của OLD và NEW nhưng không thể cập nhật được, vì MySQL đã thực hiện update xong mới gọi đến trigger này.

+ Ví dụ After Update Trigger trong MySQL

Hãy xem ví dụ dưới đây, chúng ta vẫn làm việc với bảng sales như ở bài trước. Hãy chạy lệnh SQL này để tạo bảng.

- Tạo table sale:

Dưới đây là cấu trúc của bảng Sales.

DROP TABLE IF EXISTS Sales; CREATE TABLE Sales (     id INT AUTO_INCREMENT,     product VARCHAR(100) NOT NULL,     quantity INT NOT NULL DEFAULT 0,     fiscalYear SMALLINT NOT NULL,     fiscalMonth TINYINT NOT NULL,     CHECK(fiscalMonth >= 1 AND fiscalMonth <= 12),     CHECK(fiscalYear BETWEEN 2000 and 2050),     CHECK (quantity >=0),     UNIQUE(product, fiscalYear, fiscalMonth),     PRIMARY KEY(id) );

Tiếp theo hãy insert 3 dòng dữ liệu vào, khác hoàn toàn với bài trước.

INSERT INTO Sales(product, quantity, fiscalYear, fiscalMonth) VALUES     ('2001 Ferrari Enzo',140, 2021,1),     ('1998 Chrysler Plymouth Prowler', 110,2021,1),     ('1913 Ford Model T Speedster', 120,2021,1);

Nào, bây giờ hãy sử dụng lệnh Select để xem trong bảng có gì nhé.

SELECT * FROM Sales;

Tạo thêm một table SalesChanges để lưu trữ thông tin cập nhật của bảng sales.

DROP TABLE IF EXISTS SalesChanges; CREATE TABLE SalesChanges (     id INT AUTO_INCREMENT PRIMARY KEY,     salesId INT,     beforeQuantity INT,     afterQuantity INT,     changedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP );

- Tạo trigger:

Tiếp theo hãy tạo trigger xử lý tình huống sau: Nếu giá trị của quantity cập nhật khác với gí trị cũ thì hãy lưu vết vào table SalesChanges để sau này biết lịch sử thay đổi của nó.

DELIMITER $$ CREATE TRIGGER after_sales_update AFTER UPDATE ON sales FOR EACH ROW BEGIN     IF OLD.quantity <> new.quantity THEN         INSERT INTO SalesChanges(salesId,beforeQuantity, afterQuantity)         VALUES(old.id, old.quantity, new.quantity);     END IF; END$$ DELIMITER ;

Về các hàm mình sử dụng ở trên khá quen thuộc với các bạn rồi nên mình không giải thích gì thêm.

- Thử trigger:

Bây giờ hãy thử update dữ liệu xem trigger có hoạt động không nhé.

Bạn hãy để ý dòng dữ liệu ID = 1 có giá trị của quantity là 140, bây giờ mình sẽ cập nhật nó thành 350.

Nếu như trigger hoạt động đúng thì nó sẽ lưu vết vào bảng SalesChanges.

UPDATE Sales SET quantity = 350 WHERE id = 1;

Hãy chạy câu SQL trên nhé, sau đó chạy lệnh dưới đây để xem trong bảng SalesChanges có gì không.

SELECT * FROM SalesChanges;

Như vậy là nó đã hoạt động. Bây giờ hãy thử cập nhật tất của quantity tăng lên 10%.

UPDATE Sales SET quantity = CAST(quantity * 1.1 AS UNSIGNED);

Xem lại bảng SalesChanges:

SELECT * FROM SalesChanges;

Vùng khoanh đỏ chính là ba dòng dữ liệu đã được thêm vào bởi vì đã update tăng 10% quantity cho 3 rows trong bảng Sales.

H. Before Delete Trigger trong MySQL

Đây là loại trigger sẽ xảy ra trước khi hệ thống thực hiện lệnh DELETE xóa dữ liệu.

A. Giới thiệu Before Delete Trigger

Before Delete Trigger trong MySQL sẽ được gọi tự động thì bạn chạy câu lệnh delete, và nó sẽ thực hiện trước khi MySQL thực hiên câu lệnh delete đó.

Dưới đây là cú pháp cơ bản của nó:

CREATE TRIGGER trigger_name     BEFORE DELETE     ON table_name FOR EACH ROW trigger_body

Trong đó trigger_name là tên của trigger mà bạn muốn đặt, table_name là tên của table mà bạn gắn trigger này.

Riêng trigger_body chính là nội dung xử lý quan trọng nhất, và nếu có nhiều lệnh thì bạn sẽ đặt trong cặp BEGIN .. END.

DELIMITER $$ CREATE TRIGGER trigger_name     BEFORE DELETE     ON table_name FOR EACH ROW BEGIN     -- statements END$$

Trong trigger này bạn có thể truy cập đến các giá trị OLD nhưng không thể thay đổi chúng. Riêng NEW thì không có bởi vì ta đang thực hiện câu lệnh Delete.

+ Ví dụ Before Delete Trigger 

Hãy thực hành một ví dụ về Before Delete Trigger bạn sẽ thấy dễ hiểu hơn.

- Tạo table:

Đầu tiên hãy tạo table Salaries lưu trữ thông tin lương của các nhân viên.

DROP TABLE IF EXISTS Salaries; CREATE TABLE Salaries (     employeeNumber INT PRIMARY KEY,     validFrom DATE NOT NULL,     amount DEC(12 , 2 ) NOT NULL DEFAULT 0 );

Tiếp theo hãy thêm một vai dòng dữ liệu để qua phần tiếp theo kiểm thử trigger.

INSERT INTO salaries(employeeNumber,validFrom,amount) VALUES     (1002,'2000-01-01',50000),     (1056,'2000-01-01',60000),     (1076,'2000-01-01',70000);

Bây giờ hãy tạo thêm bảng SalaryArchives dùng để lưu trữ lịch sử mức lương của nhân viên đã bị xóa.

DROP TABLE IF EXISTS SalaryArchives;    CREATE TABLE SalaryArchives (     id INT PRIMARY KEY AUTO_INCREMENT,     employeeNumber INT PRIMARY KEY,     validFrom DATE NOT NULL,     amount DEC(12 , 2 ) NOT NULL DEFAULT 0,     deletedAt TIMESTAMP DEFAULT NOW() );

- Tạo trigger:

Chương trình Trigger dưới đây sẽ thêm một dòng cần xóa vào bảng SalaryArchives.

DELIMITER $$ CREATE TRIGGER before_salaries_delete BEFORE DELETE ON salaries FOR EACH ROW BEGIN     INSERT INTO SalaryArchives(employeeNumber,validFrom,amount)     VALUES(OLD.employeeNumber,OLD.validFrom,OLD.amount); END$$    DELIMITER ;

Tên của trigger là before_salaries_delete, dòng BEFORE DELETE cho thấy đây là loại Before Delete Trigger. Dòng ON salaries khai báo trigger sẽ hoạt động trên table này.

- Kiểm tra trigger:

Ta hãy chạy một câu lệnh delete để xem có dòng nào được tự động thêm trong bảng SalaryArchives không nhé.

DELETE FROM salaries WHERE employeeNumber = 1002;

Hãy chạy câu Select để xem dữ liệu thế nào.

SELECT * FROM SalaryArchives;

Trigger before_salaries_delete đã tự động gọi nên nhân viên có ID là 1002 được thêm vào bảng này.

Bây giò ta hãy xóa hai dòng còn lại bằng cách chạy câu SQL Delete tất cả.

DELETE FROM salaries;

Nếu như trigger hoạt động ổn định thì sẽ xuất hiện thêm 2 dòng nữa trong bảng SalaryArchives. Cùng xem kết quả thế nào nhé.

SELECT * FROM SalaryArchives;

Trigger đã hoạt động thêm hai lần vì lệnh Delete ở trên đã thực hiện xóa 2 lần.

T. After Delete Trigger trong MySQL

Đây là loại trigger xảy ra khi bạn thực hiện lệnh delete, và nó sẽ chạy sau khi hệ thống chạy lênh delete đó thành công.

A. Giới thiệu After Delete Trigger trong MySQL

Về công dụng thì nó giống như Before Delete, điểm khác duy nhất là nó hoạt động sau khi lệnh delete hoàn thành, còn before delete thì nó hoạt động trước khi lệnh delete thục hiện.

Cú pháp như sau:

CREATE TRIGGER trigger_name     AFTER DELETE     ON table_name FOR EACH ROW trigger_body;

Trong đó trigger_name là tên của trigger, table_name chính là tên của table mà bạn muốn trigger này gắn vào.

Nếu có nhiều dòng SQL bên trong phần trigger_body thì hãy đặt nó trong cặp BEGIN .. END.

DELIMITER $$ CREATE TRIGGER trigger_name     AFTER DELETE     ON table_name FOR EACH ROW BEGIN     -- statements END$$    DELIMITER ;

Tương tự như Before Trigger, bạn có thể truy xuất đến giá trị của OLD nhưng không thể thay đổi chúng. Riêng NEW thì không có bởi vi đây là lệnh delete.

+ Vi dụ After Delete Trigger trong MySQL

Trước tiên hãy tạo một vài table để thực hành.

- Tạo table:

Hãy tạo một table Salaries có cấu trúc đơn giản như sau:

DROP TABLE IF EXISTS Salaries; CREATE TABLE Salaries (     employeeNumber INT PRIMARY KEY,     salary DECIMAL(10,2) NOT NULL DEFAULT 0 );

Nó chỉ có 2 field như sau:

• employeeNumber là mã số nhân viên
• salary là mức lương của nhân viên

Hãy thêm một vài dòng dữ liệu.

INSERT INTO Salaries(employeeNumber,salary) VALUES     (1002,5000),     (1056,7000),     (1076,8000);

Bây giờ tạo thêm một table SalaryBudgets dùng để lưu trữ tổng số lương của tất cả nhân viên.

INSERT INTO SalaryBudgets(total) SELECT SUM(salary) FROM Salaries;

Đúng ra ta sẽ tạo After Insert Trigger để bắt sự kiện khi thêm nhân viên thì update tổng số mức lương trong bảng SalaryBudgets, hoặc After Update Trigger cho trường hợp bạn chỉnh mức lương. Vì để bài toán đơn giản hơn nên mình sẽ thêm thủ công như sau:

INSERT INTO SalaryBudgets(total) SELECT SUM(salary) FROM Salaries;

Thử truy vấn xem tổng số lương là bao nhiêu nhé. Nếu đúng ra thì sẽ là 5000 + 7000 + 8000 = 20000.

SELECT * FROM SalaryBudgets;

Quá chuẩn!

- Tạo trigger: 

Viết trigger khi xóa một nhân viên trong bảng Salaries thì update tổng số mức lương ở bảng SalaryBudgets.

CREATE TRIGGER after_salaries_delete AFTER DELETE ON Salaries FOR EACH ROW UPDATE SalaryBudgets SET total = total - old.salary;

- Chạy thử trigger:

Trước tiên hãy thử xóa một nhân viên có id = 1002 xem thế nào nhé.

DELETE FROM Salaries WHERE employeeNumber = 1002;

Hãy xem tổng số mức lương ở bảng SalaryBudgets có được update không nhé.

SELECT * FROM SalaryBudgets;

Kết quả 15000 là đúng rồi, tại vì nhân viên đã xóa có mức lương là 5000.

Bây giờ hãy thử xóa tất cả 2 nhân viên còn lại xem tổng số có update về 0 không nhé.

DELETE FROM Salaries;

Xem kết quả thế nào.

SELECT * FROM SalaryBudgets;

 

6. Các hàm nâng cao khác trong Mysql

A. Hàm Lấy mẫu tin ngẫu nhiên Random Records

+ Lấy record ngẫu nhiên bằng ORDER BY RAND()

MySQL không tích hợp bất kỳ câu lệnh nào để chọn các record ngẫu nhiên từ một bảng. Để thực hiện điều này ta bạn sử dụng hàm RAND ().

Truy vấn sau đây chọn một hàng ngẫu nhiên trong một table bất kì:

SELECT * FROM table_name ORDER BY RAND() LIMIT 1;

Giải thích:

• Hàm RAND() sẽ sinh ra giá trị ngẫu nhiên cho từng record trả về.
• Lệnh ORDER BY sẽ sắp xếp các records theo giá trị mà hàn RAND sinh ra
• LIMIT là giới hạn số record mà bạn sẽ lấy.

Nếu bạn muốn chọn N bản ghi ngẫu nhiên từ bảng cơ sở dữ liệu, bạn cần thay đổi mệnh đề LIMIT như sau:

SELECT * FROM table_name ORDER BY RAND() LIMIT N;

Giả sử ta có table như sau:

Và câu SQL dưới đây sẽ lấy record ngẫu nhiên từ bảng này.

SELECT     customerNumber,     customerName FROM     customers ORDER BY RAND() LIMIT 5;

Lưu ý rằng khi bạn chạy lệnh này thì mỗi lần chạy kêt quả sẽ khác nhau, bởi giá trị mà hàm RAND sinh ra là ngẫu nhiên.

Kỹ thuật này hoạt động rất tốt với table nhỏ. Tuy nhiên, nó sẽ chậm đối với table lớn vì MySQL phải sắp xếp toàn bộ dữ liệu để chọn ngẫu nhiên.

Tốc độ của truy vấn cũng phụ thuộc vào số hàng trong bảng. Bảng càng có nhiều hàng, thì càng mất nhiều thời gian để tạo số ngẫu nhiên cho mỗi hàng.

• => Nói chung là không tối ưu, không nên sử dụng

+ Lấy record ngẫu nhiên bằng lệnh JOIN

Kỹ thuật này yêu cầu bảng phải có khóa chính tăng tự động (auto increment) và không có khoảng trống trong dãy thứ tự tăng dần. Tức là tăng dần 1, 2 ,3 thì ok, còn bị khuyết một giá trị như 1, 2, 5 ,6 thì không được.

Truy vấn sau tạo một số ngẫu nhiên dựa trên cột khóa chính:

SELECT    ROUND(RAND() * ( SELECT MAX(id) FROM  table_name)) AS id;

Chúng ta có thể JOIN table với kết quả này để lấy record ngẫu nhiên.

SELECT t.* FROM table_name AS t INNER JOIN     (SELECT ROUND(        RAND() *       (SELECT MAX(id) FROM table_NAME )) AS id      ) AS x WHERE     t.id >= x.id LIMIT 1;

Sử dụng kỹ thuật này bạn phải thực hiện truy vấn nhiều lần nếu muốn lấy nhiều record ngẫu nhiên, bởi vì câu lệnh sub query chỉ trả về một giá trị random mà thôi.

Truy vấn sau đây trả về một khách hàng ngẫu nhiên từ bảng customer.

SELECT     t.customerNumber, t.customerName FROM     customers AS t         JOIN     (SELECT         ROUND(RAND() * (SELECT                     MAX(customerNumber)                 FROM                     customers)) AS customerNumber     ) AS x WHERE     t.customerNumber >= x.customerNumber LIMIT 1;

+ Lấy record ngẫu nhiên bằng JOIN và tạo biến

Trong trường hợp bảng có cột khóa chính id với các giá trị nằm trong phạm vi 1..N, bạn có thể sử dụng kỹ thuật sau:

• Đầu tiên, chọn các số ngẫu nhiên trong phạm vi 1..N.
• Thứ hai, chọn các bản ghi dựa trên các số ngẫu nhiên.
• Câu lệnh sau giúp bạn thực hiện điều này:

SELECT     table. * FROM     (SELECT         ROUND(RAND() * (SELECT                     MAX(id)                 FROM                     table)) random_num,             @num:=@num + 1     FROM         (SELECT @num:=0) AS a, table     LIMIT N) AS b,     table AS t WHERE     b.random_num = t.id;

B. Hàm lấy ngày giờ

+ Lấy ngày hiện tại bằng build-in function

Thỉnh thoảng bạn muốn lấy ngày giờ hiện tại trong MySQL để phục vụ cho mội tính năng nào đó của phần mềm. Ví dụ bạn làm web tin tức là có tính năng hẹn giờ đăng bài, bạn sẽ truy vấn những bài nào có ngày đăng bé hơn hoặc bằng ngày hiện tai.

Lúc này một là bạn truyền giá trị ngày hiện tại vào, hai là dùng các hàm lấy ngày giờ trong MySQL.

- Hàm CURDATE():

Hàm CURDATE() sẽ trả vè ngày hiện tại trong hệ thống server. Hãy xem ví dụ dưới đây.

mysql> SELECT CURDATE() today; +------------+ | today      | +------------+ | 2017-07-08 | +------------+ 1 row in set (0.00 sec)

Hoặc bạn có thể sử dụng hàm now() như cách dưới đây.

- Kết hợp DATE() và NOW():

Bạn sẽ dùng hàm now() để lấy ngày giờ hiện tại định dạng milisecond kiểu số nguyên, sau đó đặt nó trong hàm date() để trả về ngày hiện tại.

mysql> SELECT DATE(NOW()) today; +------------+ | today      | +------------+ | 2017-07-08 | +------------+ 1 row in set (0.00 sec)

+ Tự tạo Store Procedure lấy ngày hiện tại

Thực tế thì cách này ít người sử dụng bởi nó tốn quá nhiều thời gian. Tuy nhiên nếu bạn thích vọc vạch MySQL thì hãy thử xem sao nhé.

Mình sẽ tạo một store procedure function có tên là today.

DELIMITER $$ CREATE FUNCTION today() RETURNS DATE BEGIN RETURN CURDATE(); END$$ DELIMITER ;

Bây giờ bạn có thể thực hiện truy vấn và sử dụng hàm đó một cách bình thường.

mysql> SELECT today(); +------------+ | today()    | +------------+ | 2017-07-08 | +------------+ 1 row in set (0.00 sec)

Lấy ngày mai thì sao, bạn chỉ việc cộng nó lên 1 day là được.

mysql> SELECT today() + interval 1 day as Tomorrow; +------------+ | Tomorrow   | +------------+ | 2017-07-09 | +------------+ 1 row in set (0.00 sec)

Tương tự, để lấy ngày hôm qua thì ta trừ nó đi 1 day.

mysql> SELECT today() - interval 1 day Yesterday; +------------+ | Yesterday  | +------------+ | 2017-07-07 | +------------+ 1 row in set (0.00 sec)

C. Lấy mấu tin thứ n

Đầu tiên bạn cần viết câu truy vấn lấy danh sách kết quả trả về. Tuy thuộc vào nhu cầu mà bạn thiết lập sắp xếp tăng hay giảm dần.

Tiếp theo sử dụng lệnh LIMIT trong MySQL để lấy thứ tự của record mong muốn.

SELECT 
    *
FROM
    table_name
ORDER BY column_name DESC
LIMIT n - 1, 1;

Nếu bạn muốn lấy record thứ 5 thì n = 5.

D. fix lỗi missing index for constraint trong MySQL

Bạn đang tạo khóa ngoại nhưng gặp lỗi "missing index for constraint" trong MySQL? Vậy thì hãy tham khảo bài hướng dẫn này nhé, mình sẽ giúp bạn fix lỗi đó.

Nếu dịch ra tiếng Anh thì nó rất rõ nghĩa, đây là lỗi bị thiếu chỉ mục cho ràng buộc khóa ngoại. Theo như quy tắc thì khá ngoại của bảng con phải trỏ đến khóa chính của bảng cha, nhưng bạn đã không tạo khóa chính cho bảng cha.

Vì vậy giải pháp là tạo chỉ mục khóa chính trước khi tạo khóa ngoai nhé.

- Tạo bảng con med_pharmacy

DROP TABLE IF EXISTS `med_pharmacy`; CREATE TABLE IF NOT EXISTS `med_pharmacy` (   `med_pharmacy_id` int(11) NOT NULL AUTO_INCREMENT,   `med_id` int(11) NOT NULL,   `med_barcode` varchar(45) DEFAULT NULL,   `med_received` date DEFAULT NULL,   `med_expiry` date DEFAULT NULL,   `med_tablet` int(11) DEFAULT NULL,   `med_pill` int(11) DEFAULT NULL,   `clinic_id` varchar(45) DEFAULT NULL,   PRIMARY KEY (`med_pharmacy_id`),   KEY `fk_med_pharmacy_medication1_idx` (`med_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1261 DEFAULT CHARSET=utf8mb4;

- Tạo bảng cha medication

DROP TABLE IF EXISTS `medication`;   CREATE TABLE `medication` (   `med_id` int(11) NOT NULL,   `med_name` varchar(75) NOT NULL,   `med_date_added` date DEFAULT NULL,   `clinic_id` varchar(45) DEFAULT NULL,   `med_type` varchar(15) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

- Bây giờ thêm khóa ngoại bằng lệnh SQL:

ALTER TABLE `med_pharmacy`   ADD CONSTRAINT `fk_med_pharmacy_medication1` FOREIGN KEY (`med_id`) REFERENCES `medication` (`med_id`) ON DELETE CASCADE ON UPDATE CASCADE MySQL

Xuất hiện lỗi:

#1822 - Failed to add the foreign key constaint. 
Missing index for constraint 'fk_med_pharmacy_medication1' 
in the referenced table 'medication'

Nó nói khá rõ ràng, trong bảng medication bạn đã không tạo khóa chính cho med_id.

Vì vậy hãy thêm nó bằng lệnh sau:

ALTER TABLE medication ADD PRIMARY KEY (med_id);

Rồi chạy lại lệnh tạo kháo ngoại ở trên nhé.

ALTER TABLE `med_pharmacy`   ADD CONSTRAINT `fk_med_pharmacy_medication1` FOREIGN KEY (`med_id`) REFERENCES `medication` (`med_id`) ON DELETE CASCADE ON UPDATE CASCADE MySQL

E. Cơ chế lưu trữ kiểu ngày tháng trong mysql

Như bạn biết trong mysql chúng ta có thể lưu định dạng ngày tháng là kiểu date, kiểu datetime. Đây là hai kiểu chính và các bạn rất thường hay sử dụng để tìm kiếm. Nhưng có một sự thật mà ít bạn quan tâm đến đó là nếu như bạn truy vấn với kiểu INT và có index thì tốc độ sẽ rất nhanh, tức là thay vì ta lưu kiểu date hoặc datetime thì ta tạo thêm một field kiểu UNSIGN INT nữa và dùng code PHP để chuyển ngày tháng sang trước khi lưu vào CSDL.

Trong mysql năm bắt đầu ta sẽ tính bắt đầu từ năm 1970 và tới nay là năm 2014, tính ra khoảng 44 năm. Và tổng khoảng thời gian 44 năm này chuyển sang kiểu INT sẽ có tổng cộng 1407612770 giây. Nhưng trong mysql, kiểu UNSIGN INT chỉ lưu trữ tới mức khoảng 4294967295 là hết, như vậy tính ra ta còn có thể lưu thê được ít nhất hơn 60 năm nữa mới max. Tới lúc đó thì công nghệ phát triển rồi nên chắc chắn sẽ ko bị hạn chế như vậy.

+ Ý tưởng thiết kế dữ liệu cho chức năng hẹn giờ: 

Ý tưởng thứ nhất chính là phần phân tích cơ chế lưu trữ trong mysql trên.

Ý tưởng thứ 2 dựa vào ý tưởng thứ nhất ta sẽ lưu trữ một field gọi là news_timer (kiểu UNSIGN INT), field này có giá trị chính là thời gian mà người dùng chọn đăng bài. Như vậy khi hiển thị danh sách tin tức thì ta chỉ cần thêm điều kiện lọc ngày hiện tại phải lơn hoặ hoặc bằng ngày hiển thị trong timer.

Khi làm việc với tin tức ta sẽ có một field gọi là status, giá trị bằng 1 là hiển thị, giá trị bằng 0 là không hiển thị. Vậy gộp lại ta có điều kiện lọc nhu sau:

Select * from news where status = 1 and timer <= $bien_kieu_int_time_hien_tai

Với điều kiện lọc như vậy thì ta thấy cũng tối ưu rồi, vì cả hai điều kiện ta có thể đánh chỉ số index. Nhưng có cách nào tối ưu hơn không? Để trả lời câu hỏi này các bạn chú ý đến phần phân tích thời gian từ 1970 tới nay sẽ có ý tưởng như sau:

Chọn thời gian max chính là giá trị max của kiểu UNSIGN INT, tức là số 4294967295, chọn 4200000000 cho chẵn.

Như vậy ta sẽ bỏ đi field status, còn mỗi field timer thôi. Nếu người dùng chọn ẩn tin thì ta sẽ thiết lập gái trị cho timer bằng 4200000000, còn không thì ta lưu thời gian bình thường. Như vậy khi hiển thị tin chỉ lọc đúng một điều kiện timer duy nhất và đánh chỉ mục index nữa thì tốc độ sẽ nhanh hơn rất nhiều.

 

Về bài trước