使用 MySQL UPDATE 多次更新同一行中的同一列 [英] Updating the same column in the same row multiple times with MySQL UPDATE

问题描述

我想更改job_title 中的所有单词.

I want to change all the words in the job_title.

在我下面的示例中，job_title 是:

In my example below the job_title is:

艺术部工作经历/greenswoman在行86619.

代码如下:

设置自动提交 = 1;

UPDATE tvt_job_title SET job_title = REPLACE(LOWER(job_title),'art','Art') WHERE job_title_id = 86619;更新 tvt_job_title SET job_title = REPLACE(LOWER(job_title),'work','Work') WHERE job_title_id = 86619;更新 tvt_job_title SET job_title = REPLACE(LOWER(job_title),'department','Department') WHERE job_title_id = 86619;更新 tvt_job_title SET job_title = REPLACE(LOWER(job_title),'experience','Experience') WHERE job_title_id = 86619;更新 tvt_job_title SET job_title = REPLACE(LOWER(job_title),'greenswoman','Greenswoman') WHERE job_title_id = 86619;

select job_title from tvt_job_title where job_title_id = 86619;

结果:

艺术部工作经历/Greenswoman 只有Greenswoman得到纠正！为什么所有的词都没有得到纠正?

art department work experience/Greenswoman only Greenswoman gets corrected! Why don't all the words get corrected?

输出:

UPDATE tvt_job_title SET job_title = REPLACE(LOWER(job_title),'art','Art') WHERE job_title_id = 86619 1 行受影响的行匹配:1 更改:1 警告:0

UPDATE tvt_job_title SET job_title = REPLACE(LOWER(job_title),'work','Work') WHERE job_title_id = 86619 1 行受影响的行匹配:1 更改:1 警告:0

UPDATE tvt_job_title SET job_title = REPLACE(LOWER(job_title),'department','Department') WHERE job_title_id = 86619 1 行受影响的行匹配:1 更改:1 警告:0

UPDATE tvt_job_title SET job_title = REPLACE(LOWER(job_title),'experience','Experience'), abbreviation = REPLACE(LOWER(abbreviation),'experience','Experience') WHERE job_title_id = 86619 1 行(s) 匹配的受影响行:1 更改:1 警告:0

UPDATE tvt_job_title SET job_title = REPLACE(LOWER(job_title),'greenswoman','Greenswoman'), abbreviation = REPLACE(LOWER(abbreviation),'greenswoman','Greenswoman') WHERE job_title_id = 86619 1 行(s) 匹配的受影响行:1 更改:1 警告:0

select job_title from tvt_job_title where job_title_id = 86619 1 row(s)返回

推荐答案

MySQL 的 REPLACE() 函数不区分大小写.

MySQL's REPLACE() function is not case-insensitive.

我发现这个博客是关于一个用户创建了一个存储函数来进行不区分大小写的字符串替换.这很尴尬.https://pento.net/2009/02/15/case-insensitive-replace-for-mysql/

I found this blog about a user who created a stored function to do case-insensitive string replace. It's awkward. https://pento.net/2009/02/15/case-insensitive-replace-for-mysql/

对于此任务，如果您有特殊的字符串比较要求，您可能会发现只需编写一个脚本来一次一行地完成它会更容易.

For this task, if you have special string comparison requirements, you might find it easier to just write a script to do it one row at a time.

这篇关于使用 MySQL UPDATE 多次更新同一行中的同一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！