Summary of Knowledge Points of null in MySQL Database

  • 2021-12-13 10:03:47
  • OfStack

In the mysql database, null is a frequent occurrence. What are the precautions for null in mysql? The following is a brief summary, which will be continuously supplemented in the future.

1. is null

First of all, judge whether the value of a 1 column in the database is null, which cannot be judged by equal, but must be used by 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 is used in the same way 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 IFNULL () function is built into the MySQL database and is used in the same way 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 judge the null value, and the second field is to replace the other value returned when the first parameter is null, that is, if the first field is null, it will be replaced with other values. For example, select IFNULL (NULL, "java is the best language the the world"), because the value of the first parameter NULL, will output the value of the second parameter, java is the language of the world; 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 the table, it is necessary to first clarify whether the table below is null or empty. If it is null, then insert into cannot be used for filling data, but update must be used. This point seems simple, but it is often overlooked in actual development, so special attention is needed.

Examples:

Create an test table, colA can not store null value, colB can store null value.


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

The above is all the relevant knowledge points introduced this time. Thank you for your study and support for this site.


Related articles: