Is changing a DOUBLE column max digits in MYSQL instantaneous?-CodePudding

If i modify the max number of allowed digits in MYSQL 5.7 from double(8,2) to double(12,2), is the change immediate or will it need to process all rows??

CodePudding user response：

You can test this to see if it can be changed as an instant change:

mysql> create table mytable (id serial primary key, d double(8,2));

mysql> alter table mytable modify column d double(25,2), algorithm=inplace, lock=none;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1

The options algorithm=inplace, lock=none mean you want the alter to run as an instant change, without performing a table copy. By default, MySQL runs the DDL change in that mode if the change can be done in that mode.

If you request it explicitly, but the change cannot be done in that mode, then you'll get an error.

For example:

mysql> alter table mytable modify column d float(8,2), algorithm=inplace, lock=none;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. 
Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

In this example I'm changing the 8-byte DOUBLE to a 4-byte FLOAT. Any change to the size of a data type cannot be done without copying the table. So the request to do it as an instant change fails and the error shown is returned.

So if you're in doubt about whether a given change can be done instantly, you can use this method to test it. You don't have to do the test against your production table! I did this test on my local instance, without even adding any data to the table. I just created an empty table as I showed above, and ran the DDL.

You should read https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html for details on which types of DDL changes can be done inplace.

CodePudding user response：

As written in the MySQL Reference Manual all ALTER Table statements needs processing time. On top of this MySQL will convert the data:

For data type changes using CHANGE or MODIFY, MySQL tries to convert existing column values to the new type as well as possible.

Therefore all Columns will be visited.

CodePudding user response：

Beside the fact that specifying number of digits for floating point data types is deprecated, a change from double(8,2) to double(12,2) doesn't change the column type, it is still a double precision 8-byte number, so not even a single row will change it's value.

Example:

CREATE TABLE t1 (a double(6,2));
INSERT INTO t1 VALUES (1234.56);

# change the precision
ALTER TABLE t1 change a a double(4,2);
INSERT INTO t1 VALUES (1234.56);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
SELECT a FROM t1;
 --------- 
| a       |
 --------- 
| 1234.56 |
 ---------

Even if 1234.56 doesn't fit in double(4,2) it is still unchanged.