MYSQL 更新查询以删除空格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2363420/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL update query to remove spaces
提问by andreas
One of my clients has added a number of account numbers in one of our applications. While trying to make a transaction the transaction fails due to the spaces at the end of the account number. How do i update his records in the Mysql database to remove all the spaces from accounts that have them at the end, without making him delete the clients and re-adding the accounts? the structure of the table(s) is as follows:
我的一位客户在我们的一个应用程序中添加了多个帐号。在尝试进行交易时，由于帐号末尾有空格，交易失败。我如何更新他在 Mysql 数据库中的记录以从最后拥有它们的帐户中删除所有空格，而不让他删除客户端并重新添加帐户？表的结构如下：
Not sure how to structure the query or the function of the mysql
不知道如何构造查询或 mysql 的功能
The account table:
账户表：
the account table:
CUSTOMER_ID
ACCOUNTNUMBER
TXT
CURRENCY_NO
USER_ID
ACTIVE_FLAG
USER_DATE
ben_bic_address
int_bic_address
the admin table
ADM_USER_ID
LOCATION_CD
LANG
USER_NAME
USER_LOGIN
USER_PASSWORD
GROUP_CODE
USER_ID
USER_DATE
ACTIVE
COUNTER
connected
IP
And the customer table:
CUSTOMER_ID
COUNTRY_NO
USER_ID
CUSTOMER_NAME
ACTIVE_FLAG
回答by Daniel Vassallo
If you need to RTRIM()all the accounts of a particular customer, you can use a JOINwith your UPDATE statement as follows:
如果您需要RTRIM()特定客户的所有帐户，您可以使用JOINUPDATE 语句，如下所示：
UPDATE
accounts_table
INNER JOIN
customers_table ON (accounts_table.user_id = customers_table.user_id)
SET
accountnumber = RTRIM(accountnumber)
WHERE
customers_table.customer_id = 'customer id';
If you do not have many records in accounts_table, and you want to make sure that all the accountnumbervalues are trimmed, you can simply apply the trim to all the records as follows:
如果accounts_table 中没有很多记录，并且您想确保所有accountnumber值都被修剪，您可以简单地将修剪应用于所有记录，如下所示：
UPDATE
accounts_table
SET
accountnumber = TRIM(accountnumber);
回答by Layke
You would use TRIM and update.
您将使用 TRIM 和更新。
Just using this should do it.
只需使用这个就可以了。
UPDATE accountTable
SET ACCOUNTNUMBER = RTrim(ACCOUNTNUMBER)
回答by a'r
If you have foreign key constraints, then you may have to remove them whilst you make the modifications.
如果您有外键约束，那么您可能需要在进行修改时删除它们。
The following query will change the records in the accounts table:
以下查询将更改帐户表中的记录：
update accounts set accountnumber = rtrim(accountnumber);
回答by TechyFlick
Try this
尝试这个
TRIM() Function removes extra (or) additional spaces from a string.
TRIM() 函数从字符串中删除额外（或）额外的空格。
UPDATE item_listing SET product_quantity = TRIM(product_quantity);
Result:
结果：
Product_quantity = " 50 " => 50
产品数量 = " 50 " => 50
