Is it bad practice to use MySQL unsigned integers? [closed]

Question

I need to store integers which will only be positive, and am currently using MySQL. I made the values UNSIGNED "just in case" I ever had so many records, however, it is doubtful I will ever get there.

I am curious whether it would be better practice to no use the UNSIGNED flag just in case I later wish to change databases to MS SQL, etc which doesn't support unsigned integers.

Is it considered bad practice to use MySQL unsigned integers?

Answer 1

I don't think it is a bad practice to use data types that are appropriate to the data being stored.

You bring up a very good issue about portability to other databases. If this is a concern, then you should try to adhere either to ANSI standard functionality or to functionality that you know is similar between the databases.

Moving code from MySQL to SQL Server can involve a lot of more significant effort than just the difference between signed and unsigned integers. There are numerous places where functions are different, non-existent, or behave different. Just to give a smattering of examples:

• cast() takes different types in MySQL versus SQL Server (as signed vs as int)
• MySQL has silent error handling in the case of 123 + '456b' whereas SQL Server will produce an error.
• The string concatenation operator is different in the two databases.
• datediff() takes different arguments in the two databases

So, the difference between signed and unsigned will be a minor concern in a conversion. You will need to write your code carefully if you are planning such a change.