Have a development colleague after the validation environment for a table to change the int field to decimal (10,4), prompted 1264 out of the range value error, consult the DBA why this error, what impact on the data.
When I received this question, the first reaction was due to the conversion of the field type, the length is not enough to occur the data overflow.
Therefore, the following simulation of the problem to test:
1. Build Test table
CREATE TABLE ' Zeng ' (
' _id ' int (one) not NULL auto_increment COMMENT ' primary key ',
' A ' int () DEFAULT NULL,
PRIMARY KEY (' _id ')
) Engine=innodb auto_increment=28129 DEFAULT Charset=utf8;
2. Prepare test data
Insert into Zeng (a) values (70000);
Insert into Zeng (a) values (800000);
Insert into Zeng (a) values (900000);
Insert into Zeng (a) values (9000000);
Insert into Zeng (a) values (99999999);
3. Modify the field type
ALTER TABLE Zeng Modify column A decimal (10,4) unsigned;
dba@192.168.74.60:3306: >alter table Zeng Modify column A decimal (10,4) unsigned;
Query OK, 5 rows affected, 2 warnings (0.05 sec)
Records:5 duplicates:0 Warnings:2
4. View warnings
dba@192.168.74.60:3306 >show warnings;
+---------+------+------------------------------ --------------+
| level | Code | message |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column ' A ' at row 4 |
| Warning | 1264 | Out of range value for column ' A ' at row 5 |
+---------+------+--------------------------------------------+
2 rows in Set (0.00 sec)
5. The data after the change of the query type
Dba@192.168.74.60:3306:>select * from Zeng;
+-------+-------------+
| _id | A |
+-------+-------------+
| 28129 | 70000.0000 |
| 28130 | 800000.0000 |
| 28131 | 900000.0000 |
| 28132 | 999999.9999 |
| 28133 | 999999.9999 |
+-------+-------------+
5 rows in Set (0.00 sec)
An overflow occurred in the A field record for lines four and fifth.
The maximum value for decimal (10,4) is 10 in 999999.9999,decimal (10,4), which means that the total number of digits is 10 (integer + decimal), 4 is decimal, 70000 can be represented in decimal (10,4), and is expressed as 70000.0000.
Show warnings see out of range indicates that the production exceeds this value, and it is recommended that the manufacturing environment make a copy of the table's Data create table as select before changing the field to a table, and if the out of range appears,
The data for this record has all been turned into 9. You can query the maximum length of a value for a field before you change it:
Select Max (Length (a)) from Zeng;
ensured that the M-n>=max (length (a)) in decimal (m,n) does not produce an overflow.