可以通过在列上应用 trim() 函数以及 mysql update 语句来完成。下面的示例会更清楚地说明这一点。
示例假设我们有一个表“employee”，其前缀为“dept.”，其中包含列“department”的所有值如下 -
mysql> select * from employee;+------+----------------+------------+----------------------+| id | name | address | department |+------+----------------+------------+----------------------+| 100 | raman | delhi | it dept. || 101 | mohan | haryana | history dept. || 102 | shyam | chandigarh | english dept. || 103 | sukhjeet singh | patiala | computer engg. dept. || 104 | bimal roy | calcutta | computer engg. dept. |+------+----------------+------------+----------------------+5 rows in set (0.01 sec)
现在，下面的查询将从“department”列中删除前缀“dept.”，并更新表格。
mysql> update employee set department = trim(trailing 'dept.' from department);query ok, 5 rows affected (0.10 sec)rows matched: 5 changed: 5 warnings: 0mysql> select * from employee;+------+----------------+------------+-----------------+| id | name | address | department |+------+----------------+------------+-----------------+| 100 | raman | delhi | it || 101 | mohan | haryana | history || 102 | shyam | chandigarh | english || 103 | sukhjeet singh | patiala | computer engg. || 104 | bimal roy | calcutta | computer engg. |+------+----------------+------------+-----------------+5 rows in set (0.00 sec)
上面的结果集显示列值中没有前缀“dept.”。
以上就是在mysql中，如何从整个列的值中删除特定前缀并更新它们？的详细内容。