SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    using AUTO_INCREMENT with LOAD DATA INFILE

    Hello

    I am using LOAD DATA INFILE to load data into a table.
    This is how the table is defined:
    CREATE TABLE people (
    ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    Name VARCHAR(255) NOT NULL,
    Address VARCHAR(255) NOT NULL,
    PRIMARY KEY (ID)
    ) ENGINE=MYISAM DEFAULT CHARSET=latin1;

    I have 2 questions:
    1) What value should be written in the file to represent a placeholder for the ID (it is an AUTO_INCREMENT attribute)?
    2) Do I need to enclose strings (Name & Address attributes) with quotes?

    thanks

  2. #2
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not add your data and then add the primary key, auto_increment afterwards. MySQL will autonumber existing data.
    Aaron Brazell
    Technosailor



  3. #3
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    if you do leave the ID column in your file empty MySQL will fill in the autoincrement values automatically. If your file contains values in the ID column those IDs will be inserted as long as they obey the rules for primary keys otherwise the row will be discarded. But you can control this behaviour by use of the keywords REPLACE and IGNORE, see

    http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

    Here you will also find an explanation for the use of delimiters. As far as I can see delimiters are not mandatory but you control this behaviour by the keyword ENCLOSED BY.
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •