MySQL string functions (1)

String is a finite sequence composed of zero or multiple characters. It is generally recorded as sa1a2an (n0 ). 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 two strings are equal.

String is a finite sequence composed of zero or multiple characters. It is generally recorded as s = 'a1a2a' (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 two strings are equal.

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 ).