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.
2
Answers
I think you have a few options here.
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.
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).
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.
(That number is 231-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: