我有下一个列,小数点(20,2)在mysql表中:
no
10.01
10.09
10.10
10.11
10.19
10.99
将该小数值更新为:
的最简单方法是什么no
10.001
10.009
10.010
10.011
10.099
..
10.100
10.101
如果我将列更改为十进制(20,3),我将获得下一个数字:10.010,10.090 ... 10.990等。每个数字必须唯一。如果MySQL无法做到,该如何使用PHP?
您可以使用这样的查询。首先将您的字段设置为十进制(20,3):以2个步骤进行操作:首先添加1个以防止重复条目。第二减1
第一步
UPDATE youtTable
set val =
CAST(val as unsigned integer) +1
+ (val - CAST(val as unsigned integer)) / 10;
第二步
UPDATE youtTable
set val = val -1;
但是,如果您首先更改最低数字,也可以使用一个查询进行操作:
UPDATE yourTable
set val =
CAST(val as unsigned integer)
+ (val - CAST(val as unsigned integer)) / 10
ORDER by val ASC;
示例
mysql> SELECT CAST(10.19 as unsigned integer) + (10.19 - CAST(10.19 as unsigned integer)) / 10;
+----------------------------------------------------------------------------------+
| CAST(10.19 as unsigned integer) + (10.19 - CAST(10.19 as unsigned integer)) / 10 |
+----------------------------------------------------------------------------------+
| 10.019000 |
+----------------------------------------------------------------------------------+
1 row in set (0,00 sec)
mysql>
我会按以下
进行此操作- 首先取下桌子。
- 然后添加一个带十进制(20,3)的新列
- 然后通过执行一些字符串操作来更新来自旧列的值的列。
- 放下旧列
- 重命名新列。
这是一个测试用例
mysql> create table test (no decimal(20,2));
Query OK, 0 rows affected (0.20 sec)
mysql> insert into test values('10.01'),('10.09'),('10.10'),('10.11'),('10.19'),('10.99');
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from test;
+-------+
| no |
+-------+
| 10.01 |
| 10.09 |
| 10.10 |
| 10.11 |
| 10.19 |
| 10.99 |
+-------+
6 rows in set (0.00 sec)
mysql> alter table test add column no_new decimal(20,3);
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test;
+-------+--------+
| no | no_new |
+-------+--------+
| 10.01 | NULL |
| 10.09 | NULL |
| 10.10 | NULL |
| 10.11 | NULL |
| 10.19 | NULL |
| 10.99 | NULL |
+-------+--------+
6 rows in set (0.00 sec)
然后
mysql> update test set no_new = concat(substring_index(no,'.',1),'.',concat('0',substring_index(no,'.',-1)));
Query OK, 6 rows affected (0.05 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> select * from test;
+-------+--------+
| no | no_new |
+-------+--------+
| 10.01 | 10.001 |
| 10.09 | 10.009 |
| 10.10 | 10.010 |
| 10.11 | 10.011 |
| 10.19 | 10.019 |
| 10.99 | 10.099 |
+-------+--------+
6 rows in set (0.00 sec)
mysql> alter table test drop column no;
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test change no_new no decimal(20,3);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test ;
+--------+
| no |
+--------+
| 10.001 |
| 10.009 |
| 10.010 |
| 10.011 |
| 10.019 |
| 10.099 |
+--------+
6 rows in set (0.00 sec)
*****Using MySql*****
--> SELECT FORMAT('COLUMN NAME',DECIMAL VALUE);
*****FOR EXAMPLE:*****
--> SELECT FORMAT(NO,3);
*****QUERY*****
Create table '#test1'
(no decimal(5,3));
insert into '#test1' values
('10.01'),
('10.09'),
('10.10'),
('10.11'),
('10.19'),
('10.99');
****RESULT****
[![DEMO IMAGE][1]][1]
[1]: https://i.stack.imgur.com/AzZpB.jpg