using AUTO_INCREMENT with LOAD DATA INFILE

davidklonski

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

sfullman

loading data that includes a primary key value can be tricky. As long as NONE of the key values exist in the target table, you don't have a problem. But if you're loading a record with ID =454 and you've already got a record, then it won't run.

On the other hand, you may have relational tables which DEPEND on the keys of hte data you're loading. You'll almost need to run a routine first to check for this; regardless of how you do it, you'll need to think through all the issues.

If key conflicts or dependencies aren't a problem you can export the data infile without mentioning the ID field.

Yes, enclose (I do) with a single quote, and then escape all single quotes with \ inside the quotes -- note that's form mysql, cybase uses two singles to replace a single.

Sam

davidklonski

The MySQL spec says that the default values in the LOAD DATA INFILE... statement are values ecnlosed by ', and that values which contain ' are parsed so that the ' is considered a part of the value.

This means that I actually don't need to enclose anything in ', if I am using the default values.

nemonoman

You are misreading the spec and correcting someone who has given you better information:

Your statements need to be set up like this:

INSERT INTO people
SET Name='Bill\'s Best Friend Bob',
Address='Bailey\'s Corners'

note the use of quotes and escaped quotes.

If you want the ID to autoincrement, either INSERT without referencing ID at all (as above)

SET ID=NULL

You can specify that a column has a default value, like 'No Name Given' for your Name column

Then if you

INSERT iNTO people SET Address ='Somewhere'

THe finished record might be
ID...88
Name..No Name Given
Address....Somewhere

The '' for default values reference means

INSERT INTO people VALUES (,,'Somwhere')

NOT

INSERT INTO people VALUES ('','','Somewhere')

In which case Name would be set to '', not the the default value No Name Given

davidklonski

I am not talking about insert, update and so on.
I am talking about using:

LOAD DATA INFILE 'file.sql' INTO TABLE my_table;

I was talking about how file.sql will need to look.
I am always escaping single quotes when I perform regular DML statements.

nemonoman

It needs to look like a bunch of regular sql statements.