MySQL Cookbook, 4th Edition

Chapter 14. Validating and Reformatting Data

14.0 Introduction

The previous chapter, Chapter 13, focused on methods for moving data into and out of MySQL, by reading lines and breaking them into separate columns. In this chapter, we’ll focus on the content rather than structure issues. For example, if you don’t know whether the values contained in a file or received via web form are legal, preprocess them to check or reformat them:

• It’s often a good idea to validate data values to make sure they’re legal for the data types into which you store them. For example, you can make sure that values intended for INT, DATE, and ENUM columns are integers, dates in ISO format (YYYY-MM-DD), and legal enumeration values, respectively.

• Data values may need reformatting. You might store credit card values as a string of digits but permit users of a web application to separate blocks of digits by spaces or dashes. These values must be rewritten before storing them. Rewriting dates from one format to another is especially common, for example, if a program writes dates in MM-DD-YY format to ISO format for import into MySQL. If a program understands only date and time formats and not a combined date-and-time format (such as MySQL uses for the DATETIME and TIMESTAMP data types), you must split date-and-time values into separate date and time values.

The chapter deals with formatting and validation issues primarily within the context of checking entire files, but many of the techniques discussed here can ...