To load data in MySQL, you need to create a data file, which specifies what data should be loaded and then run a command that specifies how data should be loaded.
Creating the Data File
Each line in the data file specifies one tuple to be loaded into <tableName>. It lists, in order, values for the attributes in the list specified in the LOAD command (described below), separated by <separator>. As a concrete example, consider relation Likes(drinker, beer) and a data file likes.dat that might look like:
zarko|Guinness nick|Bud nick| BudLiteAs the result of loading likes.dat, the following tuples are inserted into Likes:
('zarko', 'Guinness') ('nick', 'Bud') ('nick', ' BudLite')Warning: Note that the third line of likes.dat has a blank after "|". This blank is not ignored by the loader. The value to be loaded for attribute beer is ' Bud', a four-character string with a leading blank. It is a common mistake to assume that 'Bud', a three-character string with no leading blank, will be loaded instead. This can lead to some very frustrating problems that you will not notice until you try to query your loaded data, because ' Bud' and 'Bud' are different strings.
Loading Your Data
A simple load command has the following form:
LOAD DATA LOCAL INFILE "<dataFile>" REPLACE INTO TABLE <tableName> FIELDS TERMINATED BY '<separator>' (<list of all attribute names to load>)
LOAD DATA LOCAL INFILE "likes.dat" REPLACE INTO TABLE Likes FIELDS TERMINATED BY '|' (drinker, beer)
For more information on the LOAD command consult the online MySQL documentation.
Entering NULL Values
You may specify NULL values simply by entering \N. For example, if we were entering integer values into a table with schema (a, b, c) specified in the LOAD command, the following lines in the data file:
3|\N|5 \N|2|4 1|\N|6 \N|\N|7
would result in inserting the following tuples in the relation:
(3, NULL, 5) (NULL, 2, 4) (1, NULL, 6) (NULL, NULL, 7)
Keep in mind that any primary keys or other constraints requiring that values be non-NULL will reject tuples for which those attributes are unspecified.
If you do not wish to enter values for any row of a given column, you can, as mentioned above, leave that column out of the attribute list altogether.