In Windows 10 64, MySQL 5.6, the process of modifying another field value is spliced based on other field values in the current table
1. The final expectation result is: modify the avatar in the player table, its value is: /storage/web/app/player_avatar/gzhou/579365.jpg, where gzhou is the value of the current record field TEAM_CODE, 579365 is the value of the current record field code, as shown in Figure 1
Figure 1
2. First modify the avatar of a record, and its SQL statement is successfully executed, as shown in Figure 2
update `kq_player` set `avatar` = concat(/storage/web/app/player_avatar/,`team_code`,/ / *, `code`,.jpg) WHERE `code` =389345;
Figure 2
3. After refreshing, check the execution results and find that the code is 389345. avatar, its value is: /storage/web/app/player_avatar/gzhou/389345.jpg, the modification is successful, in line with expectations, as shown in Figure 3
Figure 3
4. Check the records before modifying all records, avatar is empty, as shown in Figure 4
Figure 4
5. Prepare to modify the avatars of all records, remove the where condition, and the SQL statement is successfully executed, as shown in Figure 5
update `kq_player` set `avatar` = concat(/storage/web/app/player_avatar/,`team_code`,/ / *, `code`,.jpg);
Figure 5
6. Check the records after modifying all records, avatar is no longer empty, the modification is successful, in line with expectations, as shown in Figure 6
Figure 6
7. Search the avatar field to see if it contains spaces, as shown in Figure 7
Figure 7
8. The execution of the sql statement is successful, and there are indeed some records containing spaces, as shown in Figure 8
/storage/web/app/player_avatar/lifan/974218.jpg
8
9. Decide to clear the spaces in the team_code field first, and then execute the SQL statement to modify the avatar field, and its SQL statement is successfully executed, as shown in Figure 9
update `kq_player` set `team_code` = replace(`team_code`,,);
update `kq_player` set `avatar` = concat(/storage/web/app/player_avatar/,`team_code`,/ / *, `code`,.jpg);
Figure 9
10. Search the avatar field to see if it contains spaces, and return zero rows, as shown in Figure 10
Figure 10
