MySQL Chức năng: Chuỗi, Số, Do người dùng xác định, Đã lưu trữ

Những gì đang có MySQL Chức năng?

MySQL có thể làm được nhiều việc hơn là chỉ lưu trữ và truy xuất dữ liệu. Nó cũng có thể thực hiện các thao tác trên dữ liệu trước khi truy xuất hoặc lưu trữ nó. Đó là nơi mà MySQL Hàm xuất hiện ở đây. Hàm đơn giản là những đoạn mã thực hiện một thao tác và sau đó trả về kết quả. Một số hàm chấp nhận tham số, trong khi những hàm khác thì không.

Chúng ta hãy xem xét một ví dụ ngắn gọn. Theo mặc định, MySQL Lưu trữ các kiểu dữ liệu ngày tháng ở định dạng “YYYY-MM-DD”. Giả sử chúng ta đã xây dựng một ứng dụng và người dùng muốn ngày tháng được trả về ở định dạng “DD-MM-YYYY”. Chúng ta có thể sử dụng... MySQL Sử dụng hàm tích hợp DATE_FORMAT để thực hiện điều này. DATE_FORMAT là một trong những hàm được sử dụng nhiều nhất trong... MySQLvà chúng ta sẽ xem xét chi tiết hơn về điều đó ở phần sau của bài học này.

Bất kể thuộc loại nào, một hàm số luôn trả về một giá trị duy nhất, có thể chấp nhận không hoặc nhiều tham số bên trong dấu ngoặc đơn, và có thể được sử dụng ở bất cứ nơi nào cho phép sử dụng biểu thức. — trong danh sách SELECT, mệnh đề WHERE hoặc mệnh đề ORDER BY.

Tại sao sử dụng MySQL Chức năng?

Giờ chúng ta đã biết hàm là gì, câu hỏi tiếp theo là tại sao chúng ta lại cần đưa công việc này vào cơ sở dữ liệu.

Như sơ đồ trên cho thấy, một hàm nhận một giá trị đầu vào, áp dụng logic một lần bên trong công cụ cơ sở dữ liệu, và trả về một kết quả duy nhất cho mọi ứng dụng yêu cầu nó.

Các lập trình viên có thể đang nghĩ, “Tại sao phải bận tâm đến điều đó?” MySQL Hàm ư? Hiệu ứng tương tự có thể đạt được bằng ngôn ngữ lập trình hoặc kịch bản.” Đúng là chúng ta có thể đạt được điều đó bằng cách viết một thủ tục trong chương trình ứng dụng.

Quay trở lại ví dụ về ngày tháng, để người dùng nhận được dữ liệu ở định dạng mong muốn, lớp nghiệp vụ sẽ phải tự thực hiện quá trình xử lý cần thiết.

Điều này trở thành vấn đề khi ứng dụng phải tích hợp với các hệ thống khác. Khi chúng tôi sử dụng MySQL Các hàm như DATE_FORMAT, chức năng đó được nhúng vào cơ sở dữ liệu, và bất kỳ ứng dụng nào cần dữ liệu đều nhận được dữ liệu ở định dạng yêu cầu. Điều này Giảm thiểu việc làm lại trong logic nghiệp vụ và giảm sự không nhất quán dữ liệu..

Một lý do khác để xem xét MySQL Chức năng của chúng là giúp giảm lưu lượng mạng trong các ứng dụng máy khách/máy chủ.Lớp nghiệp vụ chỉ cần gọi hàm đã lưu trữ, mà không cần tải các hàng dữ liệu thô qua mạng để xử lý. Trung bình, việc sử dụng các hàm có thể cải thiện đáng kể hiệu suất tổng thể của hệ thống.

các loại MySQL Chức năng

Sau khi đã xác định rõ "cái gì" và "tại sao", chúng ta có thể xem xét ba nhóm chức năng. MySQL Cung cấp: các hàm tích hợp sẵn, các hàm được lưu trữ và các hàm do người dùng định nghĩa.

Chức năng tích hợp sẵn

MySQL đi kèm với một số chức năng tích hợp sẵn — các chức năng đã được triển khai trong... MySQL Máy chủ. Chúng cho phép chúng ta thực hiện nhiều loại thao tác trên dữ liệu và được chia thành các nhóm thường dùng sau đây.

• Hàm chuỗi – thao tác trên kiểu dữ liệu chuỗi
• Các hàm số – thao tác trên các kiểu dữ liệu số
• Hàm ngày – hoạt động trên các kiểu dữ liệu ngày
• Chức năng tổng hợp – hoạt động trên tất cả các loại dữ liệu trên và tạo ra các tập kết quả tóm tắt.
• các chức năng khác – MySQL Ngoài ra, nó còn hỗ trợ các loại hàm tích hợp khác, nhưng chúng ta sẽ giới hạn bài học này vào các nhóm đã nêu ở trên.

Bây giờ chúng ta hãy xem xét chi tiết từng nhóm đã đề cập ở trên. Chúng ta sẽ giải thích các chức năng được sử dụng nhiều nhất bằng cách sử dụng cơ sở dữ liệu mẫu “Myflixdb” của chúng ta.

Hàm chuỗi

Các hàm xử lý chuỗi hoạt động trên các giá trị văn bản. Trong bảng phim của chúng ta, tiêu đề được lưu trữ bằng cả chữ thường và chữ hoa. Giả sử chúng ta muốn một truy vấn trả về các tiêu đề ở dạng chữ hoa. Hàm “UCASE” nhận một chuỗi làm tham số và chuyển đổi mọi chữ cái thành chữ hoa, như đoạn mã bên dưới minh họa.

SELECT `movie_id`, `title`, UCASE(`title`) AS `upper_case_title` FROM `movies`;

tại ĐÂY

• UCASE(`title`) Đây là hàm tích hợp sẵn nhận tiêu đề làm tham số và trả về tiêu đề đó dưới dạng chữ in hoa.
• AS `upper_case_title` Gán một bí danh cho cột được tính toán, nhờ đó tập kết quả mang một tiêu đề dễ đọc thay vì biểu thức thô.

Thực thi đoạn script trên trong MySQL Việc so sánh kết quả với cơ sở dữ liệu Myflixdb được thể hiện bên dưới.

Bên cạnh UCASE, còn có hai tổ chức khác đáng được ghi nhớ: LCASE chuyển đổi một chuỗi thành chữ thường, và CONCAT Nối hai hoặc nhiều chuỗi thành một. Để xem danh sách đầy đủ, hãy tham khảo... MySQL tham chiếu hàm chuỗi.

Các hàm số

Như đã đề cập trước đó, các hàm số học hoạt động trên các kiểu dữ liệu số. Chúng ta cũng có thể thực hiện các phép tính toán học trực tiếp trên dữ liệu số trong các câu lệnh SQL của mình.

Toán tử số học

MySQL Hỗ trợ các toán tử số học sau, có thể được sử dụng để thực hiện các phép tính trong câu lệnh SQL.

Dưới đây là ví dụ về từng toán tử.

Phép chia số nguyên (DIV) — Hàm DIV loại bỏ phần thập phân và chỉ trả về số nguyên.

SELECT 23 DIV 6;

Thực thi đoạn mã trên sẽ cho chúng ta kết quả như sau: 3.

Toán tử chia (/) — Không giống như phép chia (DIV), toán tử chia giữ nguyên phần thập phân của kết quả.

SELECT 23 / 6;

Thực thi đoạn mã trên sẽ cho chúng ta kết quả như sau: 3.8333.

Subtractoán tử tion (-)

SELECT 23 - 6;

Thực thi đoạn mã trên sẽ cho chúng ta kết quả như sau: 17.

Toán tử cộng (+)

SELECT 23 + 6;

Thực thi đoạn mã trên sẽ cho chúng ta kết quả như sau: 29.

Toán tử nhân (*)

SELECT 23 * 6 AS `multiplication_result`;

Kết quả:

Toán tử modulo (% hoặc MOD)

Toán tử modulo chia N cho M và cho ta phần dư. Hãy xem ví dụ về toán tử modulo, sử dụng cùng các giá trị như trong các ví dụ trước.

SELECT 23 % 6;
-- OR, equivalently:
SELECT 23 MOD 6;

Việc thực thi một trong hai đoạn mã sẽ cho chúng ta 5.

Bây giờ chúng ta hãy xem xét một số hàm số phổ biến trong MySQL.

SÀN NHÀ – Hàm này loại bỏ các chữ số thập phân khỏi một số và làm tròn xuống số nguyên gần nhất. Đoạn mã bên dưới minh họa cách sử dụng hàm này.

SELECT FLOOR(23 / 6) AS `floor_result`;

Kết quả:

ROUND – Hàm này làm tròn một số đến số nguyên gần nhất. Vì 23 / 6 có giá trị là 3.8333, nên hàm ROUND trả về 4 trong khi hàm FLOOR trả về 3 — hai hàm này không thể thay thế cho nhau.

SELECT ROUND(23 / 6) AS `round_result`;

Kết quả:

RAND – Hàm này tạo ra một số ngẫu nhiên. Giá trị của số này thay đổi mỗi khi hàm được gọi. Đoạn mã bên dưới minh họa cách sử dụng hàm này.

SELECT RAND() AS `random_result`;

Hàm ngày

Các hàm xử lý ngày tháng hoạt động trên các kiểu dữ liệu ngày và ngày giờ. Hàm DATE_FORMAT giải quyết vấn đề "YYYY-MM-DD so với DD-MM-YYYY" được mô tả trong phần giới thiệu.

ĐỊNH DẠNG NGÀY THÁNG Đoạn mã này nhận hai tham số: giá trị ngày tháng cần định dạng và một chuỗi định dạng được tạo từ các ký tự giữ chỗ. Đoạn mã bên dưới sẽ trả về mỗi ngày phát hành theo định dạng ngày-tháng-năm mà người dùng yêu cầu.

SELECT `title`, DATE_FORMAT(`date_released`, '%d-%m-%Y') AS `formatted_date`
FROM `movies`;

Các định dạng giữ chỗ được sử dụng thường xuyên nhất được liệt kê bên dưới.

Ba chức năng liên quan đến ngày tháng khác xuất hiện thường xuyên trong công việc hàng ngày:

• HIỆN TẠI () Trả về ngày hiện tại theo định dạng YYYY-MM-DD.
• HIỆN NAY() Trả về ngày hiện tại và thời gian.
• DATEDIFF(d1, d2) Hàm này trả về số ngày giữa hai ngày — cơ sở của bất kỳ báo cáo tiền thuê quá hạn nào.

Để xem danh sách đầy đủ, vui lòng xem phần sau. MySQL Tham chiếu chức năng ngày và giờ.

Chức năng được lưu trữ

Các hàm tích hợp sẵn bao gồm các trường hợp thông thường. Khi một quy tắc nghiệp vụ cụ thể hơn, chúng ta sẽ tự viết quy tắc đó — và đó là mục đích của hàm lưu trữ.

Các hàm lưu trữ hoạt động giống như các hàm tích hợp sẵn, ngoại trừ việc bạn tự định nghĩa chúng. Sau khi được tạo, một hàm lưu trữ có thể được sử dụng trong các câu lệnh SQL giống hệt như bất kỳ hàm nào khác. Cú pháp cơ bản được hiển thị bên dưới.

CREATE FUNCTION sf_name ([parameter(s)])
RETURNS data_type
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
    -- procedural statements
END

tại ĐÂY

• “CREATE FUNCTION sf_name ([parameter(s)])” là bắt buộc và cho biết MySQL Máy chủ sẽ tạo một hàm có tên `sf_name` với các tham số tùy chọn được định nghĩa bên trong dấu ngoặc đơn.
• “TRẢ VỀ kiểu dữ liệu” Đây là tham số bắt buộc và chỉ định kiểu dữ liệu mà hàm trả về.
• “XÁC ĐỊNH” Khai báo rằng hàm sẽ trả về cùng một giá trị bất cứ khi nào các đối số được cung cấp là giống nhau. “KHÔNG MANG TÍNH XÁC ĐỊNH” Điều ngược lại mới đúng.
• “BẮT ĐẦU … KẾT THÚC” Bao bọc đoạn mã thủ tục mà hàm thực thi.

Giả sử chúng ta muốn biết những bộ phim đã thuê nào đã quá hạn trả. Chúng ta có thể tạo một hàm lưu trữ nhận ngày trả phim làm tham số và so sánh nó với ngày hiện tại trên máy chủ. Nếu ngày hiện tại lớn hơn ngày trả phim, phim đã quá hạn và chúng ta trả về “Có”; ngược lại, chúng ta trả về “Không”.

DELIMITER |
CREATE FUNCTION sf_past_movie_return_date (return_date DATE)
RETURNS VARCHAR(3)
NOT DETERMINISTIC
BEGIN
    DECLARE sf_value VARCHAR(3);
    IF CURDATE() > return_date THEN
        SET sf_value = 'Yes';
    ELSEIF CURDATE() <= return_date THEN
        SET sf_value = 'No';
    END IF;
    RETURN sf_value;
END|
DELIMITER ;

⚠️ Cảnh báo — không được gắn nhãn hàm này là DETERMINISTIC. Hàm này gọi CURDATE(), vì vậy cùng một đối số có thể trả về “Không” hôm nay và “Có” ngày mai. Việc khai báo một hàm phụ thuộc thời gian là DETERMINISTIC sẽ gây hiểu lầm cho trình tối ưu hóa và không an toàn cho việc sao chép dựa trên câu lệnh. Hãy sử dụng KHÔNG XÁC ĐỊNH bất cứ khi nào phần thân hàm gọi CURDATE(), NOW() hoặc RAND().

Việc thực thi đoạn mã trên sẽ tạo ra hàm được lưu trữ `sf_past_movie_return_date`. Bây giờ chúng ta hãy kiểm tra nó.

SELECT `movie_id`, `membership_number`, `return_date`, CURDATE(),
       sf_past_movie_return_date(`return_date`) AS `is_overdue`
FROM `movierentals`;

Thực thi đoạn script trên trong MySQL Việc chạy lệnh Workbench trên cơ sở dữ liệu myflixdb cho chúng ta các kết quả sau.

Hãy chú ý đến hai hàng có giá trị NULL. Khi `return_date` là NULL, cả hai phép so sánh đều cho kết quả là NULL thay vì TRUE hoặc FALSE, do đó không có nhánh IF nào được thực thi và hàm trả về NULL — kết quả mong đợi, vì một bộ phim chưa được trả lại sẽ không có ngày trả lại để so sánh.

Các chức năng do người dùng xác định

Khi chỉ dùng SQL thôi vẫn chưa đủ nhanh, MySQL Điều này cho phép một lựa chọn thứ ba. Các hàm do người dùng định nghĩa (UDF) được viết bằng một ngôn ngữ biên dịch như... C or C++Chúng được xây dựng thành một thư viện dùng chung và được đăng ký với máy chủ. Sau khi được thêm vào, chúng được gọi giống như bất kỳ hàm nào khác. Vì UDF chạy như mã gốc bên trong tiến trình máy chủ, nên nó phù hợp với các phép tính phức tạp — nhưng một lỗi trong UDF có thể làm sập máy chủ, vì vậy UDF được sử dụng ít hơn nhiều so với các hàm lưu trữ.

Hàm tích hợp sẵn, hàm lưu trữ hay hàm do người dùng định nghĩa: Nên sử dụng loại nào?

Cả ba nhóm hàm này đều trả về một giá trị duy nhất và có thể được gọi từ bất kỳ câu lệnh SQL nào, nhưng chúng khác nhau về người viết, nơi chúng chạy và mức độ rủi ro mà chúng mang lại. Bảng dưới đây tóm tắt những khác biệt đó.

Theo nguyên tắc chung, hãy bắt đầu với một hàm tích hợp sẵn. Nếu không có hàm nào phù hợp, hãy viết một hàm lưu trữ để quy tắc được lưu trữ ở một nơi duy nhất. Chỉ sử dụng hàm do người dùng định nghĩa (UDF) khi việc sử dụng hàm lưu trữ quá chậm một cách rõ rệt.

Câu Hỏi Thường Gặp