MySQL expression evaluation and MySQL type conversion

2.4 Expression evaluation and type conversion
MySQL allows you to write expressions that include constants, function calls, and table-column references. These values can be grouped using different types of operators, such as arithmetic or comparison operators. An expression can be grouped with parentheses. An expression appears most frequently in the column selection list and the WHERE clause of the SELECT statement, as follows:

Each column that you select gives an expression, as shown in the WHERE clause. The expression also appears in the WHERE clause of the DELETE and UPDATE statements, and in the values () clause of the INSERT statement.
When MySQL encounters an expression, it is worth the result. For example, (4 * 3)/(4-2) is worth 6. An expression evaluation may involve type conversions. For example, MySQL converts to the date "19 9 6-0 8-2 1" When the number 960821 is used for an environment that requires a date value. This section discusses how to write a MySQL expression and the type conversion rules used by MySQL in evaluation of expressions. Each MySQL operator has been introduced, but MySQL has so many functions, we have only touched a few. Further introductions to each operator and function can be found in Appendix C.
2.4.1 Compose expression
An expression can be just a simple constant, such as:
0 Numeric constants
"A b C" string constant
An expression can be called by a function. Some functions require arguments (there are values in parentheses), and some do not. Multiple arguments should be separated by commas. When calling a function, there can be spaces next to the argument, but no spaces between the function name and the parentheses. Here are some examples of functions:
Now () no parameter function
STRCMP ("A b C", "D E F") functions with two parameters
STRCMP ("A b C", "D E F") parameters with spaces is legal
STRCMP ("A b C", "D E F") function name followed by a space is illegal
If there is a space behind the function name, the MySQL analyzer may interpret the function name as a column name (the functions name is not a reserved word and can be used as a column name if necessary). The result is a syntax error. You can use table columns in an expression. In the simplest case, when a column belongs to a table that is explicit in the context, it can simply refer to the column name. There is only one table name in each of the following SELECT statements, so the column references are unambiguous:

If you are using a table whose columns are ambiguous, you can precede the column name with the table name. If you are using a table in which database is also ambiguous, you can add the database name before the table name. If you just want the meaning to be more pronounced, you can also use this more specific representation in an unambiguous context, such as:

In summary, you can combine all of these values to get more complex expressions.
1. Type of operator
MySQL has several types of operators that you can use to connect an item of an expression. Arithmetic operators, as shown in table 2-15, typically include addition, subtraction, multiplication, addition, and modulo operators. When two operands are integers, the "+", "-" and "*" arithmetic operations are performed with bigint (64-bit) integer values. When the result is expected to be an integer, the "/" and "%" are also performed with bigint (64-bit) integer values. It should be recognized that if an operation involves a larger value, the result is unpredictable if the result is more than 64 digits.

The logical operator, as shown in table 2-16, estimates the expression to determine whether it is true (not 0) or False (0). MySQL contains C-style "& &", "| |" and "!" operator, which replaces a n D, OR and N O T. Pay special attention to "| |" operator, ANSI SQL specifies "| |" As a string connector, but in MySQL, it represents a logic or operation. If you execute the following query, the number 0 is returned:

MySQL converts "a b C" and "D E F" to integers for the operation, and both are converted to 0, 0 and 0, and the result is 0. In MySQL, you must use Concat ("a b C", "D E F") to complete the connection of the string.

bitwise operators, as shown in table 2-17, complete the bitwise AND and OR, where each bit of the result is evaluated by the logical and OR or of the corresponding bit of two operands. You can also move the bit left or right. Bitwise operations are performed with bigint (64-bit) integer values.

Comparison operators, as shown in table 2-18, include operators that test the order of relative size or number and string, and the operator that completes pattern matching and tests null values. The "< = >" operator is unique to MySQL and is introduced in the MySQL3.23 version.


From the MySQL3.23 version, you can use the B I N A RY operator, which can be used to convert a string into a binary string, which is case-sensitive in comparison. The first comparison below is case-insensitive, but the second and third comparisons are case-sensitive:

There is no corresponding not BINARY calculation. If you want to make a column both case-sensitive and case insensitive, you should use binary for case-insensitive columns and for comparisons that you want to be case-sensitive. Comparisons are always case-sensitive for columns that are defined using binary string types (CHAR BINARY, VARCHAR BINARY, and blob types). In order to implement a case-insensitive comparison of such column types, you can use upper () or lower () to convert to the same case:

For case-insensitive string comparisons, it is possible to think of multiple characters as equal, depending on the character set used. For example, "E" and "é" may be the same for comparison and sorting operations. Binary (case-sensitive) comparisons are done using the ASCII value of the character.
Pattern matching allows you to find a value without having to give an exact direct value. MySQL provides pattern matching for SQL using the LIKE operator and wildcard "%" (matching any sequence of characters) and "_" (matching any single character). MySQL also provides pattern matching based on regexp operators and extended regular expressions similar to those used in UNIX programs such as G R e p, sed, and VI. In order to complete pattern matching, you must use one of these pattern-matching operators, and you cannot use ' = '. For the opposite pattern match, you can use not like or not REGEXP.
In addition to the pattern operators and pattern characters used, the two pattern matches differ in the following important ways:
Like is case-insensitive unless at least one operand is a binary string. REGEXP is case-sensitive. (in later versions of MySQL3.23.4, RegExp is case-insensitive unless at least one operand is a binary string.) ）
SQL is the pattern match only if the entire string matches. A regular expression matches only if the corresponding pattern appears at one point in the string. The pattern used for the LIKE operator can include the "%" and "_" wildcard characters. For example, the pattern "F R a n k%" matches any string that begins with "F R a n k":

The wildcard character "%" matches any string, including the sequence of null characters, so that "frank%" matches "Frank":

This also means that the pattern "%" matches any string, including matching the empty string. However, "%" does not match null. In fact, any pattern matching with a null operand will fail:

MySQL's like operator is case-insensitive unless it has at least one operand that is a binary string. Thus, by default, "Frank%" matches the string "frankly" and "frankly", but in a binary comparison, it matches only one of them:

This differs from the LIKE operator for ANSI SQL, which is case-sensitive. Wildcard characters can be given anywhere in the pattern. "% bert" matches "Englebert", "Bert", and "Albert". "% Bert%" also matches all of these strings and is also associated with such as "Berthold", "Bertram", and "Alberta"
String match. Another wildcard character like the one allowed is "_", which matches a single character. "_ _" matches a string of three characters. "C _ T" and "c a T", "C ut" or even "C _ T" match (because "_" matches itself). To turn off the special meaning of "%" or "_", match the direct instance of these characters, place a slash ("%" or "_") in front of them, such as:

Another form of MySQL pattern matching uses a regular expression. Operator is regexp instead of L I K E (rlike is a synonym for regexp). The most commonly used formal expression pattern characters are as follows: '. ' Match any single character:

‘[ . . . ]’ Matches any character in the square brackets. You can list the range endpoints that are delimited by dashes '-' to specify a range of characters. To negate the meaning of this interval (that is, to match any character not listed), specify ' ^ ' as the first character of the range:

' * ' means ' match any number of characters with the preceding character ', so that ' x ' matches any number of ' X ' characters, such as:

"Any number" includes 0 instances, which is why the second expression matches the success. ' ^pat ' and ' pat$ ' fix a pattern match, so that the pattern Pat matches only when it appears at the head of the string, and ' ^pat$ ' matches only when the PAT matches the entire string, for example:

REGEXP mode can be taken out of a table column, although it is slower than constant mode if the column contains several different values. Each time a column value changes, the pattern must be checked and converted to an internal form. MySQL's regular expression matching also has some special pattern characters. For more details see Appendix C.
2. Precedence of Operators
When the value of an expression is evaluated, the operator is first viewed to determine the order of the operation. Some operators have a higher priority, for example, multiplication and Remaliah and subtraction are higher. The following two expressions are equivalent because "*" and "/" precede "+" and "-" calculations:

The precedence of operators, from high to low, is listed below. Operators that are listed in the same row have the same precedence. Operators with higher precedence are evaluated before the operator with a lower precedence level.

Parentheses are used to ignore the precedence of operators and to change the order in which expressions are evaluated, such as:

3. Null values in an expression
Note that when you use a null value in an expression, the result is likely to be an unexpected occurrence. The following guidelines will help to avoid problems. If NULL is used as an operand of an arithmetic or bitwise operator, the result is null:

If NULL is used for logical operators, NULL is considered false:

Null as an operand of any comparison operator, except for the < = >, is null, and is not NULL operators (which are specifically extended to handle null values), a null result is generated. Such as:


If a null argument is given to a function, in addition to those functions that handle null parameters, a null result is generally returned. For example, ifnull () can handle null parameters and return TRUE or false appropriately. STRCMP () expects a non-null parameter, and returns null instead of TRUE or false if it finds a null argument passed to it. In the sort operation, NULL values are grouped together. In ascending sort, null is ranked before all Non-null values (including empty strings), whereas in descending order Null is ranked after all Non-null values.
2.4.2 Type Conversions
MySQL automatically performs a large number of type conversions based on the type of operation being performed, which is done whenever a value of one type is used for an occasion that requires another type of value. The following are the reasons why type conversions are required:
The operand is converted to a type that is appropriate for an operator evaluation.
The function argument is converted to the type required by the function.
Converts a value to assign to a table column of different types. The following expressions involve type conversions. It consists of the addition operator "+" and two operands 1 and "2":
1+ "2"
The operands are of a different type, one is a number and the other is a string, so MySQL converts one of them so that two of them have the same type. But which one should be converted? Because, "+" is a numeric operator, MySQL expects the number of operands, so the string "2" is converted to 2. And then find this expression worth 3. Let me cite one more example. The C O N C at () function connection string produces a longer string as a result. To do this, it interprets the arguments as strings, regardless of the actual type of parameters. If you pass to C O N C at (), it converts them to strings and then returns the connections for those strings, such as:

If you call C O N C at () as part of an expression, further type conversions may occur. Examine the following expressions and their results:

CONCAT (1, 2, 3) produces a string of "12 3". The expression "12 3"/10 converts to 12 3/10, because except for an arithmetic operator. The result of this expression is a floating-point form of 12. 3, but repeat () requires a repeat count of integers, so it's divisible by 12. Then, REPEAT (' x ', 12) produces a result string containing 12 ' x ' characters. As a general rule, MySQL tries to convert the value to the type required by the expression, avoiding errors caused by the wrong type of value. Depending on the context, MySQL will convert values between three common types (number, string, or date and time). However, a value cannot always be converted from one type to another. This conversion fails if the converted value is not a valid value for the given type. Converts a number to something such as "a B C", and the result is 0. Converts something that is not like a date or time to a date or time type result is a value of "0" of that type. For example, convert the string "a B C" to the date result is "0" date "0 0 0 0-0 0-0 0". Any value can be processed as a string, so generally converting a value to a string does not create a problem.
MySQL also makes some minor type conversions. If a floating-point value is used in an integral environment, this value is converted and rounded when converted. It is also possible to do the opposite; an integer used as a floating-point number would not be problematic. A hexadecimal constant is generally treated as a string, unless its contents appear to represent a number. In a string context, each pair of hexadecimal digits is converted to one character, with the result as a string. Here are some examples of conversions:

The same principle of interpretation is applied to the comparison; unless compared to a number, the hexadecimal constant is treated as a string, for example:

Some operators can cast the operands to the type they want, regardless of the type of operand. For example, an arithmetic operator requires a number and then converts the operand by this, referring to the following operation:

MySQL does not search the entire string for a number of lookups; it only looks at the beginning of the string. If a string does not use a number as the leading part, the conversion result is 0.

Please note that MySQL's string to number conversion rule since 3. 2 3 has changed since then. Before this version, a string similar to a number was converted to rounded integer values. Since version 3.23, they have been converted to floating-point values, such as:

Logical AND bitwise operators are more demanding than arithmetic operators. Not only do they want the number of operands, but they also require integers. This means that a floating-point number, such as. 3, is not considered true, although it is Non-zero, because when converted to an integer. 3 has been converted to 0. In the following expression, the operands are not considered true unless the operands have a value of at least 1.

This conversion also occurs in the if () function, which requires the first argument to be an integer. In order to properly test floating-point values, it is best to take advantage of explicit comparisons. Otherwise, a value less than 1 will be considered false, for example:

The pattern matching operator requires that the string be processed. This means that the pattern-matching operators of MySQL can be used in numbers because MySQL converts them to strings in an attempt to match them. For example:

The size comparison operators ("<", "< =", "=" and so on) are context-sensitive, that is, they are evaluated based on the type of the operand. The following expression compares operands from a numeric value because the operators are both on both sides of the number.

2 < 11

-> 1

The following expression involves a string comparison because the operands on both sides of the string are strings:

"2" < "11"

-> 0

In the comparison below, the types are mixed, so MySQL compares them by number. The result is that all two expressions are true:

In each comparison, MySQL converts the operands according to the following rules:
In addition to the "< = >" operator, the value of a comparison involving a null value is null (except that null <=> NULL is true, and "< = >" is the same as "=").
If two operands are strings, the dictionary order is compared by string. String comparisons are made using a valid character set on the server.
If two operands are integers, the number is compared by integer.
Hexadecimal constants that are not compared to numbers are compared by binary strings.
If one of the operands is a Times tamp or datetime value and the other is a constant, the times tamp value is compared. Doing so will make comparisons better for ODBC applications.
Otherwise, the two operands are compared by floating-point values. Note that this includes the case where a string is compared to a number. The string is converted to numbers, and if the string is converted to a number, the result is 0. For example, "14. 3 "conversion to 14. 3, but "L 4". 3 "conversion to 0.
1. Rules for the interpretation of dates and times
MySQL converts string and number freely to date and time values in an expression environment, and vice versa. Date and time values are converted to numbers in a numeric context, and the number is converted to a date or time in the context of a date or time. Convert to a date or time value when a value is assigned to a date or time column, or when a function requires a date or time value. If the table my_table contains a date column date _ col, the following statements are equivalent:

The parameters of the To _ days () function are the same values in the following three expressions:

2. Test and force type conversions
To understand how type conversions are performed in an expression, a SELECT statement that uses a MySQL program to evaluate an expression is as follows:

As you can imagine, the author made a lot of comparisons when writing this chapter. The evaluation of a test expression is extremely important for statements such as delete or update that modify records, because you need to guarantee that only the rows involved are involved. One way to check an expression is to validate that the row selected by the clause is correct by executing it in advance with the same WHERE clause that is prepared for the DELETE or UPDATE statement. If the table my_table has a char column char _ col containing the following values:

The original plan is probably to delete the two lines containing the value "0 0". But the real effect is to delete all the rows. This is because MySQL's comparison rules work. Char_col is a string column, but 00 is not enclosed in quotes, so it is treated as a number. According to the MySQL comparison rule, a comparison of a string and a number is evaluated by a comparison of two numbers. With the execution of the delete query, each value of Char_col is converted to 0, and "0 0" is converted to 0, so all strings that are not similar are converted to 0. Thus, for each row, the WHERE clause is true, so the DELETE statement empties the table. Obviously, this is the case where the WHERE clause should be tested with a SELECT statement before the delete is executed, which will show that the expression has chosen too many rows. As shown below:

If you are not sure how a value is used, you can use the expression evaluation mechanism of MySQL to cast the value to a specific type:
Add + 0 or + 0.0 to an item to cast to a numeric value:


3. Conversion of a range or illegal value
The basic principles for the conversion of a hyper-range or illegal value are: useless input, useless output. You might get something you don't like if you don't validate it before the store date. Here are some general rules for MySQL dealing with hyper-range values or irregularities, which have been described previously:
For numeric or T I M e columns, values that exceed the valid range are clipped to the nearest numeric value range and stored as the result value.
For string columns that are not enum or set, too long strings are truncated to strings that fit the maximum length of the column's storage. The assignment of an ENUM or set column depends on the legal value given when the column is defined. Assigning an enum column A value that is not given as an enumeration member will give an error member (that is, an empty string corresponding to a 0-valued member). If the set column is given a value that contains a substring that is not given as a member of the collection, then the substrings that are not given as members of the collection are deleted and the remaining members are assigned a value to the column.
For a date or time column, the illegal value is converted to the appropriate "0" value for that type (see table 2-11). For dates and times that are not time columns, values that exceed the range of values can be converted to "0" values, NULL, or some other value (in other words, the result is unpredictable).
These transformations are reported as warning messages for alter TABLE, LOAD DATA, UPDATE, and multiline INSERT statements. In the MySQL client, this information is displayed on the status line of the query report. In a programming language, this information can be obtained by some other means. If you are using the MYSQLC API, you can call the Mysql_info () function to obtain this information. For the Perl DBI API, you can take advantage of the Mysql_info properties of the database connection. The information provided is a count of the number of warning messages. To know which rows are changed, you can publish a select ... Intooutfile the query and compares the results to the original rows