更改此类型的正确格式的列类型和设置值

Question

我有一个具有整数值的列NODE。如何更改列类型以具有以下格式的更改：

Old column: New Column: 
NODE   NODE 
1   N0C00001 
127   NOC00127 
13   NOC00013 

Answer 1

您可以试试这样。选择CONCAT（'N0C'，LPAD（'1'，5,0））;

语法：SELECT CONCAT('N0C', LPAD(your_column_name,5,0)) from table_name;

SQL：SELECT CONCAT('N0C', LPAD(old_columnname_NODE,5,0)) as new_columnname_NODE from table_name;

更新SQL：update table_name set NODE = CONCAT('N0C', LPAD(old_columnname_NODE,5,0));

谢谢。

Answer 2

在这里你可以怎么做，

mysql> select concat(left('N0C0000',length('N0C0000')-length('1')),'1') as r; 
+---------+ 
| r  | 
+---------+ 
| N0C0001 | 
+---------+ 
1 row in set (0.00 sec) 

现在改变这个更新命令会是什么样子

update table_name 
set node = concat(left('N0C0000',length('N0C0000')-length(node)),node); 

下面是测试情况

mysql> select * from test ; 
+------+ 
| node | 
+------+ 
| 1 | 
| 127 | 
| 13 | 
+------+ 
3 rows in set (0.00 sec) 

mysql> update test set node = concat(left('N0C0000',length('N0C0000')-length(node)),node); 
Query OK, 3 rows affected (0.05 sec) 
Rows matched: 3 Changed: 3 Warnings: 0 

mysql> select * from test ; 
+---------+ 
| node | 
+---------+ 
| N0C0001 | 
| N0C0127 | 
| N0C0013 | 
+---------+ 
3 rows in set (0.00 sec)