Several points of attention when executing SQL statements in MySQL

Case problems when you perform a lookup command

By default, MySQL does not differentiate between uppercase and lowercase when performing a lookup. When you use like ' a% ' on a lookup, the default returns all column values starting with a or a, and to be case-sensitive when looking, you need to use the COLLATE operator, as follows:

col_name COLLATE latin1_general_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_bin

If you want a column to always be case-sensitive when looking, you need to define the attributes of the column with case sensitive or binary collation.

When a simple comparison operation, such as >=, <=, sorting, and grouping, will comply with the intrinsic order values of the characters, such as ' e ', ' e ', ', and ' é ', these characters have the same intrinsic order value.

Problems with null value NULL

The concept of NULL values is easy to confuse, some people think that null value null and NULL character ' is a while, in fact, that is wrong, for example,

mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');

The above two statements have completely different meanings and results. The first sentence indicates that the phone number of someone in the record inserted into the table is NULL, the phone number of the person in the record is not yet known, and the second sentence inserts a ' null character ' into the table, meaning that the phone number of the person in the record in the table is already known, the phone number is ', You can also determine that the person does not have a normal phone number.

A method that assists in handling null-value NULL, primarily by performing an IS null operation judgment, being not null operation judgment, and ifnull () function operation. In SQL, any result of an operational relationship with NULL cannot be true (that is, forever false). The IS null test operation must be performed in order to look for a null value.

You can add index when a table column with a storage type, such as MyISAM, InnoDB, BDB, or memory, contains a null value, but the index must be declared as NOT NULL, so that you cannot enter a null value in the column before.

When loading data using the Load Data infile statement, nulls NULL is replaced with the '. Using distinct, GROUP by, or all null values when order by is considered equivalent. Summary functions like count (), MIN (), sum () ignore null values.

But for some special types of columns, such as the timestamp type and the column with the Auto_increment attribute: The former insert null is actually inserted into the current time value, and the latter is the next positive integer sequence when inserting null.