MySQL Cookbook, 2nd Edition

Guessing Table Structure from a Datafile

Problem

Someone gives you a datafile and says, “Here, put this into MySQL for me.” But no table yet exists to hold the data.

Solution

Write the CREATE TABLE statement yourself. Or use a utility that guesses the table structure by examining the contents of the datafile.

Discussion

Sometimes you need to import data into MySQL for which no table has yet been set up. You can create the table yourself, based on any knowledge you might have about the contents of the file. Or you might be able to avoid some of the work by using guess_table.pl, a utility located in the transfer directory of the recipes distribution. guess_table.pl reads the datafile to see what kind of information it contains, and then attempts to produce an appropriate CREATE TABLE statement that matches the contents of the file. This script is necessarily imperfect, because column contents sometimes are ambiguous. (For example, a column containing a small number of distinct strings might be a VARCHAR column or an ENUM.) Still, it’s often easier to tweak the CREATE TABLE statement that guess_table.pl produces than to write the entire statement from scratch. This utility also has a diagnostic function, although that’s not its primary purpose. For example, you might believe a column contains only numbers, but if guess_table.pl indicates that it should be created using a VARCHAR type, that tells you the column contains at least one nonnumeric value.

guess_table.pl assumes that its input ...