MySQL: MySQL string truncation function substring usage instructions

It seems that the string function of MySQL intercepts characters.ProgramThe interception (such as PHP or Java) is powerful, so we will make a record here and hope it will be useful to everyone.
Function:
1. Extract strings from the left
Left (STR, length)
Note: Left (truncated field, truncated length)
Example: Select left (content, 200) as abstract from my_content_t
2. Extract strings from the right
Right (STR, length)
Note: Right (intercepted field, truncated length)
Example: select right (content, 200) as abstract from my_content_t
3. truncate a string
Substring (STR, POS)
Substring (STR, POs, length)
Description: substring (truncated field, starting from the nth digit)
Substring (truncated field, starting from the nth digit and intercepting the length)
Example: Select substring (content, 5) as abstract from my_content_t
Select substring (content, 5,200) as abstract from my_content_t
(Note: if the number of digits is negative, for example,-5, it is the length from the last to the end or truncation of the string)
4. truncate a string by keyword
Substring_index (STR, delim, count)
Note: substring_index (intercepted field, keyword, number of times the keyword appears)
Example: Select substring_index ("blog.jb51.net ",". ", 2) as abstract from my_content_t
Result: blog. jb51
(Note: if the number of times a keyword appears is negative, for example,-2, it is counted from the last to the end of the string)

Function introduction:

Substring (STR, POS), substring (STR from POS) substring (STR, POs, Len), substring (STR from POS for Len)

Returns a substring from the STR string in a format that does not contain the Len parameter, starting from the position POS. The format with the Len parameter returns a substring with the same length as the Len character from the string STR, starting from the position POS. Use the from format as the standard SQL syntax. It may also use a negative value for pos. In this case, the position of the substring starts with the POs character at the end of the string, rather than the start position of the string. You can use a negative value for POs in the following format.

For more information, see the manual.

Instance:
Table 1: User

Table 2: Jl

Expected results: the id value stored in the jlid field of the User table is used to read the corresponding records in the JL table. to read the records with IDs of 1 and 2 in the JL table, first use in, but unfortunately
There are two ID values stored in the jlid field. Although the format complies with the In (1, 2), if you use select Jl. * From JL where Jl. ID in (select jlid from user where user. id = 1.

So what should we do? If we can get 1 and 2 in 1 and 2 respectively, then we can. Fortunately, MySQL also provides the string truncation function substring.

The SQL syntax is as follows:
Select Jl .*
From Jl
Where Jl. ID = (
Select substring ((

Select User. jlid
From User
Where user. ID = 1
), 1, 1 ))
Or Jl. ID = (

Select substring ((

Select User. jlid
From User
Where user. ID = 1
), 3, 1)
)
Limit 0, 30

A brief explanation:

Select substring (Select User. jlid from user where user. ID = 1), 1, 1 ))
Subquery is used here. First, query the value of the jlid field with ID 1 in the User table, return a string, and then use substring for truncation to obtain string 1.
Select substring (Select User. jlid from user where user. ID = 1), 3, 1 ))
This statement returns 2

1 and 2 get and query through the where of the primary query. Note that we need to query records with ID = 1 and ID = 2, so we use or. How is it a little troublesome,

Is it your first instinct to use two SQL statements in combination with the explode function of PHP? This is normal, but the efficiency between the two is high, and the webmaster has not tested it. I hope you can help me!