要进行替换,请使用REPLACE()MySQL函数。由于您需要为此更新表,因此可以将UPDATE()函数与SET子句一起使用。
以下是语法-update yourTableName set yourColumnName=replace(yourColumnName,yourOldValue,yourNewValue);
让我们首先创建一个表-create table DemoTable
(
FirstName varchar(100),
CountryName varchar(100)
);
使用插入命令在表中插入一些记录-insert into DemoTable values('John','AUS');
insert into DemoTable values('Bob','AUS');
insert into DemoTable values('Chris','US');
insert into DemoTable values('David','UK');
insert into DemoTable values('Adam','US');
使用select语句显示表中的所有记录-select *from DemoTable;
这将产生以下输出-+-----------+-------------+
| FirstName | CountryName |
+-----------+-------------+
| John | AUS |
| Bob | AUS |
| Chris | US |
| David | UK |
| Adam | US |
+-----------+-------------+
5 rows in set (0.00 sec)
以下是替换特定值的查询-update DemoTable set CountryName=replace(CountryName,'AUS','US');
Rows matched: 5 Changed: 2 Warnings: 0
让我们再次检查表记录-select *from DemoTable;
这将产生以下输出-+-----------+-------------+
| FirstName | CountryName |
+-----------+-------------+
| John | US |
| Bob | US |
| Chris | US |
| David | UK |
| Adam | US |
+-----------+-------------+
5 rows in set (0.00 sec)