• MySQL.com
  • Developer Zone
  • Partners
  • Online Shop
  • Downloads
  • Documentation
  • Forums
  • Lists
  • Bugs
  • Events
  • User Groups
  • Guilds
  • Blogs
  • Support
  • Resources
  • Books
  • FAQ
MySQL 5.1 Reference Manual :: 3 Tutorial :: 3.3 Creating and Using a Database :: 3.3.3 Loading Data into a Table
  • Overview
  • MySQL Reference Manual
  • 3.23, 4.0, 4.1
  • 5.0
  • 5.1
  • MaxDB Documentation

  • MySQL 5.1 Reference Manual

  • 3.3 Creating and Using a Database
  • 3.3.1 Creating and Selecting a Database
  • 3.3.2 Creating a Table
  • 3.3.3 Loading Data into a Table
  • 3.3.4 Retrieving Information from a Table

Get the MySQL Language Reference and MySQL Administrator's Guide from MySQL Press!



Learn about new MySQL releases, technical articles, events and more.

Subscribe to the monthly MySQL Newsletter!


3.3.1. Creating and Selecting a Database

If the administrator creates your database for you when setting up your permissions, you can begin using it. Otherwise, you need to create it yourself:

mysql> CREATE DATABASE menagerie;

Under Unix, database names are case sensitive (unlike SQL keywords), so you must always refer to your database as menagerie, not as Menagerie, MENAGERIE, or some other variant. This is also true for table names. (Under Windows, this restriction does not apply, although you must refer to databases and tables using the same lettercase throughout a given query. However, for a variety of reasons, our recommended best practice is always to use the same lettercase that was used when the database was created.)

Note: If you get an error such as ERROR 1044 (42000): Access denied for user 'monty'@'localhost' to database 'menagerie' when attempting to create a database, this means that your user account does not have the necessary privileges to do so. Discuss this with the administrator or see Section 5.8, “The MySQL Access Privilege System”.

Creating a database does not select it for use; you must do that explicitly. To make menagerie the current database, use this command:

mysql> USE menagerie
Database changed

Your database needs to be created only once, but you must select it for use each time you begin a mysql session. You can do this by issuing a USE statement as shown in the example. Alternatively, you can select the database on the command line when you invoke mysql. Just specify its name after any connection parameters that you might need to provide. For example:

shell> mysql -h host -u user -p menagerie
Enter password: ********

Note that menagerie in the command just shown is not your password. If you want to supply your password on the command line after the -p option, you must do so with no intervening space (for example, as -pmypassword, not as -p mypassword). However, putting your password on the command line is not recommended, because doing so exposes it to snooping by other users logged in on your machine.


User Comments

Posted by Darl Kuhn on February 27 2006 5:46pm[Delete] [Edit]

You can use this command to view the current database that you're connected to:

mysql> select database();

Add your own comment.

3.3.2. Creating a Table

Creating the database is the easy part, but at this point it's empty, as SHOW TABLES tells you:

mysql> SHOW TABLES;
Empty set (0.00 sec)

The harder part is deciding what the structure of your database should be: what tables you need and what columns should be in each of them.

You want a table that contains a record for each of your pets. This can be called the pet table, and it should contain, as a bare minimum, each animal's name. Because the name by itself is not very interesting, the table should contain other information. For example, if more than one person in your family keeps pets, you might want to list each animal's owner. You might also want to record some basic descriptive information such as species and sex.

How about age? That might be of interest, but it's not a good thing to store in a database. Age changes as time passes, which means you'd have to update your records often. Instead, it's better to store a fixed value such as date of birth. Then, whenever you need age, you can calculate it as the difference between the current date and the birth date. MySQL provides functions for doing date arithmetic, so this is not difficult. Storing birth date rather than age has other advantages, too:

  • You can use the database for tasks such as generating reminders for upcoming pet birthdays. (If you think this type of query is somewhat silly, note that it is the same question you might ask in the context of a business database to identify clients to whom you need to send out birthday greetings in the current week or month, for that computer-assisted personal touch.)

  • You can calculate age in relation to dates other than the current date. For example, if you store death date in the database, you can easily calculate how old a pet was when it died.

You can probably think of other types of information that would be useful in the pet table, but the ones identified so far are sufficient: name, owner, species, sex, birth, and death.

Use a CREATE TABLE statement to specify the layout of your table:

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

VARCHAR is a good choice for the name, owner, and species columns because the column values vary in length. The lengths in those column definitions need not all be the same, and need not be 20. You can normally pick any length from 1 to 65535, whatever seems most reasonable to you. If you make a poor choice and it turns out later that you need a longer field, MySQL provides an ALTER TABLE statement.

Several types of values can be chosen to represent sex in animal records, such as 'm' and 'f', or perhaps 'male' and 'female'. It is simplest to use the single characters 'm' and 'f'.

The use of the DATE data type for the birth and death columns is a fairly obvious choice.

Once you have created a table, SHOW TABLES should produce some output:

mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| pet                 |
+---------------------+

To verify that your table was created the way you expected, use a DESCRIBE statement:

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

You can use DESCRIBE any time, for example, if you forget the names of the columns in your table or what types they have.

For more information about MySQL data types, see Chapter 11, Data Types.


User Comments

Posted by Larry Blanchette on September 1 2005 8:21pm[Delete] [Edit]

you can use: show create table tablename, to get the DDL;

Posted by James Carrig on January 10 2006 8:44pm[Delete] [Edit]

While it is true that VARCHAR(20) means that the lengths in the columns "need not be 20," the more direct meaning is that the maximum column length is 20. Here is actual output which may speak more clearly than words:

mysql> CREATE TABLE demo_varchar ( words VARCHAR(5));
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO demo_varchar VALUES ('abcdef');
ERROR 1406 (22001): Data too long for column 'words' at row 1

mysql> INSERT INTO demo_varchar VALUES ('abcde');
Query OK, 1 row affected (0.38 sec)

Posted by Raymond Peck on April 6 2006 10:03pm[Delete] [Edit]

Another issue is that I assume for all or most underlying table implementations a packed string table is used, so only as much space is used as required. Might want to add a link to more detailed info on the implications of various max lengths, and any variations between MyISAM, InnoDB, etc.

Add your own comment.

3.3.3. Loading Data into a Table

After creating your table, you need to populate it. The LOAD DATA and INSERT statements are useful for this.

Suppose that your pet records can be described as shown here. (Observe that MySQL expects dates in 'YYYY-MM-DD' format; this may be different from what you are used to.)

name owner species sex birth death
Fluffy Harold cat f 1993-02-04  
Claws Gwen cat m 1994-03-17  
Buffy Harold dog f 1989-05-13  
Fang Benny dog m 1990-08-27  
Bowser Diane dog m 1979-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11  
Whistler Gwen bird   1997-12-09  
Slim Benny snake m 1996-04-29  

Because you are beginning with an empty table, an easy way to populate it is to create a text file containing a row for each of your animals, then load the contents of the file into the table with a single statement.

You could create a text file pet.txt containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values (such as unknown sexes or death dates for animals that are still living), you can use NULL values. To represent these in your text file, use \N (backslash, capital-N). For example, the record for Whistler the bird would look like this (where the whitespace between values is a single tab character):

Whistler        Gwen    bird    \N      1997-12-09      \N

To load the text file pet.txt into the pet table, use this command:

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

Note that if you created the file on Windows with an editor that uses \r\n as a line terminator, you should use:

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
    -> LINES TERMINATED BY '\r\n';

(On an Apple machine running OS X, you would likely want to use LINES TERMINATED BY '\r'.)

You can specify the column value separator and end of line marker explicitly in the LOAD DATA statement if you wish, but the defaults are tab and linefeed. These are sufficient for the statement to read the file pet.txt properly.

If the statement fails, it is likely that your MySQL installation does not have local file capability enabled by default. See Section 5.7.4, “Security Issues with LOAD DATA LOCAL”, for information on how to change this.

When you want to add new records one at a time, the INSERT statement is useful. In its simplest form, you supply values for each column, in the order in which the columns were listed in the CREATE TABLE statement. Suppose that Diane gets a new hamster named “Puffball.” You could add a new record using an INSERT statement like this:

mysql> INSERT INTO pet
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

Note that string and date values are specified as quoted strings here. Also, with INSERT, you can insert NULL directly to represent a missing value. You do not use \N like you do with LOAD DATA.
注意 这里的字串和日期的值 规定要用引号   '   括住

From this example, you should be able to see that there would be a lot more typing involved to load your records initially using several INSERT statements rather than a single LOAD DATA statement.

Previous / Next / Up / Table of Contents

User Comments

Posted by Doug Hall on February 17 2003 2:11pm[Delete] [Edit]

With Apple OS X: Use the terminal's drag and drop capability to insert the full path of the import file. This cuts down on the amount of typing, if you don't want deal with adding the import file into MySQL's data folder.

example:
%mysql --local-infile -u <username> -p <DatabaseName>
Enter password:<password>
mysql>load data local infile '<drag input file here>' into table <TableName>;

Posted by Brandon Stout on September 22 2004 10:56pm[Delete] [Edit]

you can also drag windows files to the command window, but you'll need to change the backslashes to double-backslashes or forwardslashes, and remove the c: at the beginning. If you have quotes around the path, you'll need to delete them as well.

Posted by tsaiching wong on December 3 2004 6:18am[Delete] [Edit]

mysql> LOAD DATA LOCAL INFILE '<dir>/pet.txt' INTO TABLE pet FIELDS terminated by '<delimiter>';

--> just in case anyone experienced some discomfort following above instructions.

Posted by phil newcombe on December 23 2004 6:40pm[Delete] [Edit]

I used the full path name 'c:/<path to file>' and it worked fine, but my defaults aren't THE defaults. :-)

Posted by Mark Buchanan on January 6 2005 1:52am[Delete] [Edit]

When dragging file in Windows I found the quotes needed to be kept in.

Posted by Mike Hearn on January 17 2005 6:31pm[Delete] [Edit]

Doug Halls trick also works on Linux/BSD using the GNOME or KDE terminal emulator programs.

Posted by Dennis Verbunt on February 27 2005 2:06pm[Delete] [Edit]

I was having some problems getting this working in XP but got it working after checking my syntax multiple times and then ENABLING local infiles.

Also after draging the file into the command window I had to replace the windows style backslashes with linux style forward slashes.
mysql> LOAD DATA LOCAL INFILE "C:\Documents and Settings\Dennis\Desktop\menagerie\pet.txt" INTO TABL
E PET;
ERROR 2 (HY000): File 'C:Documents and SettingsDennisDesktopmenageriepet.txt' not found (Errcode: 2)

mysql> LOAD DATA LOCAL INFILE "C:/Documents and Settings/Dennis/Desktop/menagerie/pet.txt" INTO TABL
E PET;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0

Posted by MidnighToker on May 7 2005 9:33pm[Delete] [Edit]

I'm thrilled and amazed about being able to drop files into a console to get the path, but

for people not using the mouse, you can put a path from your home directory using '~/' in the path.

Eg if your file is 'pets.txt' in your home directory

LOAD DATA LOCAL INFILE '~/pets.txt' INTO TABLE pet;

NOTE: This only works on *nix opperating systems and OSX (as far as i know).

NOTE: A blank line at the end of your infile gives wierd results:

| Slim     | Benny  | snake    | m    | 1996-04-29 | NULL       |
| | NULL | NULL | NULL | NULL | NULL |
| Puffball | Daine | hampster | f | 1999-03-30 | NULL |
Worth keeping an eye on.

Posted by Ron Nelson on June 13 2005 6:28pm[Delete] [Edit]

One thing it took me a moment to understand is that 's didn't seem to work. "s did.

Posted by Mohamed Abdulla on August 17 2005 1:14pm[Delete] [Edit]

I noticed that in case you want to use LOAD DATA LOCAL INFILE or the INSERT commands to fill your table fields with data, special care should be taken for INTEGER AUTO_INCREMENT fields. In the case of "LOAD DATA LOCAL INFILE" command I pressed TAB where was supposed to enter AUTO_INCREMENT INT value, and it was accepted. In the case of "INSERT INTO table VALUES (.." command I just entered the name of the column <without quotes> where was supposed to supply the value of that field, and it worked! Actually in all cases it worked also when entering any string value like: 'i' or 's'. It will alwys be converted to the required INT value. I thought this may help!

Posted by zan sh on September 28 2005 6:54pm[Delete] [Edit]

Used \n for NULL but not working..It takes 0 value for tht(date value).Even tried to write NULL but takes 0.
Insert is accepting value NULL but not load command

Posted by Aaron Peterson on November 9 2005 5:35pm[Delete] [Edit]

With a defalut installation from FreeBSD ports, I had to use the command line

mysql -u user -p --local-infile menagerie

to start the mysql monitor, else the LOAD DATA LOCAL command failed with an error like the following:

ERROR 1148 (42000): The used command is not allowed with this MySQL version

Also, the pet.txt downloaded from the mysql.com link (tgz archive) appears to have ended the lines with '\n' instead of '\r\n'.

Posted by hackajar on November 15 2005 11:41pm[Delete] [Edit]

When using "LOAD DATA INFILE" command, it's kinda tricky getting the data loaded when you don't want to provide a value for the "AUTO INCREMENT" field.

I wrote a quick script to just add in " \t" to beginning of each line in file. since the field was set "NOT NULL" and "AUTO INCREMENT" mysql spotted this and added a number for me.

You could probably do this just as well in Excel by adding a new colum before all others, and leaving it's data blank.

Posted by Gloria Rohmann on March 13 2006 12:44am[Delete] [Edit]

I tried preparing the text for load data local infile (pet.txt) on my mac (osX). Wasted a lot of time because there was no clear example of the text file format. The example should state specifically for us newbies:

Buffy,Gwen,cat,f,1998-03-02,/N,
Snuffy,Tom,dog,m,1999-0209,/N,

Additionally, the command should read:
mysql> LOAD DATA LOCAL INFILE '/path/pets.txt' INTO TABLE pet fields terminated by ',';

Posted by Andrew Cartine on March 16 2006 4:57pm[Delete] [Edit]

Using 5.0.19 on linux kernel 2.6.9-11, i found that i had to specify FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

without that, i couldn't import any records from my file. For some reason I was under the impression that commas and linefeeds were the default, but without specifying them I had no success.

Add your own comment.





real_vine@hotmail.com