MySQL查询将字符添加到每个条目

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将是前一个示例的最终输出，是否有可以处理此问题的查询？

4 个解决方案

#1

UPDATE Tablename SET Username = Concat('0', Username);

#2

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);

#3

You mean "prepend" ? i.e. add it on the front?

你的意思是“前置”？即将它添加到正面？

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。

#4

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'。这可能不是问题，但我学到了很多东西。

#1

UPDATE Tablename SET Username = Concat('0', Username);

#2