Cách chọn bản ghi có giá trị null trong mysql

Cơ sở dữ liệu của chúng tôi có một bảng tên là

expression IS NULL
7 với dữ liệu trong bốn cột.
expression IS NULL
8,
expression IS NULL
9,
SELECT *
FROM contacts
WHERE last_name IS NULL;
0 và
SELECT *
FROM contacts
WHERE last_name IS NULL;
1

idfirst_namemiddle_namelast_name1JohnCarlJackson2TomNULLThomson3LisaAliceNULL4AnneNULLSmith

Hãy tìm id, tên và họ của những đứa trẻ không có tên đệm (NULL trong cột

SELECT *
FROM contacts
WHERE last_name IS NULL;
0). Chúng tôi sử dụng toán tử IS NULL cho việc này

Dung dịch

SELECT id,
  first_name,
  last_name
FROM children
WHERE middle_name IS NULL;

Đây là kết quả

idfirst_namelast_name2TomThomson4AnneSmith

Điều này chỉ trả về các bản ghi có NULL trong

SELECT *
FROM contacts
WHERE last_name IS NULL;
0, vì vậy Tom và Anne có mặt, nhưng không có John và Lisa, những người có tên đệm

Thảo luận

Sử dụng toán tử

SELECT *
FROM contacts
WHERE last_name IS NULL;
4 trong điều kiện với WHERE để tìm các bản ghi có NULL trong một cột. Tất nhiên, bạn cũng có thể sử dụng bất kỳ biểu thức nào thay cho tên cột và kiểm tra xem nó có trả về NULL không. Không cần gì khác ngoài tên của một cột và toán tử IS NULL (trong ví dụ của chúng tôi,
SELECT *
FROM contacts
WHERE last_name IS NULL;
5). Đặt điều kiện này vào mệnh đề WHERE để lọc các hàng (trong ví dụ của chúng tôi là
expression IS NULL
70). Nếu điều kiện là đúng, cột lưu giá trị NULL và hàng này được trả về. Ở trên, truy vấn chỉ trả về hai bản ghi có con Tom và Anne, không có tên đệm, vì vậy cột
SELECT *
FROM contacts
WHERE last_name IS NULL;
0 lưu trữ NULL

Điều kiện MySQL IS NULL được sử dụng để kiểm tra giá trị NULL trong câu lệnh SELECT, INSERT, UPDATE hoặc DELETE

cú pháp

Cú pháp của Điều kiện IS NULL trong MySQL là

expression IS NULL

Tham số hoặc Đối số

biểu thứcGiá trị để kiểm tra xem nó có phải là giá trị NULL không

Ghi chú

  • Nếu biểu thức là một giá trị NULL, điều kiện đánh giá là TRUE
  • Nếu biểu thức không phải là giá trị NULL, điều kiện ước tính là FALSE

Ví dụ - Với câu lệnh SELECT

Hãy xem một ví dụ về cách sử dụng MySQL IS NULL trong câu lệnh SELECT

SELECT *
FROM contacts
WHERE last_name IS NULL;

Ví dụ MySQL IS NULL này sẽ xóa tất cả các bản ghi khỏi bảng danh bạ nơi last_name chứa giá trị NULL

Giá trị NULL trong ngôn ngữ lập trình thường là một khái niệm khó hiểu và có thể khá khó sử dụng. Ở đây trong hướng dẫn này, chúng ta sẽ khám phá các cách sử dụng, mẹo và thủ thuật khác nhau khi xử lý các giá trị NULL trong truy vấn MySQL

Một cột trong bảng có thể chứa giá trị NULL vì cột được định nghĩa là cho phép NULL, điều này đã được giải thích cặn kẽ trong hướng dẫn Nullability Constraint. Các giá trị NULL có thể trở nên phổ biến vì có những tình huống trong đó một cột cần chứa NULL khi việc gán giá trị mặc định là không thực tế. Trong các tình huống khác, người thiết kế bảng có thể không đủ chu đáo để tạo cột NOT NULL. Do đó, xử lý các giá trị NULL đã trở thành công việc hàng ngày đối với bất kỳ nhà phát triển SQL nào.

Để hiểu giá trị NULL là gì, hãy xem xét tình huống này - khi chèn một sản phẩm mới vào bảng Sản phẩm nơi cột Mô tả được xác định là NULL, không cần cung cấp giá trị cho cột Mô tả. Vì vậy, giá trị NULL có thể được xem là giá trị không xác định bị thiếu. Nó không phải là một chuỗi rỗng và nó cũng không phải là số không

1. So sánh với NULL

When making comparison with NULLs by using comparison or arithmetic operators +, -, <, >, =, <>, the query returns NULL instead of a Boolean TRUE or FALSE. This includes comparing NULL with itself.

One exception to the rule above is operator <=> for "NULL-safe equal to" comparison, it returns TRUE when comparing to a NULL value.

Ví dụ sau đây cho thấy khi tìm kiếm một cột cho phép NULL, sử dụng biểu thức expr = NULL không trả về hàng nào, vì expr = NULL không bao giờ là TRUE

Hai truy vấn sau so sánh NULL với chính nó và nó trả về NULL


select null = null;

select null <> null;

To look for a NULL value, you must use the IS NULL keyword or NULL-safe operator <=>. The following two queries show how to find the NULL ReportsTo record in Employees table.

Tập hợp kết quả truy vấn - trả về 1 hàng

Cách chọn bản ghi có giá trị null trong mysql

Trong MySQL, giá trị mặc định cho true từ thao tác Boolean là 1 và giá trị mặc định cho false từ thao tác Boolean là 0

Sử dụng so sánh an toàn NULL

<=> an toàn NULL bằng. Tài liệu MySQL nói rằng toán tử <=> thực hiện so sánh đẳng thức giống như toán tử =, nhưng trả về 1 thay vì NULL nếu cả hai toán hạng là NULL và 0 thay vì NULL nếu một toán hạng là NULL

Tập kết quả truy vấn. 1 bản ghi được trả lại

Cách chọn bản ghi có giá trị null trong mysql

Truy vấn sau đây sử dụng phép so sánh an toàn NULL để có được nhân viên hàng đầu (Phó chủ tịch, Bán hàng)

Tập hợp kết quả truy vấn - trả về 1 hàng

Cách chọn bản ghi có giá trị null trong mysql

Đây là truy vấn bằng cách sử dụng toán tử bằng (=). kết quả là NULL

Tập hợp kết quả truy vấn - 0 hàng được trả về

Cách chọn bản ghi có giá trị null trong mysql

2. Chọn dữ liệu có giá trị NULL

2. 1 Hàm COALESCE trả về giá trị không NULL đầu tiên trong danh sách được phân tách bằng dấu phẩy

Tập hợp kết quả truy vấn - trả về 1 hàng

Cách chọn bản ghi có giá trị null trong mysql

Tập hợp kết quả truy vấn - trả về 0 hàng

Cách chọn bản ghi có giá trị null trong mysql

2. 2 Hàm ISNULL kiểm tra xem một biểu thức có phải là NULL hay không

Truy vấn này truy xuất nhân viên không báo cáo cho bất kỳ ai vì cột ReportsTo chứa NULL cho nhân viên đó

Tập hợp kết quả truy vấn - trả về 1 hàng

Cách chọn bản ghi có giá trị null trong mysql

2. 3 Sử dụng chức năng luồng điều khiển để làm việc với các giá trị NULL

3 truy vấn sau đây sử dụng các chức năng luồng điều khiển để tìm ra người mà nhân viên báo cáo. Nếu cột ReportsTo là null, nhân viên không báo cáo cho bất kỳ ai

Tập hợp kết quả truy vấn - 9 hàng được trả về

Cách chọn bản ghi có giá trị null trong mysql

2. 4 Hàm NULLIF()

MySQL NULLIF() trả về NULL khi biểu thức đầu tiên bằng biểu thức thứ hai, nếu không, nó trả về biểu thức đầu tiên

NULLIF(expr1,expr2) trả về NULL nếu expr1 = expr2 là true, ngược lại trả về expr1. Điều này tương đương với việc sử dụng câu lệnh case CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

Truy vấn sau đây kết hợp NULLIF với câu lệnh tình huống

SELECT t1.FirstName, t1.LastName,
case when NULLIF(t1.ReportsTo,NULL) is null then 'Does not report to anyone'
else CONCAT(t2.FirstName, ' ', t2.LastName)
end as ReportsTo
FROM employees as t1
left join employees as t2 on t1.ReportsTo=t2.EmployeeID;

Tập hợp kết quả truy vấn - 9 hàng được trả về

Cách chọn bản ghi có giá trị null trong mysql

3. Giá trị NULL và mệnh đề ORDER BY

Khi sử dụng một cột chứa các giá trị NULL trong mệnh đề ORDER BY, các giá trị NULL được hiển thị đầu tiên nếu bạn ORDER BY theo thứ tự tăng dần, trong khi các giá trị NULL được hiển thị cuối cùng nếu bạn ORDER BY theo thứ tự giảm dần

Truy vấn này liệt kê Phó Chủ tịch Andrew Fuller là bản ghi đầu tiên vì ReportsTo của nó chứa NULL và ORDER BY theo thứ tự tăng dần (không cần chỉ định từ khóa ASC vì sắp xếp mặc định giả định thứ tự tăng dần)

select FirstName, LastName, Title from Employees
order by ReportsTo;

Tập hợp kết quả truy vấn - 9 hàng được trả về

Cách chọn bản ghi có giá trị null trong mysql

Truy vấn này liệt kê Phó Tổng thống Andrew Fuller là bản ghi cuối cùng vì DESC được chỉ định trong mệnh đề ORDER BY

select FirstName, LastName, Title from Employees
order by ReportsTo DESC;

Tập hợp kết quả truy vấn - 9 hàng được trả về

Cách chọn bản ghi có giá trị null trong mysql

4. Sử dụng các giá trị NULL với các hàm tổng hợp (tóm tắt)

4. 1 Sự khác nhau giữa COUNT(*), COUNT(expr) và COUNT(distinct expr)

COUNT(*) trả về số lượng hàng được truy xuất, cho dù chúng có chứa giá trị NULL hay không

select count(*) from employees;

Tập hợp kết quả truy vấn - trả về 1 hàng

Cách chọn bản ghi có giá trị null trong mysql

COUNT(expr) chỉ trả về số lượng các giá trị không phải NULL vì các giá trị NULL bị bỏ qua

Truy vấn bên dưới trả về 8 chứ không phải 9 vì Phó Tổng thống Andrew Fuller không báo cáo cho bất kỳ ai nên hồ sơ của ông không được tính

________số 8

Tập hợp kết quả truy vấn - trả về 1 hàng

Cách chọn bản ghi có giá trị null trong mysql

Khi trích xuất các bản ghi riêng biệt, các giá trị NULL được bao gồm. Khi đếm số bản ghi riêng biệt, giá trị NULL bị bỏ qua

Truy vấn sau trả về 3 bản ghi, bao gồm giá trị NULL

select distinct ReportsTo from employees;

Tập hợp kết quả truy vấn - 3 hàng được trả về

Cách chọn bản ghi có giá trị null trong mysql

Nhưng nếu bạn đếm các bản ghi riêng biệt, nó sẽ trả về 2 vì hàm COUNT chỉ trả về số lượng các giá trị không phải NULL

expression IS NULL
0

Tập hợp kết quả truy vấn - trả về 1 hàng

Cách chọn bản ghi có giá trị null trong mysql

4. 2 Hàm tổng hợp MAX(), MIN(), SUM(), v.v. , bỏ qua giá trị NULL

4. 3 Khi sử dụng GROUP BY với cột chứa giá trị NULL, các giá trị NULL được gộp chung vào một nhóm

expression IS NULL
1

Tập hợp kết quả truy vấn - 3 hàng được trả về

Cách chọn bản ghi có giá trị null trong mysql

4. 4 TỔNG KẾT

ROLLUP tạo tổng giá trị cho cột được sử dụng trong mệnh đề GROUP BY. MySQL luôn trả về NULL trong cột tổng số để cho biết hàng này là tổng số, vì vậy chúng tôi cần chuyển đổi giá trị NULL thành nhãn thích hợp, chẳng hạn như 'Tổng số'

Truy vấn bên dưới hiển thị NULL ở hàng cuối cùng cho cột Tiêu đề được cuộn lên để tính tổng số lần xuất hiện cho tất cả các tiêu đề

expression IS NULL
2

Tập hợp kết quả truy vấn - 5 hàng được trả về

Cách chọn bản ghi có giá trị null trong mysql

Truy vấn này hiển thị từ Tổng số cho hàng cuối cùng bằng cách chuyển đổi NULL thành Tổng số

SELECT t1.FirstName, t1.LastName,
case when NULLIF(t1.ReportsTo,NULL) is null then 'Does not report to anyone'
else CONCAT(t2.FirstName, ' ', t2.LastName)
end as ReportsTo
FROM employees as t1
left join employees as t2 on t1.ReportsTo=t2.EmployeeID;
0

Tập hợp kết quả truy vấn - 5 hàng được trả về

Cách chọn bản ghi có giá trị null trong mysql

5. Sử dụng các giá trị NULL trong các cột tham gia

INNER JOIN bỏ qua các giá trị NULL. Truy vấn sau đây là phép nối bên trong và nó trả về 8 bản ghi trong đó bản ghi của Phó Chủ tịch Andrew Fuller không được đưa vào vì giá trị NULL không thể được nối với bất kỳ thứ gì. Giải pháp thay thế để bao gồm các giá trị NULL là sử dụng LEFT hoặc RIGHT JOIN

SELECT t1.FirstName, t1.LastName,
case when NULLIF(t1.ReportsTo,NULL) is null then 'Does not report to anyone'
else CONCAT(t2.FirstName, ' ', t2.LastName)
end as ReportsTo
FROM employees as t1
left join employees as t2 on t1.ReportsTo=t2.EmployeeID;
1

Tập hợp kết quả truy vấn - 8 hàng được trả về

Cách chọn bản ghi có giá trị null trong mysql

LEFT hoặc RIGHT OUTER JOIN giữ nguyên giá trị NULL. Truy vấn sau đây trả về 9 bản ghi trong đó bản ghi của Phó Tổng thống Andrew Fuller được lưu giữ

SELECT t1.FirstName, t1.LastName,
case when NULLIF(t1.ReportsTo,NULL) is null then 'Does not report to anyone'
else CONCAT(t2.FirstName, ' ', t2.LastName)
end as ReportsTo
FROM employees as t1
left join employees as t2 on t1.ReportsTo=t2.EmployeeID;
2

Tập hợp kết quả truy vấn - 9 hàng được trả về

Cách chọn bản ghi có giá trị null trong mysql

6. Chèn giá trị NULL hoặc giá trị mặc định

Khi thực hiện truy vấn chèn, cách tốt nhất là chỉ định giá trị mặc định cho cột có thể null. Điều này làm cho việc bảo trì bảng dễ dàng hơn nhiều

Đối với loại ký tự của cột, giá trị mặc định có thể là một chuỗi rỗng '', 'Không xác định' hoặc 'NA' (Không áp dụng)

Đối với loại cột ngày hoặc giờ, hãy chỉ định một ngày trong quá khứ xa hoặc tương lai xa. Ví dụ: trong bảng Nhân sự, đối với một vị trí cố định/liên tục, Ngày kết thúc việc làm có thể được đặt thành 31/12/2999 để biểu thị một hợp đồng lao động dài hạn

Đối với loại cột số, giá trị mặc định có thể là 0 hoặc -1 hoặc bất kỳ giá trị nào khác phù hợp với trường hợp của bạn. Nếu cột số là cột khóa ngoại có thực thi tính toàn vẹn tham chiếu, thì bạn cần thêm giá trị mặc định 0 hoặc -1 làm khóa chính trong bảng cha (còn được gọi là bảng chính hoặc bảng được tham chiếu hoặc bảng khóa chính). Thông thường 0 đại diện cho 'Không xác định' và -1 đại diện cho 'NA

Truy vấn sau đây sẽ không thành công với Mã lỗi 1048 vì cột CompanyName không được NULL và không có ràng buộc MẶC ĐỊNH nào được xác định cho cột này

Truy vấn này đã chèn một giá trị NULL vào cột ReportsTo và đã thành công vì cột này cho phép NULL

Nhân viên mới được thêm vào được hiển thị bên dưới

Cách chọn bản ghi có giá trị null trong mysql

7. Sử dụng NULL với cột TIMESTAMP hoặc AUTO_INCREMENT

Đối với một số loại dữ liệu, MySQL xử lý các giá trị NULL đặc biệt. Nếu bạn chèn NULL vào cột DẤU THỜI GIAN, ngày và giờ hiện tại sẽ được chèn. Nếu bạn chèn NULL vào cột số nguyên có thuộc tính AUTO_INCREMENT, thì số tiếp theo trong chuỗi sẽ được chèn

Trong truy vấn bên dưới, chúng tôi đã cố tình bỏ qua cột ID nhân viên và chúng tôi không chỉ định giá trị cho cột đó. Chạy xong nó tự động chèn EmployeeID 11 vì nó là dãy số auto tiếp theo cần dùng

SELECT t1.FirstName, t1.LastName,
case when NULLIF(t1.ReportsTo,NULL) is null then 'Does not report to anyone'
else CONCAT(t2.FirstName, ' ', t2.LastName)
end as ReportsTo
FROM employees as t1
left join employees as t2 on t1.ReportsTo=t2.EmployeeID;
3

Nhân viên mới được thêm vào được hiển thị bên dưới

Cách chọn bản ghi có giá trị null trong mysql

8. Lập chỉ mục một cột nullable

Bạn có thể thêm một chỉ mục trên một cột có giá trị NULL nếu bạn đang sử dụng công cụ lưu trữ MyISAM, InnoDB hoặc BDB hoặc MEMORY. Ngược lại, bạn phải khai báo cột có chỉ số NOT NULL, và bạn không thể chèn NULL vào cột

Bạn có thể CHỌN NULL trong SQL không?

Từ NULL được sử dụng để mô tả một giá trị bị thiếu trong SQL. Trong một bảng, giá trị NULL là một giá trị trong trường có vẻ trống. Trường có giá trị NULL giống với trường không có giá trị

Là NULL trong truy vấn MySQL?

Ràng buộc IS NULL có thể được sử dụng bất cứ khi nào cột trống và ký hiệu ( ' ') được sử dụng khi có giá trị trống. mysql> SELECT * FROM ColumnValueNullDemo WHERE ColumnName IS NULL OR ColumnName = ' '; Sau khi thực hiện truy vấn trên, kết quả thu được là.

Số đếm (*) có tính các hàng NULL không?

Ký hiệu COUNT(*) bao gồm các giá trị NULL trong tổng số . Ký hiệu COUNT(tên_cột) chỉ xem xét các hàng trong đó cột chứa giá trị không phải NULL.

MySQL xử lý các giá trị NULL như thế nào?

IS NULL − Toán tử này trả về true, nếu giá trị cột là NULL . IS NOT NULL - Toán tử này trả về true, nếu giá trị cột không phải là NULL.