Friday, May 8, 2009

MySQL Auto Data Conversion

So I've been researching MySQL, and I have run into something that kinda boggles my mind. Whenever you give MySQL the wrong datatype for a field, MySQL attempts to do autoconversion of your data. This in itself isn't a bad thing, and some of them may make sense, such as doing string to int or int to string. However, if you pass MySQL data that it is out of bounds, MySQL chooses data that is in bounds, and places that data in the field instead. So if you have a field defined as a tinyint, whose bounds are -127 to 127, and you pass it 1,000, MySQL will input 127, and consider it a success. 

You have to write your own MySQL specific data validation in your application BEFORE you pass it to MySQL..... Say it with me..... WHAT??? The whole idea with abstraction and error catching is that each layer of your software stack should be able to pass "something" to another layer and receive back success or failure, at which point the originating layer can decide how to handle it. By having your application layer have to do database validation checks BEFORE passing data to MySQL, you loose the benefits of abstraction, and by definition, you loose the benefits of modern software engineering. 

Somebody out there explain to my how MySQL's handling of out of bounds data is a good idea, cause I don't get it.

No comments:

Post a Comment