We are using phpmyadmin database to store our user datas. There is a column named 'gold' and it is int(11). So when the users max out their golds, it becames negative. So I want to change the column type to float or varchar. What happens to our user datas if I change it like this?

I haven't tried to change it because it may broke our users datas. Even though I had a backup, I was afraid to try it.

CodePudding user response：

In MySQL, integers don't overflow to negative values. If you try to store an integer that is too large to fit in the data type, it will return an error if you have strict mode set (this is the default).

mysql> create table mytable (n int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into mytable set n = 4000000000;
ERROR 1264 (22003): Out of range value for column 'n' at row 1

If you disable strict mode, MySQL still does not wrap around to negative numbers. It truncates the value to the largest value that fits in a signed integer. I prefer using strict mode, because the non-strict behavior doesn't have many good uses.

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into mytable set n = 4000000000;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
 --------- ------ -------------------------------------------- 
| Level   | Code | Message                                    |
 --------- ------ -------------------------------------------- 
| Warning | 1264 | Out of range value for column 'n' at row 1 |
 --------- ------ -------------------------------------------- 
1 row in set (0.00 sec)

mysql> select * from mytable;
 ------------ 
| n          |
 ------------ 
| 2147483647 |
 ------------ 

(That number is 2³¹-1, which is the largest value for a signed 32-bit integer type.)

You can alter tables to change data types, if there's a way to cast one type as another. Casting an INT to a BIGINT for example is easy because BIGINT includes every value that can be in an INT. Casting INT to VARCHAR is also possible without data loss.

Casting an INT to a FLOAT may not give you what you want, because some values turn into approximations. Read https://dev.mysql.com/doc/refman/8.0/en/problems-with-float.html

Casting a BIGINT to an INT could cause problems because large values would not fit in an INT, and these would either be truncated or cause errors.

Others have given good advice: test it yourself. Don't do any change to your important data until you have tried it on scratch data first until you understand how it works and if there are any limitations. This general advice applies to many types of programming work. Don't use any feature or operation for the first time on the data that is most important and difficult to restore if you get it wrong.

To be safe, you may want to choose an incremental approach:

1. Add a new column with the data type you want to use
2. UPDATE all rows to copy the value from the old column to the new column, allowing type casting to occur.
3. Inspect the result, to make sure it looks like what you expect on all rows.
4. Drop the old column and then rename the new column to be the same name as the old column.

CodePudding user response：

I think you have a few options here.

1. Create a "dummy" table, and simply just try it on that table, see what happens.
2. This post suggests that simply changing the type will force MySQL to cast the int to a float, this conversion however is lossy, and some 32 bit integer values cannot be represented in 32 bit float without loss of precision.

How much gold is a user expected to acquire? It seems to me you may simply want to use a 64bit integer (Type BIGINT), which can represent all integers up to (and a bit higher) than 10^19.