MySQL update image path (SQL statement) instance

Table testinfo structure:

Id picurl

1 AA/BB/CC/1.jpg

2 cc/DD/EE/FF/AA/2.jpg

3 hh/3.jpg

Bytes ----------------------------------------------------------------------------------------------------------------

MySQL Functions

Concat (str1, str2 ,...)
Concatenate a parameter into a long string and return it (if any parameter is null, return NULL)
Mysql> select Concat ('My, s', 'ql ');
-> 'Mysql'
Mysql> select Concat ('My, null, 'ql ');
-> Null
Mysql> select Concat (14.3 );
-> '14. 3 ′

Truncate string by keyword
Substring_index (STR, delim, count)
Note: substring_index (intercepted field, keyword, number of times the keyword appears)

Example: Select substring_index ("www.a.com ",".", 2) as abstract from my_content_t

Result: www.

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

Result: a.com

Bytes -------------------------------------------------------------------------------------------------------------------

Remove the path in front of the image and add AAA/example: AAA/1.jpg

Update testinfo set picurl = Concat ('aaa', '/', substring_index (picurl, '/',-1 ))

Output result:

Id picurl

1 AAA/1.jpg

2 AAA/2.jpg

3 AAA/3.jpg

Save only images for example: 1.jpg

Update testinfo set picurl = substring_index (picurl, '/',-1)

Output result:

Id picurl

1 1.jpg

2 2.jpg

3 3.jpg