Database The difference between a hollow string and a null value two concepts __ Database

Source: Internet
Author: User
Tags mysql manual

Null in the database means "no input data to (column) variables", that is, NULL in the database can distinguish between "no input data to (column) variables" and "Input data to (column) variables". And the input of the empty string, is here the "have to (column) variable input data" in the case, that is, there is input data, but the input data is an empty string (or The Terminator). In this sense, such as int t; statements, but also belong to the "have to (column) variable input data" in the case, but not the programmer himself input value, but by (compile) the system input a random number.

The difference between null in a database and Null in languages such as C + +:

Null in the database is a keyword that says "No input data to (column) variables";

Null in languages such as C + + is a macro, expressed as an integer 0 general.

Therefore, you cannot use NULL (a macro) or ' null ' (a string) in the source binding variable function, because neither of them represents the keyword NULL in the database.
Of course, in the source code , NULL in the SQL statement string that does not contain the binding variable (for example, "INSERT into Table 1 values (null,null)") can be interpreted as null for a database. The SQL statement string is executed with the step function to successfully achieve the goal that is, the value in the table field is null
.

===========================================================================

the difference between null and void values in MySQL

Yesterday at work, there was a problem: a table in the database specified as NOT NULL, but the corresponding field inserted in the string "" is inserted directly into the database, without prohibiting the insertion.

Checked on the Internet, found that in MySQL, null (empty) and "" meaning is not the same:

Stackoverflow.com related Issues "MySQL, better to insert NULL or empty string?" High vote Answer:

Using null distinguishes between "no input data" and "input null data", the difference being that null is NULL, the length of an empty string is 01 string null data is more than the empty string priority count (message) counts an empty string in, But it does not count null data. You can search for an empty string by using a binding variable, but you cannot search for NULL, for example:

1 2 3
SELECT * FROM mytable WHERE mytext =?
MyText can never match the ' NULL ' value, regardless of how you pass the value from the client. A method that matches ' NULL ' can only be queried in this way:
1 2 3
SELECT * FROM MyTable WHERE mytext is NULL
MySQL Manual (version 5.0) test NULL needs to be null or is isn't null;=,<>,&lt, > equal to NULL calculation, the result is still null MySQL, 0 or null is FALSE, Any other value represents true. The value of the default Boolean operation is 1 in the group by operation, two null is considered equal; The first of the ASC is displayed, and in Desc is the last
=========================================


mysql empty string and null value problem

MySQL empty string and null values we all often meet, but the two are not a concept, and here is a brief description of the difference between null and MySQL null strings for your reference.

For beginners of SQL, the concept of null values often creates confusion, and they often assume that null is the same thing as a MySQL empty string. This is not the case. For example, the following statements are completely different:

Mysql> INSERT into my_table (phone) VALUES (NULL); Mysql> INSERT into my_table (phone) VALUES (');

Each of these two statements inserts a value into the phone column, but the 1th statement inserts a null value, and the 2nd statement inserts an empty string. The meaning of the 1th situation could be interpreted as "unknown telephone number", and the meaning of the 2nd situation could be interpreted as "the person has no telephone and therefore no telephone number".

For null processing, you can use the is null and are not NULL operators, and the Ifnull () function.

In SQL, a comparison of a null value with any other value (even if it is null) will never be true. An expression containing NULL always exports null values, unless otherwise specified in the document on the operator and in the function of the expression. All columns in the following example return null:

Mysql> SELECT NULL, 1+null, CONCAT (' invisible ', NULL);


If you intend to search for columns with null columns, you cannot use expr = null test. The following statement does not return any rows because, for any expression, expr = null is never true:

Mysql> SELECT * from my_table WHERE phone = NULL;

To find null values, you must use the IS NULL test . In the following statement, the way to find null phone numbers and empty phone numbers is described:

Mysql> SELECT * FROM my_table WHERE phone is NULL; Mysql> SELECT * from my_table WHERE phone = ';


more information and examples:

If you are using the MyISAM, InnoDB, BDB, or memory storage engine, you can add 1 indexes to a column that might have a null value. Otherwise, you must declare the index as NOT null and you cannot insert NULL into the column.

When you read data with the load data infile, you will update them for empty or missing columns. If you want to have a null value in the column, you should use \ n in the data file. In some cases, the literal word "NULL" can also be used.

When you use DISTINCT, GROUP by, or order BY, all null values are considered equivalent.

When you use ORDER BY, a null value is first displayed, and if you specify DESC in descending order, the null value is displayed last.

for aggregate (cumulative) functions, such as count (), MIN (), and sum (), null values are ignored. The exception to this is count (*), which counts rows instead of individual column values. For example, the following statement produces two counts. First counts the number of rows in the table, followed by counting the number of non-null values in the Age column:mysql> SELECT count (*), count (age) from the person;

For some column types, MySQL does special processing for null values. If you insert null into the timestamp column, the current date and time are inserted. If NULL is inserted into an integer column with the Auto_increment property, the next number in the sequence is inserted.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.