Chapter 12 Functions and Operators


内容表

12.1 Function and Operator Reference
12.2 Type Conversion in Expression Evaluation
12.3 Operators
12.3.1 Operator Precedence
12.3.2 Comparison Functions and Operators
12.3.3 Logical Operators
12.3.4 Assignment Operators
12.4 Control Flow Functions
12.5 String Functions
12.5.1 String Comparison Functions
12.5.2 Regular Expressions
12.5.3 Character Set and Collation of Function Results
12.6 Numeric Functions and Operators
12.6.1 Arithmetic Operators
12.6.2 Mathematical Functions
12.7 Date and Time Functions
12.8 What Calendar Is Used By MySQL?
12.9 Full-Text Search Functions
12.9.1 Natural Language Full-Text Searches
12.9.2 Boolean Full-Text Searches
12.9.3 Full-Text Searches with Query Expansion
12.9.4 Full-Text Stopwords
12.9.5 Full-Text Restrictions
12.9.6 Fine-Tuning MySQL Full-Text Search
12.9.7 Adding a Collation for Full-Text Indexing
12.9.8 ngram Full-Text Parser
12.9.9 MeCab Full-Text Parser Plugin
12.10 Cast Functions and Operators
12.11 XML Functions
12.12 Bit Functions and Operators
12.13 Encryption and Compression Functions
12.14 Locking Functions
12.15 Information Functions
12.16 Spatial Analysis Functions
12.16.1 Spatial Function Reference
12.16.2 Argument Handling by Spatial Functions
12.16.3 Functions That Create Geometry Values from WKT Values
12.16.4 Functions That Create Geometry Values from WKB Values
12.16.5 MySQL-Specific Functions That Create Geometry Values
12.16.6 Geometry Format Conversion Functions
12.16.7 Geometry Property Functions
12.16.8 Spatial Operator Functions
12.16.9 Functions That Test Spatial Relations Between Geometry Objects
12.16.10 Spatial Geohash Functions
12.16.11 Spatial GeoJSON Functions
12.16.12 Spatial Convenience Functions
12.17 JSON Functions
12.17.1 JSON Function Reference
12.17.2 Functions That Create JSON Values
12.17.3 Functions That Search JSON Values
12.17.4 Functions That Modify JSON Values
12.17.5 Functions That Return JSON Value Attributes
12.17.6 JSON Table Functions
12.17.7 JSON Utility Functions
12.18 Functions Used with Global Transaction Identifiers (GTIDs)
12.19 MySQL Enterprise Encryption Functions
12.19.1 MySQL Enterprise Encryption Installation
12.19.2 MySQL Enterprise Encryption Usage and Examples
12.19.3 MySQL Enterprise Encryption Function Reference
12.19.4 MySQL Enterprise Encryption Function Descriptions
12.20 Aggregate (GROUP BY) Functions
12.20.1 Aggregate (GROUP BY) Function Descriptions
12.20.2 GROUP BY Modifiers
12.20.3 MySQL Handling of GROUP BY
12.20.4 Detection of Functional Dependence
12.21 Window Functions
12.21.1 Window Function Descriptions
12.21.2 Window Function Concepts and Syntax
12.21.3 Window Function Frame Specification
12.21.4 Named Windows
12.21.5 Window Function Restrictions
12.22 Internal Functions
12.23 Miscellaneous Functions
12.24 Precision Math
12.24.1 Types of Numeric Values
12.24.2 DECIMAL Data Type Characteristics
12.24.3 Expression Handling
12.24.4 Rounding Behavior
12.24.5 Precision Math Examples

Expressions can be used at several points in SQL statements, such as in the ORDER BY or HAVING clauses of SELECT statements, in the WHERE clause of a SELECT , DELETE , or UPDATE statement, or in SET statements. Expressions can be written using literal values, column values, NULL , built-in functions, stored functions, user-defined functions, and operators. This chapter describes the functions and operators that are permitted for writing expressions in MySQL. Instructions for writing stored functions and user-defined functions are given in Section 24.2, “Using Stored Routines (Procedures and Functions)” , and Section 29.4, “Adding New Functions to MySQL” . See Section 9.2.4, “Function Name Parsing and Resolution” , for the rules describing how the server interprets references to different kinds of functions.

An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for a particular function or operator.

Note

By default, there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. However, spaces around function arguments are permitted.

You can tell the MySQL server to accept spaces after function names by starting it with the --sql-mode=IGNORE_SPACE option. (See Section 5.1.11, “Server SQL Modes” .) Individual client programs can request this behavior by using the CLIENT_IGNORE_SPACE option for mysql_real_connect() . In either case, all function names become reserved words.

For the sake of brevity, most examples in this chapter display the output from the mysql program in abbreviated form. Rather than showing examples in this format:

mysql> SELECT MOD(29,9);
+-----------+
| mod(29,9) |
+-----------+
|         2 |
+-----------+
1 rows in set (0.00 sec)
                

This format is used instead:

mysql> SELECT MOD(29,9);
        -> 2
                

12.1 Function and Operator Reference

Table 12.1 Functions and Operators

Name 描述
ABS() Return the absolute value
ACOS() Return the arc cosine
ADDDATE() Add time values (intervals) to a date value
ADDTIME() Add time
AES_DECRYPT() Decrypt using AES
AES_ENCRYPT() Encrypt using AES
AND , && Logical AND
ANY_VALUE() Suppress ONLY_FULL_GROUP_BY value rejection
ASCII() Return numeric value of left-most character
ASIN() Return the arc sine
= Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement)
:= Assign a value
ASYMMETRIC_DECRYPT() Decrypt ciphertext using private or public key
ASYMMETRIC_DERIVE() Derive symmetric key from asymmetric keys
ASYMMETRIC_ENCRYPT() Encrypt cleartext using private or public key
ASYMMETRIC_SIGN() Generate signature from digest
ASYMMETRIC_VERIFY() Verify that signature matches digest
ATAN() Return the arc tangent
ATAN2() , ATAN() Return the arc tangent of the two arguments
AVG() Return the average value of the argument
BENCHMARK() Repeatedly execute an expression
BETWEEN ... AND ... Check whether a value is within a range of values
BIN() Return a string containing binary representation of a number
BIN_TO_UUID() Convert binary UUID to string
BINARY Cast a string to a binary string
BIT_AND() Return bitwise AND
BIT_COUNT() Return the number of bits that are set
BIT_LENGTH() Return length of argument in bits
BIT_OR() Return bitwise OR
BIT_XOR() Return bitwise XOR
& Bitwise AND
~ Bitwise inversion
| Bitwise OR
^ Bitwise XOR
CAN_ACCESS_COLUMN() Internal use only
CAN_ACCESS_DATABASE() Internal use only
CAN_ACCESS_TABLE() Internal use only
CAN_ACCESS_VIEW() Internal use only
CASE Case operator
CAST() Cast a value as a certain type
CEIL() Return the smallest integer value not less than the argument
CEILING() Return the smallest integer value not less than the argument
CHAR() Return the character for each integer passed
CHAR_LENGTH() Return number of characters in argument
CHARACTER_LENGTH() Synonym for CHAR_LENGTH()
CHARSET() Return the character set of the argument
COALESCE() Return the first non-NULL argument
COERCIBILITY() Return the collation coercibility value of the string argument
COLLATION() Return the collation of the string argument
COMPRESS() Return result as a binary string
CONCAT() Return concatenated string
CONCAT_WS() Return concatenate with separator
CONNECTION_ID() Return the connection ID (thread ID) for the connection
CONV() Convert numbers between different number bases
CONVERT() Cast a value as a certain type
CONVERT_TZ() Convert from one time zone to another
COS() Return the cosine
COT() Return the cotangent
COUNT() Return a count of the number of rows returned
COUNT(DISTINCT) Return the count of a number of different values
CRC32() Compute a cyclic redundancy check value
CREATE_ASYMMETRIC_PRIV_KEY() Create private key
CREATE_ASYMMETRIC_PUB_KEY() Create public key
CREATE_DH_PARAMETERS() Generate shared DH secret
CREATE_DIGEST() Generate digest from string
CUME_DIST() Cumulative distribution value
CURDATE() Return the current date
CURRENT_DATE() , CURRENT_DATE Synonyms for CURDATE()
CURRENT_ROLE() Returns the current active roles
CURRENT_TIME() , CURRENT_TIME Synonyms for CURTIME()
CURRENT_TIMESTAMP() , CURRENT_TIMESTAMP Synonyms for NOW()
CURRENT_USER() , CURRENT_USER The authenticated user name and host name
CURTIME() Return the current time
DATABASE() Return the default (current) database name
DATE() Extract the date part of a date or datetime expression
DATE_ADD() Add time values (intervals) to a date value
DATE_FORMAT() Format date as specified
DATE_SUB() Subtract a time value (interval) from a date
DATEDIFF() Subtract two dates
DAY() Synonym for DAYOFMONTH()
DAYNAME() Return the name of the weekday
DAYOFMONTH() Return the day of the month (0-31)
DAYOFWEEK() Return the weekday index of the argument
DAYOFYEAR() Return the day of the year (1-366)
DECODE() Decodes a string encrypted using ENCODE()
DEFAULT() Return the default value for a table column
DEGREES() Convert radians to degrees
DENSE_RANK() Rank of current row within its partition, without gaps
DES_DECRYPT() Decrypt a string
DES_ENCRYPT() Encrypt a string
DIV Integer division
/ Division operator
ELT() Return string at index number
ENCODE() Encode a string
ENCRYPT() Encrypt a string
= Equal operator
<=> NULL-safe equal to operator
EXP() Raise to the power of
EXPORT_SET() Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
EXTRACT() Extract part of a date
ExtractValue() Extracts a value from an XML string using XPath notation
FIELD() Return the index (position) of the first argument in the subsequent arguments
FIND_IN_SET() Return the index position of the first argument within the second argument
FIRST_VALUE() Value of argument from first row of window frame
FLOOR() Return the largest integer value not greater than the argument
FORMAT() Return a number formatted to specified number of decimal places
FOUND_ROWS() For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause
FROM_BASE64() Decode base64 encoded string and return result
FROM_DAYS() Convert a day number to a date
FROM_UNIXTIME() Format Unix timestamp as a date
GeomCollection() Construct geometry collection from geometries
GeometryCollection() Construct geometry collection from geometries
GET_DD_COLUMN_PRIVILEGES() Internal use only
GET_DD_CREATE_OPTIONS() Internal use only
GET_DD_INDEX_SUB_PART_LENGTH() Internal use only
GET_FORMAT() Return a date format string
GET_LOCK() Get a named lock
> Greater than operator
>= Greater than or equal operator
GREATEST() Return the largest argument
GROUP_CONCAT() Return a concatenated string
GROUPING() Distinguish super-aggregate ROLLUP rows from regular rows
GTID_SUBSET() Return true if all GTIDs in subset are also in set; otherwise false.
GTID_SUBTRACT() Return all GTIDs in set that are not in subset.
HEX() Return a hexadecimal representation of a decimal or string value
HOUR() Extract the hour
ICU_VERSION() ICU library version
IF() If/else construct
IFNULL() Null if/else construct
IN() Check whether a value is within a set of values
INET_ATON() Return the numeric value of an IP address
INET_NTOA() Return the IP address from a numeric value
INET6_ATON() Return the numeric value of an IPv6 address
INET6_NTOA() Return the IPv6 address from a numeric value
INSERT() Insert a substring at the specified position up to the specified number of characters
INSTR() Return the index of the first occurrence of substring
INTERNAL_AUTO_INCREMENT() Internal use only
INTERNAL_AVG_ROW_LENGTH() Internal use only
INTERNAL_CHECK_TIME() Internal use only
INTERNAL_CHECKSUM() Internal use only
INTERNAL_DATA_FREE() Internal use only
INTERNAL_DATA_LENGTH() Internal use only
INTERNAL_DD_CHAR_LENGTH() Internal use only
INTERNAL_GET_COMMENT_OR_ERROR() Internal use only
INTERNAL_GET_VIEW_WARNING_OR_ERROR() Internal use only
INTERNAL_INDEX_COLUMN_CARDINALITY() Internal use only
INTERNAL_INDEX_LENGTH() Internal use only
INTERNAL_KEYS_DISABLED() Internal use only
INTERNAL_MAX_DATA_LENGTH() Internal use only
INTERNAL_TABLE_ROWS() Internal use only
INTERNAL_UPDATE_TIME() Internal use only
INTERVAL() Return the index of the argument that is less than the first argument
IS Test a value against a boolean
IS_FREE_LOCK() Whether the named lock is free
IS_IPV4() Whether argument is an IPv4 address
IS_IPV4_COMPAT() Whether argument is an IPv4-compatible address
IS_IPV4_MAPPED() Whether argument is an IPv4-mapped address
IS_IPV6() Whether argument is an IPv6 address
IS NOT Test a value against a boolean
IS NOT NULL NOT NULL value test
IS NULL NULL value test
IS_USED_LOCK() Whether the named lock is in use; return connection identifier if true
IS_UUID() Whether argument is a valid UUID
ISNULL() Test whether the argument is NULL
JSON_ARRAY() Create JSON array
JSON_ARRAY_APPEND() Append data to JSON document
JSON_ARRAY_INSERT() Insert into JSON array
JSON_ARRAYAGG() Return result set as a single JSON array
-> Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
JSON_CONTAINS() Whether JSON document contains specific object at path
JSON_CONTAINS_PATH() Whether JSON document contains any data at path
JSON_DEPTH() Maximum depth of JSON document
JSON_EXTRACT() Return data from JSON document
->> Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
JSON_INSERT() Insert data into JSON document
JSON_KEYS() Array of keys from JSON document
JSON_LENGTH() Number of elements in JSON document
JSON_MERGE() (deprecated 8.0.3) Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()
JSON_MERGE_PATCH() Merge JSON documents, replacing values of duplicate keys
JSON_MERGE_PRESERVE() Merge JSON documents, preserving duplicate keys
JSON_OBJECT() Create JSON object
JSON_OBJECTAGG() Return result set as a single JSON object
JSON_PRETTY() Prints a JSON document in human-readable format, with each array element or object member printed on a new line, indented two spaces with respect to its parent.
JSON_QUOTE() Quote JSON document
JSON_REMOVE() Remove data from JSON document
JSON_REPLACE() Replace values in JSON document
JSON_SEARCH() Path to value within JSON document
JSON_SET() Insert data into JSON document
JSON_STORAGE_FREE() Freed space within binary representation of a JSON column value following a partial update
JSON_STORAGE_SIZE() Space used for storage of binary representation of a JSON document; for a JSON column, the space used when the document was inserted, prior to any partial updates
JSON_TABLE() Returns data from a JSON expression as a relational table
JSON_TYPE() Type of JSON value
JSON_UNQUOTE() Unquote JSON value
JSON_VALID() Whether JSON value is valid
LAG() Value of argument from row lagging current row within partition
LAST_DAY Return the last day of the month for the argument
LAST_INSERT_ID() Value of the AUTOINCREMENT column for the last INSERT
LAST_VALUE() Value of argument from last row of window frame
LCASE() Synonym for LOWER()
LEAD() Value of argument from row leading current row within partition
LEAST() Return the smallest argument
LEFT() Return the leftmost number of characters as specified
<< Left shift
LENGTH() Return the length of a string in bytes
< Less than operator
<= Less than or equal operator
LIKE Simple pattern matching
LineString() Construct LineString from Point values
LN() Return the natural logarithm of the argument
LOAD_FILE() Load the named file
LOCALTIME() , LOCALTIME Synonym for NOW()
LOCALTIMESTAMP , LOCALTIMESTAMP() Synonym for NOW()
LOCATE() Return the position of the first occurrence of substring
LOG() Return the natural logarithm of the first argument
LOG10() Return the base-10 logarithm of the argument
LOG2() Return the base-2 logarithm of the argument
LOWER() Return the argument in lowercase
LPAD() Return the string argument, left-padded with the specified string
LTRIM() Remove leading spaces
MAKE_SET() Return a set of comma-separated strings that have the corresponding bit in bits set
MAKEDATE() Create a date from the year and day of year
MAKETIME() Create time from hour, minute, second
MASTER_POS_WAIT() Block until the slave has read and applied all updates up to the specified position
MATCH Perform full-text search
MAX() Return the maximum value
MBRContains() Whether MBR of one geometry contains MBR of another
MBRCoveredBy() Whether one MBR is covered by another
MBRCovers() Whether one MBR covers another
MBRDisjoint() Whether MBRs of two geometries are disjoint
MBREquals() Whether MBRs of two geometries are equal
MBRIntersects() Whether MBRs of two geometries intersect
MBROverlaps() Whether MBRs of two geometries overlap
MBRTouches() Whether MBRs of two geometries touch
MBRWithin() Whether MBR of one geometry is within MBR of another
MD5() Calculate MD5 checksum
MICROSECOND() Return the microseconds from argument
MID() Return a substring starting from the specified position
MIN() Return the minimum value
- Minus operator
MINUTE() Return the minute from the argument
MOD() Return the remainder
% , MOD Modulo operator
MONTH() Return the month from the date passed
MONTHNAME() Return the name of the month
MultiLineString() Contruct MultiLineString from LineString values
MultiPoint() Construct MultiPoint from Point values
MultiPolygon() Construct MultiPolygon from Polygon values
NAME_CONST() Causes the column to have the given name
NOT , ! Negates value
NOT BETWEEN ... AND ... Check whether a value is not within a range of values
!= , <> Not equal operator
NOT IN() Check whether a value is not within a set of values
NOT LIKE Negation of simple pattern matching
NOT REGEXP Negation of REGEXP
NOW() Return the current date and time
NTH_VALUE() Value of argument from N-th row of window frame
NTILE() Bucket number of current row within its partition.
NULLIF() Return NULL if expr1 = expr2
OCT() Return a string containing octal representation of a number
OCTET_LENGTH() Synonym for LENGTH()
|| , OR Logical OR
ORD() Return character code for leftmost character of the argument
PASSWORD() Calculate and return a password string
PERCENT_RANK() Percentage rank value
PERIOD_ADD() Add a period to a year-month
PERIOD_DIFF() Return the number of months between periods
PI() Return the value of pi
+ Addition operator
Point() Construct Point from coordinates
Polygon() Construct Polygon from LineString arguments
POSITION() Synonym for LOCATE()
POW() Return the argument raised to the specified power
POWER() Return the argument raised to the specified power
QUARTER() Return the quarter from a date argument
QUOTE() Escape the argument for use in an SQL statement
RADIANS() Return argument converted to radians
RAND() Return a random floating-point value
RANDOM_BYTES() Return a random byte vector
RANK() Rank of current row within its partition, with gaps
REGEXP Whether string matches regular expression
REGEXP_INSTR() Starting index of substring matching regular expression
REGEXP_LIKE() Whether string matches regular expression
REGEXP_REPLACE() Replace substrings matching regular expression
REGEXP_SUBSTR() Return substring matching regular expression
RELEASE_ALL_LOCKS() Releases all current named locks
RELEASE_LOCK() Releases the named lock
REPEAT() Repeat a string the specified number of times
REPLACE() Replace occurrences of a specified string
REVERSE() Reverse the characters in a string
RIGHT() Return the specified rightmost number of characters
>> Right shift
RLIKE Whether string matches regular expression
ROLES_GRAPHML() Returns a GraphML document representing memory role subgraphs
ROUND() Round the argument
ROW_COUNT() The number of rows updated
ROW_NUMBER() Number of current row within its partition
RPAD() Append string the specified number of times
RTRIM() Remove trailing spaces
SCHEMA() Synonym for DATABASE()
SEC_TO_TIME() Converts seconds to 'HH:MM:SS' format
SECOND() Return the second (0-59)
SESSION_USER() Synonym for USER()
SHA1() , SHA() Calculate an SHA-1 160-bit checksum
SHA2() Calculate an SHA-2 checksum
SIGN() Return the sign of the argument
SIN() Return the sine of the argument
SLEEP() Sleep for a number of seconds
SOUNDEX() Return a soundex string
SOUNDS LIKE Compare sounds
SPACE() Return a string of the specified number of spaces
SQRT() Return the square root of the argument
ST_Area() Return Polygon or MultiPolygon area
ST_AsBinary() , ST_AsWKB() Convert from internal geometry format to WKB
ST_AsGeoJSON() Generate GeoJSON object from geometry
ST_AsText() , ST_AsWKT() Convert from internal geometry format to WKT
ST_Buffer() Return geometry of points within given distance from geometry
ST_Buffer_Strategy() Produce strategy option for ST_Buffer()
ST_Centroid() Return centroid as a point
ST_Contains() Whether one geometry contains another
ST_ConvexHull() Return convex hull of geometry
ST_Crosses() Whether one geometry crosses another
ST_Difference() Return point set difference of two geometries
ST_Dimension() Dimension of geometry
ST_Disjoint() Whether one geometry is disjoint from another
ST_Distance() The distance of one geometry from another
ST_Distance_Sphere() Minimum distance on earth between two geometries
ST_EndPoint() End Point of LineString
ST_Envelope() Return MBR of geometry
ST_Equals() Whether one geometry is equal to another
ST_ExteriorRing() Return exterior ring of Polygon
ST_GeoHash() Produce a geohash value
ST_GeomCollFromText() , ST_GeometryCollectionFromText() , ST_GeomCollFromTxt() Return geometry collection from WKT
ST_GeomCollFromWKB() , ST_GeometryCollectionFromWKB() Return geometry collection from WKB
ST_GeometryN() Return N-th geometry from geometry collection
ST_GeometryType() Return name of geometry type
ST_GeomFromGeoJSON() Generate geometry from GeoJSON object
ST_GeomFromText() , ST_GeometryFromText() Return geometry from WKT
ST_GeomFromWKB() , ST_GeometryFromWKB() Return geometry from WKB
ST_InteriorRingN() Return N-th interior ring of Polygon
ST_Intersection() Return point set intersection of two geometries
ST_Intersects() Whether one geometry intersects another
ST_IsClosed() Whether a geometry is closed and simple
ST_IsEmpty() Placeholder function
ST_IsSimple() Whether a geometry is simple
ST_IsValid() Whether a geometry is valid
ST_LatFromGeoHash() Return latitude from geohash value
ST_Latitude() Return latitude of Point
ST_Length() Return length of LineString
ST_LineFromText() , ST_LineStringFromText() Construct LineString from WKT
ST_LineFromWKB() , ST_LineStringFromWKB() Construct LineString from WKB
ST_LongFromGeoHash() Return longitude from geohash value
ST_Longitude() Return longitude of Point
ST_MakeEnvelope() Rectangle around two points
ST_MLineFromText() , ST_MultiLineStringFromText() Construct MultiLineString from WKT
ST_MLineFromWKB() , ST_MultiLineStringFromWKB() Construct MultiLineString from WKB
ST_MPointFromText() , ST_MultiPointFromText() Construct MultiPoint from WKT
ST_MPointFromWKB() , ST_MultiPointFromWKB() Construct MultiPoint from WKB
ST_MPolyFromText() , ST_MultiPolygonFromText() Construct MultiPolygon from WKT
ST_MPolyFromWKB() , ST_MultiPolygonFromWKB() Construct MultiPolygon from WKB
ST_NumGeometries() Return number of geometries in geometry collection
ST_NumInteriorRing() , ST_NumInteriorRings() Return number of interior rings in Polygon
ST_NumPoints() Return number of points in LineString
ST_Overlaps() Whether one geometry overlaps another
ST_PointFromGeoHash() Convert geohash value to POINT value
ST_PointFromText() Construct Point from WKT
ST_PointFromWKB() Construct Point from WKB
ST_PointN() Return N-th point from LineString
ST_PolyFromText() , ST_PolygonFromText() Construct Polygon from WKT
ST_PolyFromWKB() , ST_PolygonFromWKB() Construct Polygon from WKB
ST_Simplify() Return simplified geometry
ST_SRID() Return spatial reference system ID for geometry
ST_StartPoint() Start Point of LineString
ST_SwapXY() Return argument with X/Y coordinates swapped
ST_SymDifference() Return point set symmetric difference of two geometries
ST_Touches() Whether one geometry touches another
ST_Transform() Transform coordinates of geometry
ST_Union() Return point set union of two geometries
ST_Validate() Return validated geometry
ST_Within() Whether one geometry is within another
ST_X() Return X coordinate of Point
ST_Y() Return Y coordinate of Point
STATEMENT_DIGEST() Compute statement digest hash value
STATEMENT_DIGEST_TEXT() Compute normalized statement digest
STD() Return the population standard deviation
STDDEV() Return the population standard deviation
STDDEV_POP() Return the population standard deviation
STDDEV_SAMP() Return the sample standard deviation
STR_TO_DATE() Convert a string to a date
STRCMP() Compare two strings
SUBDATE() Synonym for DATE_SUB() when invoked with three arguments
SUBSTR() Return the substring as specified
SUBSTRING() Return the substring as specified
SUBSTRING_INDEX() Return a substring from a string before the specified number of occurrences of the delimiter
SUBTIME() Subtract times
SUM() Return the sum
SYSDATE() Return the time at which the function executes
SYSTEM_USER() Synonym for USER()
TAN() Return the tangent of the argument
TIME() Extract the time portion of the expression passed
TIME_FORMAT() Format as time
TIME_TO_SEC() Return the argument converted to seconds
TIMEDIFF() Subtract time
* Multiplication operator
TIMESTAMP() With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
TIMESTAMPADD() Add an interval to a datetime expression
TIMESTAMPDIFF() Subtract an interval from a datetime expression
TO_BASE64() Return the argument converted to a base-64 string
TO_DAYS() Return the date argument converted to days
TO_SECONDS() Return the date or datetime argument converted to seconds since Year 0
TRIM() Remove leading and trailing spaces
TRUNCATE() Truncate to specified number of decimal places
UCASE() Synonym for UPPER()
- Change the sign of the argument
UNCOMPRESS() Uncompress a string compressed
UNCOMPRESSED_LENGTH() Return the length of a string before compression
UNHEX() Return a string containing hex representation of a number
UNIX_TIMESTAMP() Return a Unix timestamp
UpdateXML() Return replaced XML fragment
UPPER() Convert to uppercase
USER() The user name and host name provided by the client
UTC_DATE() Return the current UTC date
UTC_TIME() Return the current UTC time
UTC_TIMESTAMP() Return the current UTC date and time
UUID() Return a Universal Unique Identifier (UUID)
UUID_SHORT() Return an integer-valued universal identifier
UUID_TO_BIN() Convert string UUID to binary
VALIDATE_PASSWORD_STRENGTH() Determine strength of password
VALUES() Defines the values to be used during an INSERT
VAR_POP() Return the population standard variance
VAR_SAMP() Return the sample variance
VARIANCE() Return the population standard variance
VERSION() Return a string that indicates the MySQL server version
WAIT_FOR_EXECUTED_GTID_SET() Wait until the given GTIDs have executed on slave.
WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS() Wait until the given GTIDs have executed on slave.
WEEK() Return the week number
WEEKDAY() Return the weekday index
WEEKOFYEAR() Return the calendar week of the date (1-53)
WEIGHT_STRING() Return the weight string for a string
XOR Logical XOR
YEAR() Return the year
YEARWEEK() Return the year and week

12.2 Type Conversion in Expression Evaluation

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts strings to numbers as necessary, and vice versa.

mysql> SELECT 1+'1';
        -> 2
mysql> SELECT CONCAT(2,' test');
        -> '2 test'
                    

It is also possible to convert a number to a string explicitly using the CAST() function. Conversion occurs implicitly with the CONCAT() function because it expects string arguments.

mysql> SELECT 38.8, CAST(38.8 AS CHAR);
        -> 38.8, '38.8'
mysql> SELECT 38.8, CONCAT(38.8);
        -> 38.8, '38.8'
                    

See later in this section for information about the character set of implicit number-to-string conversions, and for modified rules that apply to CREATE TABLE ... SELECT statements.

The following rules describe how conversion occurs for comparison operations:

  • If one or both arguments are NULL , the result of the comparison is NULL , except for the NULL -safe <=> equality comparison operator. For NULL <=> NULL , the result is true. No conversion is needed.

  • If both arguments in a comparison operation are strings, they are compared as strings.

  • If both arguments are integers, they are compared as integers.

  • Hexadecimal values are treated as binary strings if not compared to a number.

  • If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. This is not done for the arguments to IN() . To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.

    A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, use CAST() to explicitly convert the subquery value to DATETIME .

  • If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.

  • In all other cases, the arguments are compared as floating-point (real) numbers.

For information about conversion of values from one temporal type to another, see Section 11.3.7, “Conversion Between Date and Time Types” .

Comparison of JSON values takes place at two levels. The first level of comparison is based on the JSON types of the compared values. If the types differ, the comparison result is determined solely by which type has higher precedence. If the two values have the same JSON type, a second level of comparison occurs using type-specific rules. For comparison of JSON and non-JSON values, the non-JSON value is converted to JSON and the values compared as JSON values. For details, see Comparison and Ordering of JSON Values .

The following examples illustrate conversion of strings to numbers for comparison operations:

mysql> SELECT 1 > '6x';
        -> 0
mysql> SELECT 7 > '6x';
        -> 1
mysql> SELECT 0 > 'x6';
        -> 0
mysql> SELECT 0 = 'x6';
        -> 1
                    

For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If str_col is an indexed string column, the index cannot be used when performing the lookup in the following statement:

SELECT * FROM tbl_name WHERE str_col=1;
                    

The reason for this is that there are many different strings that may convert to the value 1 , such as '1' , ' 1' , or '1a' .

Comparisons that use floating-point numbers (or values that are converted to floating-point numbers) are approximate because such numbers are inexact. This might lead to results that appear inconsistent:

mysql> SELECT '18015376320243458' = 18015376320243458;
        -> 1
mysql> SELECT '18015376320243459' = 18015376320243459;
        -> 0
                    

Such results can occur because the values are converted to floating-point numbers, which have only 53 bits of precision and are subject to rounding:

mysql> SELECT '18015376320243459'+0.0;
        -> 1.8015376320243e+16
                    

Furthermore, the conversion from string to floating-point and from integer to floating-point do not necessarily occur the same way. The integer may be converted to floating-point by the CPU, whereas the string is converted digit by digit in an operation that involves floating-point multiplications.

The results shown will vary on different systems, and can be affected by factors such as computer architecture or the compiler version or optimization level. One way to avoid such problems is to use CAST() so that a value is not converted implicitly to a float-point number:

mysql> SELECT CAST('18015376320243459' AS UNSIGNED) = 18015376320243459;
        -> 1
                    

For more information about floating-point comparisons, see Section B.6.4.8, “Problems with Floating-Point Values” .

The server includes dtoa , a conversion library that provides the basis for improved conversion between string or DECIMAL values and approximate-value ( FLOAT / DOUBLE ) numbers:

  • Consistent conversion results across platforms, which eliminates, for example, Unix versus Windows conversion differences.

  • Accurate representation of values in cases where results previously did not provide sufficient precision, such as for values close to IEEE limits.

  • Conversion of numbers to string format with the best possible precision. The precision of dtoa is always the same or better than that of the standard C library functions.

Because the conversions produced by this library differ in some cases from non- dtoa results, the potential exists for incompatibilities in applications that rely on previous results. For example, applications that depend on a specific exact result from previous conversions might need adjustment to accommodate additional precision.

The dtoa library provides conversions with the following properties. D represents a value with a DECIMAL or string representation, and F represents a floating-point number in native binary (IEEE) format.

  • F -> D conversion is done with the best possible precision, returning D as the shortest string that yields F when read back in and rounded to the nearest value in native binary format as specified by IEEE.

  • D -> F conversion is done such that F is the nearest native binary number to the input decimal string D .

These properties imply that F -> D -> F conversions are lossless unless F is -inf , +inf , or NaN . The latter values are not supported because the SQL standard defines them as invalid values for FLOAT or DOUBLE .

For D -> F -> D conversions, a sufficient condition for losslessness is that D uses 15 or fewer digits of precision, is not a denormal value, -inf , +inf , or NaN . In some cases, the conversion is lossless even if D has more than 15 digits of precision, but this is not always the case.

Implicit conversion of a numeric or temporal value to string produces a value that has a character set and collation determined by the character_set_connection and collation_connection system variables. (These variables commonly are set with SET NAMES . For information about connection character sets, see Section 10.4, “Connection Character Sets and Collations” .)

This means that such a conversion results in a character (nonbinary) string (a CHAR , VARCHAR , or LONGTEXT value), except in the case that the connection character set is set to binary . In that case, the conversion result is a binary string (a BINARY , VARBINARY , or LONGBLOB value).

For integer expressions, the preceding remarks about expression evaluation apply somewhat differently for expression assignment ; for example, in a statement such as this:

CREATE TABLE t SELECT integer_expr;
                    

In this case, the table in the column resulting from the expression has type INT or BIGINT depending on the length of the integer expression. If the maximum length of the expression does not fit in an INT , BIGINT is used instead. The length is taken from the max_length value of the SELECT result set metadata (see Section 28.7.5, “C API Data Structures” ). This means that you can force a BIGINT rather than INT by use of a sufficiently long expression:

CREATE TABLE t SELECT 000000000000000000000;
                    

12.3 Operators

Table 12.2 Operators

Name 描述
AND , && Logical AND
= Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement)
:= Assign a value
BETWEEN ... AND ... Check whether a value is within a range of values
BINARY Cast a string to a binary string
& Bitwise AND
~ Bitwise inversion
| Bitwise OR
^ Bitwise XOR
CASE Case operator
DIV Integer division
/ Division operator
= Equal operator
<=> NULL-safe equal to operator
> Greater than operator
>= Greater than or equal operator
IS Test a value against a boolean
IS NOT Test a value against a boolean
IS NOT NULL NOT NULL value test
IS NULL NULL value test
-> Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
->> Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
<< Left shift
< Less than operator
<= Less than or equal operator
LIKE Simple pattern matching
- Minus operator
% , MOD Modulo operator
NOT , ! Negates value
NOT BETWEEN ... AND ... Check whether a value is not within a range of values
!= , <> Not equal operator
NOT LIKE Negation of simple pattern matching
NOT REGEXP Negation of REGEXP
|| , OR Logical OR
+ Addition operator
REGEXP Whether string matches regular expression
>> Right shift
RLIKE Whether string matches regular expression
SOUNDS LIKE Compare sounds
* Multiplication operator
- Change the sign of the argument
XOR Logical XOR

12.3.1 Operator Precedence

Operator precedences are shown in the following list, from highest precedence to the lowest. Operators that are shown together on a line have the same precedence.

INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
AND, &&
XOR
OR, ||
= (assignment), :=
                        

The precedence of = depends on whether it is used as a comparison operator ( = ) or as an assignment operator ( = ). When used as a comparison operator, it has the same precedence as <=> , >= , > , <= , < , <> , != , IS , LIKE , REGEXP , and IN . When used as an assignment operator, it has the same precedence as := . Section 13.7.5.1, “SET Syntax for Variable Assignment” , and Section 9.4, “User-Defined Variables” , explain how MySQL determines which interpretation of = should apply.

For operators that occur at the same precedence level within an expression, evaluation proceeds left to right, with the exception that assignments evaluate right to left.

The precedence and meaning of some operators depends on the SQL mode:

See Section 5.1.11, “Server SQL Modes” .

The precedence of operators determines the order of evaluation of terms in an expression. To override this order and group terms explicitly, use parentheses. For example:

mysql> SELECT 1+2*3;
        -> 7
mysql> SELECT (1+2)*3;
        -> 9
                        

12.3.2 Comparison Functions and Operators

Table 12.3 Comparison Operators

Name 描述
BETWEEN ... AND ... Check whether a value is within a range of values
COALESCE() Return the first non-NULL argument
= Equal operator
<=> NULL-safe equal to operator
> Greater than operator
>= Greater than or equal operator
GREATEST() Return the largest argument
IN() Check whether a value is within a set of values
INTERVAL() Return the index of the argument that is less than the first argument
IS Test a value against a boolean
IS NOT Test a value against a boolean
IS NOT NULL NOT NULL value test
IS NULL NULL value test
ISNULL() Test whether the argument is NULL
LEAST() Return the smallest argument
< Less than operator
<= Less than or equal operator
LIKE Simple pattern matching
NOT BETWEEN ... AND ... Check whether a value is not within a range of values
!= , <> Not equal operator
NOT IN() Check whether a value is not within a set of values
NOT LIKE Negation of simple pattern matching
STRCMP() Compare two strings

Comparison operations result in a value of 1 ( TRUE ), 0 ( FALSE ), or NULL . These operations work for both numbers and strings. Strings are automatically converted to numbers and numbers to strings as necessary.

The following relational comparison operators can be used to compare not only scalar operands, but row operands:

=  >  <  >=  <=  <>  !=
                        

The descriptions for those operators later in this section detail how they work with row operands. For additional examples of row comparisons in the context of row subqueries, see Section 13.2.11.5, “Row Subqueries” .

Some of the functions in this section return values other than 1 ( TRUE ), 0 ( FALSE ), or NULL . LEAST() and GREATEST() are examples of such functions; Section 12.2, “Type Conversion in Expression Evaluation” , describes the rules for comparison operations performed by these and similar functions for determining their return values.

Note

In previous versions of MySQL, when evaluating an expression containing LEAST() or GREATEST() , the server attempted to guess the context in which the function was used, and to coerce the function's arguments to the data type of the expression as a whole. For example, the arguments to LEAST("11", "45", "2") are evaluated and sorted as strings, so that this expression returns "11" . In MySQL 8.0.3 and earlier, when evaluating the expression LEAST("11", "45", "2") + 0 , the server converted the arguments to integers (anticipating the addition of integer 0 to the result) before sorting them, thus returning 2.

Beginning with MySQL 8.0.4, the server no longer attempts to infer context in this fashion. Instead, the function is executed using the arguments as provided, performing data type conversions to one or more of the arguments if and only if they are not all of the same type. Any type coercion mandated by an expression that makes use of the return value is now performed following function execution. This means that, in MySQl 8.0.4 and later, LEAST("11", "45", "2") + 0 evaluates to "11" + 0 and thus to integer 11. (Bug #83895, Bug #25123839)

To convert a value to a specific type for comparison purposes, you can use the CAST() function. String values can be converted to a different character set using CONVERT() . See Section 12.10, “Cast Functions and Operators” .

By default, string comparisons are not case-sensitive and use the current character set. The default is utf8mb4 .

  • =

    Equal:

    mysql> SELECT 1 = 0;
            -> 0
    mysql> SELECT '0' = 0;
            -> 1
    mysql> SELECT '0.0' = 0;
            -> 1
    mysql> SELECT '0.01' = 0;
            -> 0
    mysql> SELECT '.01' = 0.01;
            -> 1
                                        

    For row comparisons, (a, b) = (x, y) is equivalent to:

    (a = x) AND (b = y)
                                        
  • <=>

    NULL -safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL , and 0 rather than NULL if one operand is NULL .

    The <=> operator is equivalent to the standard SQL IS NOT DISTINCT FROM operator.

    mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
            -> 1, 1, 0
    mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
            -> 1, NULL, NULL
                                        

    For row comparisons, (a, b) <=> (x, y) is equivalent to:

    (a <=> x) AND (b <=> y)
                                        
  • <> , !=

    Not equal:

    mysql> SELECT '.01' <> '0.01';
            -> 1
    mysql> SELECT .01 <> '0.01';
            -> 0
    mysql> SELECT 'zapp' <> 'zappp';
            -> 1
                                        

    For row comparisons, (a, b) <> (x, y) and (a, b) != (x, y) are equivalent to:

    (a <> x) OR (b <> y)
                                        
  • <=

    Less than or equal:

    mysql> SELECT 0.1 <= 2;
            -> 1
                                        

    For row comparisons, (a, b) <= (x, y) is equivalent to:

    (a < x) OR ((a = x) AND (b <= y))
                                        
  • <

    Less than:

    mysql> SELECT 2 < 2;
            -> 0
                                        

    For row comparisons, (a, b) < (x, y) is equivalent to:

    (a < x) OR ((a = x) AND (b < y))
                                        
  • >=

    Greater than or equal:

    mysql> SELECT 2 >= 2;
            -> 1
                                        

    For row comparisons, (a, b) >= (x, y) is equivalent to:

    (a > x) OR ((a = x) AND (b >= y))
                                        
  • >

    Greater than:

    mysql> SELECT 2 > 2;
            -> 0
                                        

    For row comparisons, (a, b) > (x, y) is equivalent to:

    (a > x) OR ((a = x) AND (b > y))
                                        
  • IS boolean_value

    Tests a value against a boolean value, where boolean_value can be TRUE , FALSE , or UNKNOWN .

    mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
            -> 1, 1, 1
                                        
  • IS NOT boolean_value

    Tests a value against a boolean value, where boolean_value can be TRUE , FALSE , or UNKNOWN .

    mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
            -> 1, 1, 0
                                        
  • IS NULL

    Tests whether a value is NULL .

    mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
            -> 0, 0, 1
                                        

    To work well with ODBC programs, MySQL supports the following extra features when using IS NULL :

  • IS NOT NULL

    Tests whether a value is not NULL .

    mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
            -> 1, 1, 0
                                        
  • expr BETWEEN min AND max

    If expr is greater than or equal to min and expr is less than or equal to max , BETWEEN returns 1 , otherwise it returns 0 . This is equivalent to the expression ( min <= expr AND expr <= max ) if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 12.2, “Type Conversion in Expression Evaluation” , but applied to all the three arguments.

    mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;
            -> 1, 0
    mysql> SELECT 1 BETWEEN 2 AND 3;
            -> 0
    mysql> SELECT 'b' BETWEEN 'a' AND 'c';
            -> 1
    mysql> SELECT 2 BETWEEN 2 AND '3';
            -> 1
    mysql> SELECT 2 BETWEEN 2 AND 'x-3';
            -> 0
                                        

    For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE , cast the string to a DATE .

  • expr NOT BETWEEN min AND max

    This is the same as NOT ( expr BETWEEN min AND max ) .

  • COALESCE( value ,...)

    Returns the first non- NULL value in the list, or NULL if there are no non- NULL values.

    The return type of COALESCE() is the aggregated type of the argument types.

    mysql> SELECT COALESCE(NULL,1);
            -> 1
    mysql> SELECT COALESCE(NULL,NULL,NULL);
            -> NULL
                                        
  • GREATEST( value1 , value2 ,...)

    With two or more arguments, returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for LEAST() .

    mysql> SELECT GREATEST(2,0);
            -> 2
    mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
            -> 767.0
    mysql> SELECT GREATEST('B','A','C');
            -> 'C'
                                        

    GREATEST() returns NULL if any argument is NULL .

  • expr IN ( value ,...)

    Returns 1 if expr is equal to any of the values in the IN list, else returns 0 . If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. Otherwise, type conversion takes place according to the rules described in Section 12.2, “Type Conversion in Expression Evaluation” , but applied to all the arguments.

    mysql> SELECT 2 IN (0,3,5,7);
            -> 0
    mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
            -> 1
                                        

    IN can be used to compare row constructors:

    mysql> SELECT (3,4) IN ((1,2), (3,4));
            -> 1
    mysql> SELECT (3,4) IN ((1,2), (3,5));
            -> 0
                                        

    You should never mix quoted and unquoted values in an IN list because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) differ. Mixing types may therefore lead to inconsistent results. For example, do not write an IN expression like this:

    SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');
                                        

    Instead, write it like this:

    SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');
                                        

    The number of values in the IN list is only limited by the max_allowed_packet value.

    To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL , but also if no match is found in the list and one of the expressions in the list is NULL .

    IN() syntax can also be used to write certain types of subqueries. See Section 13.2.11.3, “Subqueries with ANY, IN, or SOME” .

  • expr NOT IN ( value ,...)

    This is the same as NOT ( expr IN ( value ,...)) .

  • ISNULL( expr )

    If expr is NULL , ISNULL() returns 1 , otherwise it returns 0 .

    mysql> SELECT ISNULL(1+1);
            -> 0
    mysql> SELECT ISNULL(1/0);
            -> 1
                                        

    ISNULL() can be used instead of = to test whether a value is NULL . (Comparing a value to NULL using = always yields NULL .)

    The ISNULL() function shares some special behaviors with the IS NULL comparison operator. See the description of IS NULL .

  • INTERVAL( N , N1 , N2 , N3 ,...)

    Returns 0 if N < N1 , 1 if N < N2 and so on or -1 if N is NULL . All arguments are treated as integers. It is required that N1 < N2 < N3 < ... < Nn for this function to work correctly. This is because a binary search is used (very fast).

    mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
            -> 3
    mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
            -> 2
    mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
            -> 0
                                        
  • LEAST( value1 , value2 ,...)

    With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:

    • If any argument is NULL , the result is NULL . No comparison is needed.

    • If all arguments are integer-valued, they are compared as integers.

    • If at least one argument is double precision, they are compared as double-precision values. Otherwise, if at least one argument is a DECIMAL value, they are compared as DECIMAL values.

    • If the arguments comprise a mix of numbers and strings, they are compared as numbers.

    • If any argument is a nonbinary (character) string, the arguments are compared as nonbinary strings.

    • In all other cases, the arguments are compared as binary strings.

    The return type of LEAST() is the aggregated type of the comparison argument types.

    mysql> SELECT LEAST(2,0);
            -> 0
    mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
            -> 3.0
    mysql> SELECT LEAST('B','A','C');
            -> 'A'
                                        

12.3.3 Logical Operators

Table 12.4 Logical Operators

Name 描述
AND , && Logical AND
NOT , ! Negates value
|| , OR Logical OR
XOR Logical XOR

In SQL, all logical operators evaluate to TRUE , FALSE , or NULL ( UNKNOWN ). In MySQL, these are implemented as 1 ( TRUE ), 0 ( FALSE ), and NULL . Most of this is common to different SQL database servers, although some servers may return any nonzero value for TRUE .

MySQL evaluates any nonzero, non- NULL value to TRUE . For example, the following statements all assess to TRUE :

mysql> SELECT 10 IS TRUE;
-> 1
mysql> SELECT -10 IS TRUE;
-> 1
mysql> SELECT 'string' IS NOT NULL;
-> 1
                        
  • NOT , !

    Logical NOT. Evaluates to 1 if the operand is 0 , to 0 if the operand is nonzero, and NOT NULL returns NULL .

    mysql> SELECT NOT 10;
            -> 0
    mysql> SELECT NOT 0;
            -> 1
    mysql> SELECT NOT NULL;
            -> NULL
    mysql> SELECT ! (1+1);
            -> 0
    mysql> SELECT ! 1+1;
            -> 1
                                        

    The last example produces 1 because the expression evaluates the same way as (!1)+1 .

  • AND , &&

    Logical AND. Evaluates to 1 if all operands are nonzero and not NULL , to 0 if one or more operands are 0 , otherwise NULL is returned.

    mysql> SELECT 1 AND 1;
            -> 1
    mysql> SELECT 1 AND 0;
            -> 0
    mysql> SELECT 1 AND NULL;
            -> NULL
    mysql> SELECT 0 AND NULL;
            -> 0
    mysql> SELECT NULL AND 0;
            -> 0
                                        
  • OR , ||

    Logical OR. When both operands are non- NULL , the result is 1 if any operand is nonzero, and 0 otherwise. With a NULL operand, the result is 1 if the other operand is nonzero, and NULL otherwise. If both operands are NULL , the result is NULL .

    mysql> SELECT 1 OR 1;
            -> 1
    mysql> SELECT 1 OR 0;
            -> 1
    mysql> SELECT 0 OR 0;
            -> 0
    mysql> SELECT 0 OR NULL;
            -> NULL
    mysql> SELECT 1 OR NULL;
            -> 1
                                        
  • XOR

    Logical XOR. Returns NULL if either operand is NULL . For non- NULL operands, evaluates to 1 if an odd number of operands is nonzero, otherwise 0 is returned.

    mysql> SELECT 1 XOR 1;
            -> 0
    mysql> SELECT 1 XOR 0;
            -> 1
    mysql> SELECT 1 XOR NULL;
            -> NULL
    mysql> SELECT 1 XOR 1 XOR 1;
            -> 1
                                        

    a XOR b is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b) .

12.3.4 Assignment Operators

Table 12.5 Assignment Operators

Name 描述
= Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement)
:= Assign a value

  • :=

    Assignment operator. Causes the user variable on the left hand side of the operator to take on the value to its right. The value on the right hand side may be a literal value, another variable storing a value, or any legal expression that yields a scalar value, including the result of a query (provided that this value is a scalar value). You can perform multiple assignments in the same SET statement. You can perform multiple assignments in the same statement.

    Unlike = , the := operator is never interpreted as a comparison operator. This means you can use := in any valid SQL statement (not just in SET statements) to assign a value to a variable.

    mysql> SELECT @var1, @var2;
            -> NULL, NULL
    mysql> SELECT @var1 := 1, @var2;
            -> 1, NULL
    mysql> SELECT @var1, @var2;
            -> 1, NULL
    mysql> SELECT @var1, @var2 := @var1;
            -> 1, 1
    mysql> SELECT @var1, @var2;
            -> 1, 1
    mysql> SELECT @var1:=COUNT(*) FROM t1;
            -> 4
    mysql> SELECT @var1;
            -> 4
                                        

    You can make value assignments using := in other statements besides SELECT , such as UPDATE , as shown here:

    mysql> SELECT @var1;
            -> 4
    mysql> SELECT * FROM t1;
            -> 1, 3, 5, 7
    mysql> UPDATE t1 SET c1 = 2 WHERE c1 = @var1:= 1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    mysql> SELECT @var1;
            -> 1
    mysql> SELECT * FROM t1;
            -> 2, 3, 5, 7
                                        

    While it is also possible both to set and to read the value of the same variable in a single SQL statement using the := operator, this is not recommended. Section 9.4, “User-Defined Variables” , explains why you should avoid doing this.

  • =

    This operator is used to perform value assignments in two cases, described in the next two paragraphs.

    Within a SET statement, = is treated as an assignment operator that causes the user variable on the left hand side of the operator to take on the value to its right. (In other words, when used in a SET statement, = is treated identically to := .) The value on the right hand side may be a literal value, another variable storing a value, or any legal expression that yields a scalar value, including the result of a query (provided that this value is a scalar value). You can perform multiple assignments in the same SET statement.

    In the SET clause of an UPDATE statement, = also acts as an assignment operator; in this case, however, it causes the column named on the left hand side of the operator to assume the value given to the right, provided any WHERE conditions that are part of the UPDATE are met. You can make multiple assignments in the same SET clause of an UPDATE statement.

    In any other context, = is treated as a comparison operator .

    mysql> SELECT @var1, @var2;
            -> NULL, NULL
    mysql> SELECT @var1 := 1, @var2;
            -> 1, NULL
    mysql> SELECT @var1, @var2;
            -> 1, NULL
    mysql> SELECT @var1, @var2 := @var1;
            -> 1, 1
    mysql> SELECT @var1, @var2;
            -> 1, 1
                                        

    更多信息,请见 Section 13.7.5.1, “SET Syntax for Variable Assignment” , Section 13.2.12, “UPDATE Syntax” , and Section 13.2.11, “Subquery Syntax” .

12.4 Control Flow Functions

Table 12.6 Flow Control Operators

Name 描述
CASE Case operator
IF() If/else construct
IFNULL() Null if/else construct
NULLIF() Return NULL if expr1 = expr2

  • CASE value WHEN [ compare_value ] THEN result [WHEN [ compare_value ] THEN result ...] [ELSE result ] END

    CASE WHEN [ condition ] THEN result [WHEN [ condition ] THEN result ...] [ELSE result ] END

    The first CASE syntax returns the result for the first value = compare_value comparison that is true. The second syntax returns the result for the first condition that is true. If no comparison or condition is true, the result after ELSE is returned, or NULL if there is no ELSE part.

    Note

    The syntax of the CASE expr described here differs slightly from that of the SQL CASE statement described in Section 13.6.5.1, “CASE Syntax” , for use inside stored programs. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END .

    The return type of a CASE expression result is the aggregated type of all result values:

    • If all types are numeric, the aggregated type is also numeric:

      • If at least one argument is double precision, the result is double precision.

      • Otherwise, if at least one argument is DECIMAL , the result is DECIMAL .

      • Otherwise, the result is an integer type (with one exception):

        • If all integer types are all signed or all unsigned, the result is the same sign and the precision is the highest of all specified integer types (that is, TINYINT , SMALLINT , MEDIUMINT , INT , or BIGINT ).

        • If there is a combination of signed and unsigned integer types, the result is signed and the precision may be higher. For example, if the types are signed INT and unsigned INT , the result is signed BIGINT .

        • The exception is unsigned BIGINT combined with any signed integer type. The result is DECIMAL with sufficient precision and scale 0.

    • If all types are BIT , the result is BIT . Otherwise, BIT arguments are treated similar to BIGINT .

    • If all types are YEAR , the result is YEAR . Otherwise, YEAR arguments are treated similar to INT .

    • If all types are character string ( CHAR or VARCHAR ), the result is VARCHAR with maximum length determined by the longest character length of the operands.

    • If all types are character or binary string, the result is VARBINARY .

    • SET and ENUM are treated similar to VARCHAR ; the result is VARCHAR .

    • If all types are JSON , the result is JSON .

    • If all types are temporal, the result is temporal:

    • If all types are GEOMETRY , the result is GEOMETRY .

    • If any type is BLOB , the result is BLOB .

    • For all other type combinations, the result is VARCHAR .

    • Literal NULL operands are ignored for type aggregation.

    mysql> SELECT CASE 1 WHEN 1 THEN 'one'
        ->     WHEN 2 THEN 'two' ELSE 'more' END;
            -> 'one'
    mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
            -> 'true'
    mysql> SELECT CASE BINARY 'B'
        ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
            -> NULL
                                    
  • IF( expr1 , expr2 , expr3 )

    If expr1 is TRUE ( expr1 <> 0 and expr1 <> NULL ), IF() returns expr2 . Otherwise, it returns expr3 .

    Note

    There is also an IF statement , which differs from the IF() function described here. See Section 13.6.5.2, “IF Syntax” .

    If only one of expr2 or expr3 is explicitly NULL , the result type of the IF() function is the type of the non- NULL expression.

    The default return type of IF() (which may matter when it is stored into a temporary table) is calculated as follows:

    • If expr2 or expr3 produce a string, the result is a string.

      If expr2 and expr3 are both strings, the result is case-sensitive if either string is case sensitive.

    • If expr2 or expr3 produce a floating-point value, the result is a floating-point value.

    • If expr2 or expr3 produce an integer, the result is an integer.

    mysql> SELECT IF(1>2,2,3);
            -> 3
    mysql> SELECT IF(1<2,'yes','no');
            -> 'yes'
    mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
            -> 'no'
                                    
  • IFNULL( expr1 , expr2 )

    If expr1 is not NULL , IFNULL() returns expr1 ; otherwise it returns expr2 .

    mysql> SELECT IFNULL(1,0);
            -> 1
    mysql> SELECT IFNULL(NULL,10);
            -> 10
    mysql> SELECT IFNULL(1/0,10);
            -> 10
    mysql> SELECT IFNULL(1/0,'yes');
            -> 'yes'
                                    

    The default return type of IFNULL( expr1 , expr2 ) is the more general of the two expressions, in the order STRING , REAL , or INTEGER . Consider the case of a table based on expressions or where MySQL must internally store a value returned by IFNULL() in a temporary table:

    mysql> CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
    mysql> DESCRIBE tmp;
    +-------+--------------+------+-----+---------+-------+
    | Field | Type         | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | test  | varbinary(4) | NO   |     |         |       |
    +-------+--------------+------+-----+---------+-------+
                                    

    In this example, the type of the test column is VARBINARY(4) (a string type).

  • NULLIF( expr1 , expr2 )

    Returns NULL if expr1 = expr2 is true, otherwise returns expr1 . This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END .

    The return value has the same type as the first argument.

    mysql> SELECT NULLIF(1,1);
            -> NULL
    mysql> SELECT NULLIF(1,2);
            -> 1
                                    
    Note

    MySQL evaluates expr1 twice if the arguments are not equal.

12.5 String Functions

Table 12.7 String Operators

Name 描述
ASCII() Return numeric value of left-most character
BIN() Return a string containing binary representation of a number
BIT_LENGTH() Return length of argument in bits
CHAR() Return the character for each integer passed
CHAR_LENGTH() Return number of characters in argument
CHARACTER_LENGTH() Synonym for CHAR_LENGTH()
CONCAT() Return concatenated string
CONCAT_WS() Return concatenate with separator
ELT() Return string at index number
EXPORT_SET() Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
FIELD() Return the index (position) of the first argument in the subsequent arguments
FIND_IN_SET() Return the index position of the first argument within the second argument
FORMAT() Return a number formatted to specified number of decimal places
FROM_BASE64() Decode base64 encoded string and return result
HEX() Return a hexadecimal representation of a decimal or string value
INSERT() Insert a substring at the specified position up to the specified number of characters
INSTR() Return the index of the first occurrence of substring
LCASE() Synonym for LOWER()
LEFT() Return the leftmost number of characters as specified
LENGTH() Return the length of a string in bytes
LIKE Simple pattern matching
LOAD_FILE() Load the named file
LOCATE() Return the position of the first occurrence of substring
LOWER() Return the argument in lowercase
LPAD() Return the string argument, left-padded with the specified string
LTRIM() Remove leading spaces
MAKE_SET() Return a set of comma-separated strings that have the corresponding bit in bits set
MATCH Perform full-text search
MID() Return a substring starting from the specified position
NOT LIKE Negation of simple pattern matching
NOT REGEXP Negation of REGEXP
OCT() Return a string containing octal representation of a number
OCTET_LENGTH() Synonym for LENGTH()
ORD() Return character code for leftmost character of the argument
POSITION() Synonym for LOCATE()
QUOTE() Escape the argument for use in an SQL statement
REGEXP Whether string matches regular expression
REGEXP_INSTR() Starting index of substring matching regular expression
REGEXP_LIKE() Whether string matches regular expression
REGEXP_REPLACE() Replace substrings matching regular expression
REGEXP_SUBSTR() Return substring matching regular expression
REPEAT() Repeat a string the specified number of times
REPLACE() Replace occurrences of a specified string
REVERSE() Reverse the characters in a string
RIGHT() Return the specified rightmost number of characters
RLIKE Whether string matches regular expression
RPAD() Append string the specified number of times
RTRIM() Remove trailing spaces
SOUNDEX() Return a soundex string
SOUNDS LIKE Compare sounds
SPACE() Return a string of the specified number of spaces
STRCMP() Compare two strings
SUBSTR() Return the substring as specified
SUBSTRING() Return the substring as specified
SUBSTRING_INDEX() Return a substring from a string before the specified number of occurrences of the delimiter
TO_BASE64() Return the argument converted to a base-64 string
TRIM() Remove leading and trailing spaces
UCASE() Synonym for UPPER()
UNHEX() Return a string containing hex representation of a number
UPPER() Convert to uppercase
WEIGHT_STRING() Return the weight string for a string

String-valued functions return NULL if the length of the result would be greater than the value of the max_allowed_packet system variable. See Section 5.1.1, “Configuring the Server” .

For functions that operate on string positions, the first position is numbered 1.

For functions that take length arguments, noninteger arguments are rounded to the nearest integer.

  • ASCII( str )

    Returns the numeric value of the leftmost character of the string str . Returns 0 if str is the empty string. Returns NULL if str is NULL . ASCII() works for 8-bit characters.

    mysql> SELECT ASCII('2');
            -> 50
    mysql> SELECT ASCII(2);
            -> 50
    mysql> SELECT ASCII('dx');
            -> 100
                                    

    See also the ORD() function.

  • BIN( N )

    Returns a string representation of the binary value of N , where N is a longlong ( BIGINT ) number. This is equivalent to CONV( N ,10,2) . Returns NULL if N is NULL .

    mysql> SELECT BIN(12);
            -> '1100'
                                    
  • BIT_LENGTH( str )

    Returns the length of the string str in bits.

    mysql> SELECT BIT_LENGTH('text');
            -> 32
                                    
  • CHAR( N ,... [USING charset_name ])

    CHAR() interprets each argument N as an integer and returns a string consisting of the characters given by the code values of those integers. NULL values are skipped.

    mysql> SELECT CHAR(77,121,83,81,'76');
            -> 'MySQL'
    mysql> SELECT CHAR(77,77.3,'77.3');
            -> 'MMM'
                                    

    CHAR() arguments larger than 255 are converted into multiple result bytes. For example, CHAR(256) is equivalent to CHAR(1,0) , and CHAR(256*256) is equivalent to CHAR(1,0,0) :

    mysql> SELECT HEX(CHAR(1,0)), HEX(CHAR(256));
    +----------------+----------------+
    | HEX(CHAR(1,0)) | HEX(CHAR(256)) |
    +----------------+----------------+
    | 0100           | 0100           |
    +----------------+----------------+
    mysql> SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256*256));
    +------------------+--------------------+
    | HEX(CHAR(1,0,0)) | HEX(CHAR(256*256)) |
    +------------------+--------------------+
    | 010000           | 010000             |
    +------------------+--------------------+
                                    

    By default, CHAR() returns a binary string. To produce a string in a given character set, use the optional USING clause:

    mysql> SELECT CHARSET(CHAR(X'65')), CHARSET(CHAR(X'65' USING utf8));
    +----------------------+---------------------------------+
    | CHARSET(CHAR(X'65')) | CHARSET(CHAR(X'65' USING utf8)) |
    +----------------------+---------------------------------+
    | binary               | utf8                            |
    +----------------------+---------------------------------+
                                    

    If USING is given and the result string is illegal for the given character set, a warning is issued. Also, if strict SQL mode is enabled, the result from CHAR() becomes NULL .

  • CHAR_LENGTH( str )

    Returns the length of the string str , measured in characters. A multibyte character counts as a single character. This means that for a string containing five 2-byte characters, LENGTH() returns 10 , whereas CHAR_LENGTH() returns 5 .

  • CHARACTER_LENGTH( str )

    CHARACTER_LENGTH() is a synonym for CHAR_LENGTH() .

  • CONCAT( str1 , str2 ,...)

    Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form.

    CONCAT() returns NULL if any argument is NULL .

    mysql> SELECT CONCAT('My', 'S', 'QL');
            -> 'MySQL'
    mysql> SELECT CONCAT('My', NULL, 'QL');
            -> NULL
    mysql> SELECT CONCAT(14.3);
            -> '14.3'
                                    

    For quoted strings, concatenation can be performed by placing the strings next to each other:

    mysql> SELECT 'My' 'S' 'QL';
            -> 'MySQL'
                                    
  • CONCAT_WS( separator , str1 , str2 ,...)

    CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT() . The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL , the result is NULL .

    mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
            -> 'First name,Second name,Last Name'
    mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
            -> 'First name,Last Name'
                                    

    CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

  • ELT( N , str1 , str2 , str3 ,...)

    ELT() returns the N th element of the list of strings: str1 if N = 1 , str2 if N = 2 , and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD() .

    mysql> SELECT ELT(1, 'Aa', 'Bb', 'Cc', 'Dd');
            -> 'Aa'
    mysql> SELECT ELT(4, 'Aa', 'Bb', 'Cc', 'Dd');
            -> 'Dd'
                                    
  • EXPORT_SET( bits , on , off [, separator [, number_of_bits ]])

    Returns a string such that for every bit set in the value bits , you get an on string and for every bit not set in the value, you get an off string. Bits in bits are examined from right to left (from low-order to high-order bits). Strings are added to the result from left to right, separated by the separator string (the default being the comma character , ). The number of bits examined is given by number_of_bits , which has a default of 64 if not specified. number_of_bits is silently clipped to 64 if larger than 64. It is treated as an unsigned integer, so a value of −1 is effectively the same as 64.

    mysql> SELECT EXPORT_SET(5,'Y','N',',',4);
            -> 'Y,N,Y,N'
    mysql> SELECT EXPORT_SET(6,'1','0',',',10);
            -> '0,1,1,0,0,0,0,0,0,0'
                                    
  • FIELD( str , str1 , str2 , str3 ,...)

    Returns the index (position) of str in the str1 , str2 , str3 , ... list. Returns 0 if str is not found.

    If all arguments to FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double.

    If str is NULL , the return value is 0 because NULL fails equality comparison with any value. FIELD() is the complement of ELT() .

    mysql> SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
            -> 2
    mysql> SELECT FIELD('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
            -> 0
                                    
  • FIND_IN_SET( str , strlist )

    Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by , characters. If the first argument is a constant string and the second is a column of type SET , the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL . This function does not work properly if the first argument contains a comma ( , ) character.

    mysql> SELECT FIND_IN_SET('b','a,b,c,d');
            -> 2
                                    
  • FORMAT( X , D [, locale ])

    Formats the number X to a format like '#,###,###.##' , rounded to D decimal places, and returns the result as a string. If D is 0 , the result has no decimal point or fractional part.

    The optional third parameter enables a locale to be specified to be used for the result number's decimal point, thousands separator, and grouping between separators. Permissible locale values are the same as the legal values for the lc_time_names system variable (see Section 10.15, “MySQL Server Locale Support” ). If no locale is specified, the default is 'en_US' .

    mysql> SELECT FORMAT(12332.123456, 4);
            -> '12,332.1235'
    mysql> SELECT FORMAT(12332.1,4);
            -> '12,332.1000'
    mysql> SELECT FORMAT(12332.2,0);
            -> '12,332'
    mysql> SELECT FORMAT(12332.2,2,'de_DE');
            -> '12.332,20'
                                    
  • FROM_BASE64( str )

    Takes a string encoded with the base-64 encoded rules used by TO_BASE64() and returns the decoded result as a binary string. The result is NULL if the argument is NULL or not a valid base-64 string. See the description of TO_BASE64() for details about the encoding and decoding rules.

    mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
            -> 'JWJj', 'abc'
                                    
  • HEX( str ) , HEX( N )

    For a string argument str , HEX() returns a hexadecimal string representation of str where each byte of each character in str is converted to two hexadecimal digits. (Multibyte characters therefore become more than two digits.) The inverse of this operation is performed by the UNHEX() function.

    For a numeric argument N , HEX() returns a hexadecimal string representation of the value of N treated as a longlong ( BIGINT ) number. This is equivalent to CONV( N ,10,16) . The inverse of this operation is performed by CONV(HEX( N ),16,10) .

    mysql> SELECT X'616263', HEX('abc'), UNHEX(HEX('abc'));
            -> 'abc', 616263, 'abc'
    mysql> SELECT HEX(255), CONV(HEX(255),16,10);
            -> 'FF', 255
                                    
  • INSERT( str , pos , len , newstr )

    Returns the string str , with the substring beginning at position pos and len characters long replaced by the string newstr . Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string. Returns NULL if any argument is NULL .

    mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
            -> 'QuWhattic'
    mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
            -> 'Quadratic'
    mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
            -> 'QuWhat'
                                    

    This function is multibyte safe.

  • INSTR( str , substr )

    Returns the position of the first occurrence of substring substr in string str . This is the same as the two-argument form of LOCATE() , except that the order of the arguments is reversed.

    mysql> SELECT INSTR('foobarbar', 'bar');
            -> 4
    mysql> SELECT INSTR('xbar', 'foobar');
            -> 0
                                    

    This function is multibyte safe, and is case-sensitive only if at least one argument is a binary string.

  • LCASE( str )

    LCASE() is a synonym for LOWER() .

    LCASE() used in a view is rewritten as LOWER() when storing the view's definition. (Bug #12844279)

  • LEFT( str , len )

    Returns the leftmost len characters from the string str , or NULL if any argument is NULL .

    mysql> SELECT LEFT('foobarbar', 5);
            -> 'fooba'
                                    

    This function is multibyte safe.

  • LENGTH( str )

    Returns the length of the string str , measured in bytes. A multibyte character counts as multiple bytes. This means that for a string containing five 2-byte characters, LENGTH() returns 10 , whereas CHAR_LENGTH() returns 5 .

    mysql> SELECT LENGTH('text');
            -> 4
                                    
    Note

    The Length() OpenGIS spatial function is named ST_Length() in MySQL.

  • LOAD_FILE( file_name )

    Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes. If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

    If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL .

    The character_set_filesystem system variable controls interpretation of file names that are given as literal strings.

    mysql> UPDATE t
                SET blob_col=LOAD_FILE('/tmp/picture')
                WHERE id=1;
                                    
  • LOCATE( substr , str ) , LOCATE( substr , str , pos )

    The first syntax returns the position of the first occurrence of substring substr in string str . The second syntax returns the position of the first occurrence of substring substr in string str , starting at position pos . Returns 0 if substr is not in str . Returns NULL if any argument is NULL .

    mysql> SELECT LOCATE('bar', 'foobarbar');
            -> 4
    mysql> SELECT LOCATE('xbar', 'foobar');
            -> 0
    mysql> SELECT LOCATE('bar', 'foobarbar', 5);
            -> 7
                                    

    This function is multibyte safe, and is case-sensitive only if at least one argument is a binary string.

  • LOWER( str )

    Returns the string str with all characters changed to lowercase according to the current character set mapping. The default is utf8mb4 .

    mysql> SELECT LOWER('QUADRATICALLY');
            -> 'quadratically'
                                    

    LOWER() (and UPPER() ) are ineffective when applied to binary strings ( BINARY , VARBINARY , BLOB ). To perform lettercase conversion, convert the string to a nonbinary string:

    mysql> SET @str = BINARY 'New York';
    mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING utf8mb4));
    +-------------+------------------------------------+
    | LOWER(@str) | LOWER(CONVERT(@str USING utf8mb4)) |
    +-------------+------------------------------------+
    | New York    | new york                           |
    +-------------+------------------------------------+
                                    

    For collations of Unicode character sets, LOWER() and UPPER() work according to the Unicode Collation Algorithm (UCA) version in the collation name, if there is one, and UCA 4.0.0 if no version is specified. For example, utf8mb4_0900_ai_ci and utf8_unicode_520_ci work according to UCA 9.0.0 and 5.2.0, respectively, whereas utf8_unicode_ci works according to UCA 4.0.0. See Section 10.10.1, “Unicode Character Sets” .

    This function is multibyte safe.

    LCASE() used within views is rewritten as LOWER() .

  • LPAD( str , len , padstr )

    Returns the string str , left-padded with the string padstr to a length of len characters. If str is longer than len , the return value is shortened to len characters.

    mysql> SELECT LPAD('hi',4,'??');
            -> '??hi'
    mysql> SELECT LPAD('hi',1,'??');
            -> 'h'
                                    
  • LTRIM( str )

    Returns the string str with leading space characters removed.

    mysql> SELECT LTRIM('  barbar');
            -> 'barbar'
                                    

    This function is multibyte safe.

  • MAKE_SET( bits , str1 , str2 ,...)

    Returns a set value (a string containing substrings separated by , characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, and so on. NULL values in str1 , str2 , ... are not appended to the result.

    mysql> SELECT MAKE_SET(1,'a','b','c');
            -> 'a'
    mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
            -> 'hello,world'
    mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');
            -> 'hello'
    mysql> SELECT MAKE_SET(0,'a','b','c');
            -> ''
                                    
  • MID( str , pos , len )

    MID( str , pos , len ) is a synonym for SUBSTRING( str , pos , len ) .

  • OCT( N )

    Returns a string representation of the octal value of N , where N is a longlong ( BIGINT ) number. This is equivalent to CONV( N ,10,8) . Returns NULL if N is NULL .

    mysql> SELECT OCT(12);
            -> '14'
                                    
  • OCTET_LENGTH( str )

    OCTET_LENGTH() is a synonym for LENGTH() .

  • ORD( str )

    If the leftmost character of the string str is a multibyte character, returns the code for that character, calculated from the numeric values of its constituent bytes using this formula:

      (1st byte code)
    + (2nd byte code * 256)
    + (3rd byte code * 256^2) ...
                                    

    If the leftmost character is not a multibyte character, ORD() returns the same value as the ASCII() function.

    mysql> SELECT ORD('2');
            -> 50
                                    
  • POSITION( substr IN str )

    POSITION( substr IN str ) is a synonym for LOCATE( substr , str ) .

  • QUOTE( str )

    Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned enclosed by single quotation marks and with each instance of backslash ( \ ), single quote ( ' ), ASCII NUL , and Control+Z preceded by a backslash. If the argument is NULL , the return value is the word NULL without enclosing single quotation marks.

    mysql> SELECT QUOTE('Don\'t!');
            -> 'Don\'t!'
    mysql> SELECT QUOTE(NULL);
            -> NULL
                                    

    For comparison, see the quoting rules for literal strings and within the C API in Section 9.1.1, “String Literals” , and Section 28.7.7.56, “mysql_real_escape_string_quote()” .

  • REPEAT( str , count )

    Returns a string consisting of the string str repeated count times. If count is less than 1, returns an empty string. Returns NULL if str or count are NULL .

    mysql> SELECT REPEAT('MySQL', 3);
            -> 'MySQLMySQLMySQL'
                                    
  • REPLACE( str , from_str , to_str )

    Returns the string str with all occurrences of the string from_str replaced by the string to_str . REPLACE() performs a case-sensitive match when searching for from_str .

    mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
            -> 'WwWwWw.mysql.com'
                                    

    This function is multibyte safe.

  • REVERSE( str )

    Returns the string str with the order of the characters reversed.

    mysql> SELECT REVERSE('abc');
            -> 'cba'
                                    

    This function is multibyte safe.

  • RIGHT( str , len )

    Returns the rightmost len characters from the string str , or NULL if any argument is NULL .

    mysql> SELECT RIGHT('foobarbar', 4);
            -> 'rbar'
                                    

    This function is multibyte safe.

  • RPAD( str , len , padstr )

    Returns the string str , right-padded with the string padstr to a length of len characters. If str is longer than len , the return value is shortened to len characters.

    mysql> SELECT RPAD('hi',5,'?');
            -> 'hi???'
    mysql> SELECT RPAD('hi',1,'?');
            -> 'h'
                                    

    This function is multibyte safe.

  • RTRIM( str )

    Returns the string str with trailing space characters removed.

    mysql> SELECT RTRIM('barbar   ');
            -> 'barbar'
                                    

    This function is multibyte safe.

  • SOUNDEX( str )

    Returns a soundex string from str . Two strings that sound almost the same should have identical soundex strings. A standard soundex string is four characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. All nonalphabetic characters in str are ignored. All international alphabetic characters outside the A-Z range are treated as vowels.

    Important

    When using SOUNDEX() , you should be aware of the following limitations:

    • This function, as currently implemented, is intended to work well with strings that are in the English language only. Strings in other languages may not produce reliable results.

    • This function is not guaranteed to provide consistent results with strings that use multibyte character sets, including utf-8 . See Bug #22638 for more information.

    mysql> SELECT SOUNDEX('Hello');
            -> 'H400'
    mysql> SELECT SOUNDEX('Quadratically');
            -> 'Q36324'
                                    
    Note

    This function implements the original Soundex algorithm, not the more popular enhanced version (also described by D. Knuth). The difference is that original version discards vowels first and duplicates second, whereas the enhanced version discards duplicates first and vowels second.

  • expr1 SOUNDS LIKE expr2

    This is the same as SOUNDEX( expr1 ) = SOUNDEX( expr2 ) .

  • SPACE( N )

    Returns a string consisting of N space characters.

    mysql> SELECT SPACE(6);
            -> '      '
                                    
  • SUBSTR( str , pos ) , SUBSTR( str FROM pos ) , SUBSTR( str , pos , len ) , SUBSTR( str FROM pos FOR len )

    SUBSTR() is a synonym for SUBSTRING() .

  • SUBSTRING( str , pos ) , SUBSTRING( str FROM pos ) , SUBSTRING( str , pos , len ) , SUBSTRING( str FROM pos FOR len )

    The forms without a len argument return a substring from string str starting at position pos . The forms with a len argument return a substring len characters long from string str , starting at position pos . The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos . In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function.

    For all forms of SUBSTRING() , the position of the first character in the string from which the substring is to be extracted is reckoned as 1 .

    mysql> SELECT SUBSTRING('Quadratically',5);
            -> 'ratically'
    mysql> SELECT SUBSTRING('foobarbar' FROM 4);
            -> 'barbar'
    mysql> SELECT SUBSTRING('Quadratically',5,6);
            -> 'ratica'
    mysql> SELECT SUBSTRING('Sakila', -3);
            -> 'ila'
    mysql> SELECT SUBSTRING('Sakila', -5, 3);
            -> 'aki'
    mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
            -> 'ki'
                                    

    This function is multibyte safe.

    If len is less than 1, the result is the empty string.

  • SUBSTRING_INDEX( str , delim , count )

    Returns the substring from string str before count occurrences of the delimiter delim . If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim .

    mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
            -> 'www.mysql'
    mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
            -> 'mysql.com'
                                    

    This function is multibyte safe.

  • TO_BASE64( str )

    Converts the string argument to base-64 encoded form and returns the result as a character string with the connection character set and collation. If the argument is not a string, it is converted to a string before conversion takes place. The result is NULL if the argument is NULL . Base-64 encoded strings can be decoded using the FROM_BASE64() function.

    mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
            -> 'JWJj', 'abc'
                                    

    Different base-64 encoding schemes exist. These are the encoding and decoding rules used by TO_BASE64() and FROM_BASE64() :

    • The encoding for alphabet value 62 is '+' .

    • The encoding for alphabet value 63 is '/' .

    • Encoded output consists of groups of 4 printable characters. Each 3 bytes of the input data are encoded using 4 characters. If the last group is incomplete, it is padded with '=' characters to a length of 4.

    • A newline is added after each 76 characters of encoded output to divide long output into multiple lines.

    • Decoding recognizes and ignores newline, carriage return, tab, and space.

  • TRIM([{BOTH | LEADING | TRAILING} [ remstr ] FROM] str ) , TRIM([ remstr FROM] str )

    Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH , LEADING , or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.

    mysql> SELECT TRIM('  bar   ');
            -> 'bar'
    mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
            -> 'barxxx'
    mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
            -> 'bar'
    mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
            -> 'barx'
                                    

    This function is multibyte safe.

  • UCASE( str )

    UCASE() is a synonym for UPPER() .

    UCASE() used within views is rewritten as UPPER() .

  • UNHEX( str )

    For a string argument str , UNHEX( str ) interprets each pair of characters in the argument as a hexadecimal number and converts it to the byte represented by the number. The return value is a binary string.

    mysql> SELECT UNHEX('4D7953514C');
            -> 'MySQL'
    mysql> SELECT X'4D7953514C';
            -> 'MySQL'
    mysql> SELECT UNHEX(HEX('string'));
            -> 'string'
    mysql> SELECT HEX(UNHEX('1267'));
            -> '1267'
                                    

    The characters in the argument string must be legal hexadecimal digits: '0' .. '9' , 'A' .. 'F' , 'a' .. 'f' . If the argument contains any nonhexadecimal digits, the result is NULL :

    mysql> SELECT UNHEX('GG');
    +-------------+
    | UNHEX('GG') |
    +-------------+
    | NULL        |
    +-------------+
                                    

    A NULL result can occur if the argument to UNHEX() is a BINARY column, because values are padded with 0x00 bytes when stored but those bytes are not stripped on retrieval. For example, '41' is stored into a CHAR(3) column as '41 ' and retrieved as '41' (with the trailing pad space stripped), so UNHEX() for the column value returns 'A' . By contrast '41' is stored into a BINARY(3) column as '41\0' and retrieved as '41\0' (with the trailing pad 0x00 byte not stripped). '\0' is not a legal hexadecimal digit, so UNHEX() for the column value returns NULL .

    For a numeric argument N , the inverse of HEX( N ) is not performed by UNHEX() . Use CONV(HEX( N ),16,10) instead. See the description of HEX() .

  • UPPER( str )

    Returns the string str with all characters changed to uppercase according to the current character set mapping. The default is utf8mb4 .

    mysql> SELECT UPPER('Hej');
            -> 'HEJ'
                                    

    See the description of LOWER() for information that also applies to UPPER() . This included information about how to perform lettercase conversion of binary strings ( BINARY , VARBINARY , BLOB ) for which these functions are ineffective, and information about case folding for Unicode character sets.

    This function is multibyte safe.

    UCASE() used within views is rewritten as UPPER() .

  • WEIGHT_STRING( str [AS {CHAR|BINARY}( N )] [ flags ])

    This function returns the weight string for the input string. The return value is a binary string that represents the comparison and sorting value of the string. It has these properties:

    WEIGHT_STRING() is a debugging function intended for internal use. Its behavior can change without notice between MySQL versions. It can be used for testing and debugging of collations, especially if you are adding a new collation. See Section 10.13, “Adding a Collation to a Character Set” .

    This list briefly summarizes the arguments. More details are given in the discussion following the list.

    • str : The input string expression.

    • AS clause: Optional; cast the input string to a given type and length.

    • flags : Optional; unused.

    The input string, str , is a string expression. If the input is a nonbinary (character) string such as a CHAR , VARCHAR , or TEXT value, the return value contains the collation weights for the string. If the input is a binary (byte) string such as a BINARY , VARBINARY , or BLOB value, the return value is the same as the input (the weight for each byte in a binary string is the byte value). If the input is NULL , WEIGHT_STRING() returns NULL .

    Examples:

    mysql> SET @s = _utf8mb4 'AB' COLLATE utf8mb4_0900_ai_ci;
    mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
    +------+---------+------------------------+
    | @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
    +------+---------+------------------------+
    | AB   | 4142    | 1C471C60               |
    +------+---------+------------------------+
                                    
    mysql> SET @s = _utf8mb4 'ab' COLLATE utf8mb4_0900_ai_ci;
    mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
    +------+---------+------------------------+
    | @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
    +------+---------+------------------------+
    | ab   | 6162    | 1C471C60               |
    +------+---------+------------------------+
                                    
    mysql> SET @s = CAST('AB' AS BINARY);
    mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
    +------+---------+------------------------+
    | @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
    +------+---------+------------------------+
    | AB   | 4142    | 4142                   |
    +------+---------+------------------------+
                                    
    mysql> SET @s = CAST('ab' AS BINARY);
    mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
    +------+---------+------------------------+
    | @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
    +------+---------+------------------------+
    | ab   | 6162    | 6162                   |
    +------+---------+------------------------+
                                    

    The preceding examples use HEX() to display the WEIGHT_STRING() result. Because the result is a binary value, HEX() can be especially useful when the result contains nonprinting values, to display it in printable form:

    mysql> SET @s = CONVERT(X'C39F' USING utf8) COLLATE utf8_czech_ci;
    mysql> SELECT HEX(WEIGHT_STRING(@s));
    +------------------------+
    | HEX(WEIGHT_STRING(@s)) |
    +------------------------+
    | 0FEA0FEA               |
    +------------------------+
                                    

    For non- NULL return values, the data type of the value is VARBINARY if its length is within the maximum length for VARBINARY , otherwise the data type is BLOB .

    The AS clause may be given to cast the input string to a nonbinary or binary string and to force it to a given length:

    • AS CHAR( N ) casts the string to a nonbinary string and pads it on the right with spaces to a length of N characters. N must be at least 1. If N is less than the length of the input string, the string is truncated to N characters. No warning occurs for truncation.

    • AS BINARY( N ) is similar but casts the string to a binary string, N is measured in bytes (not characters), and padding uses 0x00 bytes (not spaces).

    mysql> SET NAMES 'latin1';
    mysql> SELECT HEX(WEIGHT_STRING('ab' AS CHAR(4)));
    +-------------------------------------+
    | HEX(WEIGHT_STRING('ab' AS CHAR(4))) |
    +-------------------------------------+
    | 41422020                            |
    +-------------------------------------+
    mysql> SET NAMES 'utf8';
    mysql> SELECT HEX(WEIGHT_STRING('ab' AS CHAR(4)));
    +-------------------------------------+
    | HEX(WEIGHT_STRING('ab' AS CHAR(4))) |
    +-------------------------------------+
    | 0041004200200020                    |
    +-------------------------------------+
                                    
    mysql> SELECT HEX(WEIGHT_STRING('ab' AS BINARY(4)));
    +---------------------------------------+
    | HEX(WEIGHT_STRING('ab' AS BINARY(4))) |
    +---------------------------------------+
    | 61620000                              |
    +---------------------------------------+
                                    

    The flags clause currently is unused.

12.5.1 String Comparison Functions

Table 12.8 String Comparison Operators

Name 描述
LIKE Simple pattern matching
NOT LIKE Negation of simple pattern matching
STRCMP() Compare two strings

If a string function is given a binary string as an argument, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This affects only comparisons.

Normally, if any expression in a string comparison is case sensitive, the comparison is performed in case-sensitive fashion.

  • expr LIKE pat [ESCAPE ' escape_char ']

    Pattern matching using an SQL pattern. Returns 1 ( TRUE ) or 0 ( FALSE ). If either expr or pat is NULL , the result is NULL .

    The pattern need not be a literal string. For example, it can be specified as a string expression or table column.

    Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:

    mysql> SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci;
    +-----------------------------------------+
    | 'ä' LIKE 'ae' COLLATE latin1_german2_ci |
    +-----------------------------------------+
    |                                       0 |
    +-----------------------------------------+
    mysql> SELECT 'ä' = 'ae' COLLATE latin1_german2_ci;
    +--------------------------------------+
    | 'ä' = 'ae' COLLATE latin1_german2_ci |
    +--------------------------------------+
    |                                    1 |
    +--------------------------------------+
                                        

    In particular, trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator:

    mysql> SELECT 'a' = 'a ', 'a' LIKE 'a ';
    +------------+---------------+
    | 'a' = 'a ' | 'a' LIKE 'a ' |
    +------------+---------------+
    |          1 |             0 |
    +------------+---------------+
    1 row in set (0.00 sec)
                                        

    With LIKE you can use the following two wildcard characters in the pattern:

    • % matches any number of characters, even zero characters.

    • _ matches exactly one character.

    mysql> SELECT 'David!' LIKE 'David_';
            -> 1
    mysql> SELECT 'David!' LIKE '%D%v%';
            -> 1
                                        

    To test for literal instances of a wildcard character, precede it by the escape character. If you do not specify the ESCAPE character, \ is assumed.

    • \% matches one % character.

    • \_ matches one _ character.

    mysql> SELECT 'David!' LIKE 'David\_';
            -> 0
    mysql> SELECT 'David_' LIKE 'David\_';
            -> 1
                                        

    To specify a different escape character, use the ESCAPE clause:

    mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
            -> 1
                                        

    The escape sequence should be empty or one character long. The expression must evaluate as a constant at execution time. If the NO_BACKSLASH_ESCAPES SQL mode is enabled, the sequence cannot be empty.

    The following two statements illustrate that string comparisons are not case-sensitive unless one of the operands is case-sensitive (uses a case-sensitive collation or is a binary string):

    mysql> SELECT 'abc' LIKE 'ABC';
            -> 1
    mysql> SELECT 'abc' LIKE _utf8mb4 'ABC' COLLATE utf8mb4_0900_as_cs;
            -> 0
    mysql> SELECT 'abc' LIKE _utf8mb4 'ABC' COLLATE utf8mb4_bin;
            -> 0
    mysql> SELECT 'abc' LIKE BINARY 'ABC';
            -> 0
                                        

    As an extension to standard SQL, MySQL permits LIKE on numeric expressions.

    mysql> SELECT 10 LIKE '1%';
            -> 1
                                        
    Note

    Because MySQL uses C escape syntax in strings (for example, \n to represent a newline character), you must double any \ that you use in LIKE strings. For example, to search for \n , specify it as \\n . To search for \ , specify it as \\\\ ; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

    Exception: At the end of the pattern string, backslash can be specified as \\ . At the end of the string, backslash stands for itself because there is nothing following to escape. Suppose that a table contains the following values:

    mysql> SELECT filename FROM t1;
    +--------------+
    | filename     |
    +--------------+
    | C:           |
    | C:\          |
    | C:\Programs  |
    | C:\Programs\ |
    +--------------+
                                            

    To test for values that end with backslash, you can match the values using either of the following patterns:

    mysql> SELECT filename, filename LIKE '%\\' FROM t1;
    +--------------+---------------------+
    | filename     | filename LIKE '%\\' |
    +--------------+---------------------+
    | C:           |                   0 |
    | C:\          |                   1 |
    | C:\Programs  |                   0 |
    | C:\Programs\ |                   1 |
    +--------------+---------------------+
    mysql> SELECT filename, filename LIKE '%\\\\' FROM t1;
    +--------------+-----------------------+
    | filename     | filename LIKE '%\\\\' |
    +--------------+-----------------------+
    | C:           |                     0 |
    | C:\          |                     1 |
    | C:\Programs  |                     0 |
    | C:\Programs\ |                     1 |
    +--------------+-----------------------+
                                            
  • expr NOT LIKE pat [ESCAPE ' escape_char ']

    This is the same as NOT ( expr LIKE pat [ESCAPE ' escape_char ']) .

    Note

    Aggregate queries involving NOT LIKE comparisons with columns containing NULL may yield unexpected results. For example, consider the following table and data:

    CREATE TABLE foo (bar VARCHAR(10));
    INSERT INTO foo VALUES (NULL), (NULL);
                                            

    The query SELECT COUNT(*) FROM foo WHERE bar LIKE '%baz%'; returns 0 . You might assume that SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%'; would return 2 . However, this is not the case: The second query returns 0 . This is because NULL NOT LIKE expr always returns NULL , regardless of the value of expr . The same is true for aggregate queries involving NULL and comparisons using NOT RLIKE or NOT REGEXP . In such cases, you must test explicitly for NOT NULL using OR (and not AND ), as shown here:

    SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%' OR bar IS NULL;
                                            
  • STRCMP( expr1 , expr2 )

    STRCMP() returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise.

    mysql> SELECT STRCMP('text', 'text2');
            -> -1
    mysql> SELECT STRCMP('text2', 'text');
            -> 1
    mysql> SELECT STRCMP('text', 'text');
            -> 0
                                        

    STRCMP() performs the comparison using the collation of the arguments.

    mysql> SET @s1 = _utf8mb4 'x' COLLATE utf8mb4_0900_ai_ci;
    mysql> SET @s2 = _utf8mb4 'X' COLLATE utf8mb4_0900_ai_ci;
    mysql> SET @s3 = _utf8mb4 'x' COLLATE utf8mb4_0900_as_cs;
    mysql> SET @s4 = _utf8mb4 'X' COLLATE utf8mb4_0900_as_cs;
    mysql> SELECT STRCMP(@s1, @s2), STRCMP(@s3, @s4);
    +------------------+------------------+
    | STRCMP(@s1, @s2) | STRCMP(@s3, @s4) |
    +------------------+------------------+
    |                0 |               -1 |
    +------------------+------------------+
                                        

    If the collations are incompatible, one of the arguments must be converted to be compatible with the other. See Section 10.8.4, “Collation Coercibility in Expressions” .

    mysql> SET @s1 = _utf8mb4 'x' COLLATE utf8mb4_0900_ai_ci;
    mysql> SET @s2 = _utf8mb4 'X' COLLATE utf8mb4_0900_ai_ci;
    mysql> SET @s3 = _utf8mb4 'x' COLLATE utf8mb4_0900_as_cs;
    mysql> SET @s4 = _utf8mb4 'X' COLLATE utf8mb4_0900_as_cs;
    -->
    mysql> SELECT STRCMP(@s1, @s3);
    ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT)
    and (utf8mb4_0900_as_cs,IMPLICIT) for operation 'strcmp'
    mysql> SELECT STRCMP(@s1, @s3 COLLATE utf8mb4_0900_ai_ci);
    +---------------------------------------------+
    | STRCMP(@s1, @s3 COLLATE utf8mb4_0900_ai_ci) |
    +---------------------------------------------+
    |                                           0 |
    +---------------------------------------------+
                                        

12.5.2 Regular Expressions

Table 12.9 Regular Expression Functions and Operators

Name 描述
NOT REGEXP Negation of REGEXP
REGEXP Whether string matches regular expression
REGEXP_INSTR() Starting index of substring matching regular expression
REGEXP_LIKE() Whether string matches regular expression
REGEXP_REPLACE() Replace substrings matching regular expression
REGEXP_SUBSTR() Return substring matching regular expression