- Word of the day: Ouch.
- I’m done with the cancer, but the cancer isn’t done with me
- Goodbye, my dopey dog.
- So long, Alex King
Polluting the internet since 2004
August 10, 2009 | Computers
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.
Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.
[…] Type Casting in MySQL, One Possible Application – very cool. […]
August 16, 2009 | 10:20 pm
Comments are closed