Summary of knowledge points about null in MySQL database

In the MySQL database, null is a common situation. What are the precautions regarding null in MySQL? The following is a brief summary, which will be supplemented later.

1. is null

First, determine whether the value of a column in the database is null. You cannot use equals to determine whether it is null. You must use is. For example, select * from users where user_name is null or select * from users where user_name is not null, but not select * from users where user_name = null

2. ISNULL()

The ISNULL() function is built into the MySQL database and its usage is the same as other built-in functions such as sum() provided in MySQL. For example, select ISNULL(user_name) from users where user_name = 'Demrystv' returns 0; select ISNULL(NULL) returns 1

3. IFNULL()

The MySQL database has a built-in IFNULL() function, and its usage is the same as other built-in functions such as sum() provided in MySQL. It mainly receives two parameters. The first parameter is the field or value to be judged as null, and the second field is another value to be returned when the first parameter is null. That is, if the first field is null, it will be replaced with another value. For example, select IFNULL(NULL, "java is the best language of the world"), since the value of the first parameter is NULL, the value of the second parameter, java is the best language of the world, will be output; similarly, if the first field is not empty, the value of the first field will be returned.

4. insert into and null

When using insert into to fill data into a table, you need to first make sure whether the table is null or empty. If it is null, you cannot use insert into to fill data, and you must use update. This seems simple, but it is often overlooked in actual development, so special attention needs to be paid.

example:

Create a test table, colA cannot store null values, but colB can store null values.

CREATE TABLE `test` (
`colA` varchar(255) NOT NULL,
`colB` varchar(255) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

The above are all the relevant knowledge points introduced this time. Thank you for your learning and support for 123WORDPRESS.COM.