Considerations for executing SQL statements in MySQL

We need to pay attention to the execution of search and so on during MySQL SQL statement execution. BKJIA recommends the MySQL database getting started and proficient tutorial.

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 % 'for search, all column values starting with a or a are returned by default, you need to use the collate operator for implementation. The specific 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 'then©'These characters have the same internal Sequence Value.

Problems related to NULL values

The concept of NULL values is confusing. Some people think that NULL and NULL characters ''are incorrect. 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, the second sentence inserts a ''null character into the table, which means that you already know the phone number of the person in the record. 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, NULL values are 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.