dongliling6336
2014-01-23 03:05
浏览 107
已采纳

SQL TRIM特定字段(和变量集)中的所有值

Excuse me if I ask the obvious, but I am quite mySQL illiterate .

I am referring to a standard wordpress DB install, although this is not a wordpress specific question, but more mySQL general knowledge ..

If I want to change all the values of a certain field across all posts , I usually do :

UPDATE `wp_postmeta` SET `meta_value` = replace(meta_value, 'old_value', 'new_value') WHERE `meta_key` LIKE 'my_meta_key'

The problem is as follows :

In that specific DB , the INITIAL values were inserted with a white space prefix and suffix , e.g. :

"city name" 

was actually inserted as

" city name "

of course , being a PHP person, I immediately thought of the trim() command - which I found out exists also in mySQL.

SO now I thought I could just do

UPDATE `wp_postmeta` SET `meta_value` = TRIM('meta_value') WHERE `meta_key` LIKE 'my_meta_key'

But It is not working ..

What Am I doing wrong ? I know that mySQL is "ticks sensetive" ( so to speak ) But I tried both 'meta_value' and `meta_value' ( sorry, markdown limitations seems to truncate the backticks)

Doing replace(meta_value, ' ', '') is dengaurous because some cities havea spaces in their name, and LTRIM RTRIM is somewhat not working for me as well

I also know there is a way to set a variable in sql ( @my_var ) but what is the correct syntax of doing that in my case ?

SET @my_var = `meta_value`

and then

UPDATE `wp_postmeta` TRIM(@my_var) WHERE `meta_key` LIKE 'my_meta_key'

I of course know how to fix it in a php loop (with trim() ) but I would like to learn what is the best way to do it in mySQL..

图片转代码服务由CSDN问答提供 功能建议

打扰一下,如果我问明显,但我是mySQL文盲。 < p>我指的是一个标准的wordpress DB安装,虽然这不是一个wordpress特定的问题,但更多mySQL的一般知识..

如果我想改变一定的所有值 所有帖子中的字段,我通常会这样做:

  UPDATE`wp_postmeta` SET`meta_value` = replace(meta_value,'old_value','new_value')WHERE`metay_key` LIKE'  my_meta_key'
   
 
 

问题如下:

在该特定数据库中,INITIAL值插入了空格 前缀和后缀,例如 :

 “city name”
   
 
 

实际插入为

 “city name”
   
 
 

当然,作为一个PHP人员,我立刻想到了 trim()命令 - 我发现也在mySQL 中。

现在我觉得我可以做到

  UPDATE`wp_postmeta` SET`metaval_value` = TRIM('meta_value')WHERE`  meta_key` LIKE'my_meta_key'
   
 
 

但它不起作用..

我做错了什么? 我知道mySQL是“滴答敏感”(可以这么说)但我尝试了'meta_value'`meta_value'(对不起,降价限制似乎截断了反引号)

执行 replace(meta_value,'','')是不礼貌的,因为有些城市名称中有空格, LTRIM RTRIM 对我来说有点不起作用

我也知道有一种方法可以在sql中设置变量( @my_var ) 但是正确的语法是什么 在我的情况下这样做?

  SET @my_var =`meta_value` 
   
 
 

然后

  UPDATE`wp_postmeta`TRIM(@my_var)WHERE`metanet_key` LIKE'my_meta_key'
   
 
 

我当然知道如何 修复它在php循环中(使用trim())但我想知道在mySQL中执行它的最佳方法是什么..

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • drjmrg8766 2014-01-23 03:07
    已采纳

    To refer to the column, you need backticks:

    UPDATE `wp_postmeta`
        SET `meta_value` = TRIM(`meta_value`)
        WHERE `meta_key` LIKE 'my_meta_key';
    

    Your expression TRIM('meta_value') is trimming the string 'meta_value', not the value in the column.

    点赞 打赏 评论

相关推荐 更多相似问题