Database Reference
In-Depth Information
As it processes the document, the script generates and executes the following set of
statements:
INSERT
INTO
expt
(
subject
,
test
,
score
)
VALUES
(
'Jane'
,
'A'
,
'47'
)
INSERT
INTO
expt
(
subject
,
test
,
score
)
VALUES
(
'Jane'
,
'B'
,
'50'
)
INSERT
INTO
expt
(
subject
,
test
)
VALUES
(
'Jane'
,
'C'
)
INSERT
INTO
expt
(
subject
,
test
)
VALUES
(
'Jane'
,
'D'
)
INSERT
INTO
expt
(
subject
,
test
,
score
)
VALUES
(
'Marvin'
,
'A'
,
'52'
)
INSERT
INTO
expt
(
subject
,
test
,
score
)
VALUES
(
'Marvin'
,
'B'
,
'45'
)
INSERT
INTO
expt
(
subject
,
test
,
score
)
VALUES
(
'Marvin'
,
'C'
,
'53'
)
INSERT
INTO
expt
(
subject
,
test
)
VALUES
(
'Marvin'
,
'D'
)
Note that these statements do not all insert the same number of columns. MySQL will
set the missing columns to their default values.
11.11. 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
Use a utility that guesses the table structure by examining the datafile contents.
Discussion
Sometimes you must import data into MySQL for which no table has yet been set up.
You can create the table yourself, based on any knowledge you 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, 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 exam‐
ple, a column containing a small number of distinct strings might be a
VARCHAR
column
or an
ENUM
.) Still, it may be easier to tweak the
CREATE
TABLE
statement that
guess_table.pl
produces than to write the statement from scratch. This utility also has diagnostic value,
although that's not its primary purpose. For example, if you believe a column contains
only numbers, but
guess_table.pl
indicates that it should be a
VARCHAR
column, that tells
you the column contains at least one nonnumeric value.
guess_table.pl
assumes that its input is in tab-delimited, linefeed-terminated format. It
also assumes valid input because any attempt to guess data types based on possibly
flawed data is doomed to failure. This means, for example, that if a date column is to be
recognized as such, it should be in ISO format. Otherwise,
guess_table.pl
may charac‐
