要在字段中查找/替换字符串,语法如下-
update yourTableName set yourColumnName =REPLACE(yourColumnName,yourOldValue,yourNewValue);
为了理解上述语法,让我们创建一个表。创建表的查询如下-
mysql> create table FindReplaceDemo -> ( -> FileId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> FileDirectory text -> );
现在,您可以使用insert命令在表中插入一些记录。查询如下-
mysql> insert into FindReplaceDemo(FileDirectory) values('C://User//MySQL'); mysql> insert into FindReplaceDemo(FileDirectory) values('D://WebsiteImage//image1.jpg'); mysql> insert into FindReplaceDemo(FileDirectory) values('E://Java//AdvancedJava');
使用select语句显示表中的所有记录。查询如下-
mysql> select *from FindReplaceDemo;
这是输出-
+--------+------------------------------+ | FileId | FileDirectory | +--------+------------------------------+ | 1 | C://User//MySQL | | 2 | D://WebsiteImage//image1.jpg | | 3 | E://Java//AdvancedJava | +--------+------------------------------+ 3 rows in set (0.00 sec)
这是在字段中查找/替换字符串的查询-
mysql> update FindReplaceDemo -> set FileDirectory =REPLACE(FileDirectory,'E://Java//AdvancedJava','E://Java//SpringAndHibernateFramework'); Rows matched: 3 Changed: 1 Warnings: 0
现在,让我们再次检查表记录。查询如下-
mysql> select *from FindReplaceDemo;
输出如下,并带有替换值-
+--------+---------------------------------------+ | FileId | FileDirectory | +--------+---------------------------------------+ | 1 | C://User//MySQL | | 2 | D://WebsiteImage//image1.jpg | | 3 | E://Java//SpringAndHibernateFramework | +--------+---------------------------------------+ 3 rows in set (0.00 sec)