MySQL MySQL查询以在每个条目前添加字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/156641/
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 query to prepend character to each entry
提问by Stuart
I have a table of users which has a username column consisting of a six digit number e.g 675381, I need to prepend a zero to each of these usernames e.g. 0675381 would be the final output of the previous example, is there a query that could handle this?
我有一个用户表，它的用户名列由一个六位数组成，例如 675381，我需要在每个用户名前面加上一个零，例如 0675381 将是上一个示例的最终输出，是否有可以处理的查询这个？
回答by daniels
UPDATE Tablename SET Username = Concat('0', Username);
回答by f13o
what type is the column of?
列是什么类型的？
if it's string type, try something like this:
如果是字符串类型，请尝试以下操作：
UPDATE your_table SET column_name=concat('0',column_name);
回答by Mike Woodhouse
You mean "prepend" ? i.e. add it on the front?
您指的是 “prepend” 吗？即在前面添加它？
Is the column numeric? Do you always want 7 characters output?
列是数字吗？你总是想要 7 个字符的输出吗？
Assuming that, something like this would work for a query:
假设这样的事情适用于查询：
select LPAD(CONVERT(username, CHAR), 7, '0')
If the column is characters, the CONVERT() part is unnecessary, just LPAD the username.
如果列是字符，则不需要 CONVERT() 部分，只需 LPAD 用户名。
If you want to permanently modify the value in the table, you'll need to ensure the column is a character type and UPDATE using the above.
如果要永久修改表中的值，则需要确保该列是字符类型并使用上述方法进行 UPDATE。
回答by Darryl Hein
You might want to use CONCAT_WS('', '0', Username) because if there is a null value, then you'll end up with NULL instead of '0'. This probably isn't a problem, but something I've learnt the hard way.
您可能想要使用 CONCAT_WS('', '0', Username) 因为如果有空值，那么您最终会得到 NULL 而不是 '0'。这可能不是问题，但我已经通过艰难的方式学到了一些东西。
