MySQL 查找/替换字段中的字符串?
mysqlmysqli database
要查找/替换字段中的字符串，语法如下 −
update yourTableName set yourColumnName =REPLACE(yourColumnName,yourOldValue,yourNewValue);
为了理解上述语法，让我们创建一个表。创建表的查询如下 −
mysql> create table FindReplaceDemo -> ( -> FileId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> FileDirectory text -> ); Query OK, 0 rows affected (0.92 sec)
现在，您可以使用 insert 命令在表中插入一些记录。查询如下 −
mysql> insert into FindReplaceDemo(FileDirectory) values('C://User//MySQL');
Query OK, 1 row affected (0.19 sec)
mysql> insert into FindReplaceDemo(FileDirectory) values('D://WebsiteImage//image1.jpg');
Query OK, 1 row affected (0.12 sec)
mysql> insert into FindReplaceDemo(FileDirectory) values('E://Java//AdvancedJava');
Query OK, 1 row affected (0.20 sec)
使用 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'); Query OK, 1 row affected (0.12 sec) 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)
