Notes for SQL statement parsing in MySQL Databases

Case sensitivity when executing the SEARCH Command

By default, MySQL does not distinguish uppercase and lowercase letters when performing search. When you use like a % during search, all column values starting with a or A are returned by default. To search for a time zone, use the collate operator, an example is as follows:

If you want to keep a column case sensitive during search, use case sensitive or binary collation to define the column features.

When performing simple comparison operations, such as >=, >,=, <<=, sorting, and grouping, the internal sequence values of the characters, such as 'E ', 'E', and '?? 'These characters have the same internal Sequence Value.

Problems related to NULL values

The concept of NULL is confusing. Some people think that the concept of NULL and NULL characters is wrong for a while. For example,

The preceding two statements have different meanings and results. The first sentence indicates that the phone number of a person in the record inserted into the table is NULL, which means that the person's phone number in the record is unknown, and the second sentence inserts an empty character into the table, this means that you already know the phone number of the person in the record in the table. The phone number is, you can also confirm that this person does not have a normal phone number.

The help method for processing NULL values is mainly implemented by executing the is null operation, is not null operation, and IFNULL () function operation. In SQL, any result that has an operational relationship with NULL cannot be true (that is, it is always false ). To find the NULL value, the is null test operation must be executed.

You can add an index when a null value is contained in a table store such as MyISAM, InnoDB, BDB, or MEMORY. However, the index must be declared as not null, in this way, you cannot enter null values in this column.

When the load data infile statement is used to load data, the NULL value is replaced. When DISTINCT, group by, or order by is used, all null values are considered equal. Summary functions such as COUNT (), MIN (), SUM () Ignore NULL values.

But for some special types of columns, such as the timestamp type and columns with the auto_increment attribute, the former inserts NULL to insert the current time value, when NULL is inserted to the backend, it is the next positive integer sequence.