- Digging up the past
- Happy Monkey
- So, who likes that new Y! nav bar on Flickr?
- Point Reyes
- A few handy git aliases
Polluting the internet since 2004
Long story short: If you have a closed system and have to store signed integers in a MySQL varchar field you can still get the benefits of the signed integer by type casting the field during the query.
Sometimes when writing functionality for a system like WordPress that has a definite benefit to using the built in storage mechanism the data can end up being stored in less than optimal formats. For example, signed integers in a varchar field. Fortunately it can still be made to work using the built in MySQL CAST function.
First, some test data if you want to follow along at home:
Now, in the following query I select the number field and cast it as a signed integer. Pretty straight forward.
The results are this:
+--------+-----------+ | number | sortfield | +--------+-----------+ | 10 | 10 | | 4 | 4 | | 2 | 2 | | 1 | 1 | | -1 | -1 | | -2 | -2 | | -4 | -4 | | -10 | -10 | +--------+-----------+ 8 rows in set (0.00 sec)
This was done on MySQL 5.0.83 and I haven’t done a heck of a lot of research but it certainly seems promising (and, according to Google, a relatively widely accepted practice).
CAST can be used to convert to the following types:
So, while your best storage option may not be the optimal storage option, you don’t have to lose the benefit of doing as much work on the SQL server as possible when selecting your data sets.