String is a finite sequence composed of zero or multiple characters. It is generally recorded as s = 'a1a2 • an '(n> = 0 ). It is the data type that represents text in programming languages.
Generally, the entire string is used as the operation object, for example: search for a substring in a substring, obtain a substring, insert a substring at a certain position in the substring, and delete a substring. The required and sufficient conditions for the two strings to be equal are: the length is equal, and the characters at each corresponding position are equal. If p and q are two strings, the calculation of the position where q first appeared in p is called pattern matching. The two most basic storage methods of strings are sequential storage and link storage.
Let's take a look at the string functions in MySQL.
If the result length is greater than the maximum value of the max_allowed_packet system variable, the return value of the string value function is NULL.
For a function operated at the string position, the first position is numbered 1.
◆ ASCII (str)
The return value is the leftmost character of the str string. If str is a null string, the return value is 0. If 'str' is NULL, the return value is NULL. ASCII () is a character with a value ranging from 0 to 255.
mysql> SELECT ASCII('2'); -> 50mysql> SELECT ASCII(2); -> 50mysql> SELECT ASCII('dx'); -> 100 |
See the ORD () function.
◆ BIN (N)
The string that returns the binary value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV (N, 10, 2 ). If N is NULL, the return value is NULL.
mysql> SELECT BIN(12); -> '1100' |
◆ BIT_LENGTH (str)
Returns the str length of a binary string.
mysql> SELECT BIT_LENGTH('text'); -> 32 |
◆ CHAR (N,... [USING charset])
CHAR () interprets each parameter N as an integer, and its return value is a string containing the characters given by the code values of these integers. The NULL value is omitted.
mysql> SELECT CHAR(77,121,83,81,'76'); -> 'MySQL' mysql> SELECT CHAR(77,77.3,'77.3'); -> 'MMM' |
The CHAR () parameter greater than 255 is converted to multi-result characters. For example, CHAR (256) is equivalent to CHAR (256), while CHAR (256 *) is equivalent to CHAR (, 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 | +------------------+--------------------+ |
The return value of CHAR () is a binary string. You can use the USING statement to generate a string in the given character set:
mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8)); mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8)); +---------------------+--------------------------------+ | CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 USING utf8)) | +---------------------+--------------------------------+ | binary | utf8 | +---------------------+--------------------------------+ |
If USING has been generated and the result string does not conform to the given character set, a warning is issued. Similarly, if the strict SQL mode is activated, the CHAR () result will become NULL.
◆ CHAR_LENGTH (str)
The return value is the length of the str string, measured in characters. A multi-byte character is counted as a single character. For a five-Byte Character Set, LENGTH () returns 10, while CHAR_LENGTH () returns 5.
◆ CHARACTER_LENGTH (str)
CHARACTER_LENGTH () is a synonym for CHAR_LENGTH.
◆ COMPRESS (string_to_compress)
Compress a string. This function requires that MySQL has been compressed using a compression library such as zlib. Otherwise, the return value is always NULL. UNCOMPRESS () can extract compressed strings.
mysql> SELECT LENGTH(COMPRESS(REPEAT('a',1000))); -> 21 mysql> SELECT LENGTH(COMPRESS('')); -> 0 mysql> SELECT LENGTH(COMPRESS('a')); -> 13 mysql> SELECT LENGTH(COMPRESS(REPEAT('a',16))); -> 15 |
The content of the compressed string is stored as follows:
Empty strings are stored as empty strings.
The four-byte length of a non-empty string that is not compressed is stored (the first is low byte), followed by a compressed string. If the string ends with a space, ". ", to prevent the result value from automatically removing trailing spaces when it is stored in a CHAR or VARCHAR field column. (CHAR or VARCHAR is not recommended for storing compressed strings. It is best to use a BLOB column instead ).