List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 13 2002 9:48pm
Subject:Re: AUTO_INCREMENT= ?
View as plain text  
At 17:41 -0400 5/13/02, Amer Neely wrote:
>Paul DuBois wrote:
>>
>>  At 15:23 -0400 5/13/02, Amer Neely wrote:
>>  >  > Amer,
>>  >>  Monday, May 13, 2002, 2:03:28 AM, you wrote:
>>  >>
>>  >>  AN> Win/98
>>  >>  AN> MySQL 3.23.46
>>  >>
>>  >>  AN> I'm trying to use AUTO_INCREMENT=1000 to specify my 
>>staring value in an
>>  >>  AN> ID column in batch mode, but it doesn't want to work. I 
>>can get it to
>>  >>  AN> work in interactive mode though.
>>  >>
>>  >>  AN> In a file (create_tables.sql) I have:
>>  >>
>>  >>  AN> CREATE TABLE Respondents (UserID SMALLINT UNSIGNED 
>>AUTO_INCREMENT NOT
>>  >>  AN> NULL PRIMARY KEY,
>>  >>  AN>                           UserLastName VARCHAR(25) NOT NULL,
>>  >>  AN>                           UserFirstName VARCHAR(20) NOT NULL,
>>  >>  AN>                           UserEmail VARCHAR(60) NOT NULL,
>>  >>  AN>                           UserPhoneAC CHAR(3) NOT NULL,
>>  >>  AN>                           UserPhoneNum VARCHAR(8) NOT NULL,
>>  >>  AN>                           UserPhoneExt VARCHAR(5),
>>  >>  AN>                           UserLevel VARCHAR(20) NOT NULL,
>>  >>  AN>                           UserFoundBy VARCHAR(40) NOT NULL,
>>  >>  AN>                           KeyDM ENUM('Y','N') NOT NULL,
>>  >>  AN>                           DMPositionTitle VARCHAR(30) NOT
> NULL,
>>  >>  AN>                           IPAddr VARCHAR(15) NOT NULL,
>>  >>  AN>                           IPName VARCHAR(100))
>>  >>  AN>                           AUTO_INCREMENT=1000;
>>  >>
>>  >>  AN> which I am then loading into mysql like this:
>>  >>
>>  >>  AN> c:\mysql\data\mydb mysql mydb < create_tables.sql
>>  >>
>>  >>  AN> No errors, but when I populate the table with values (also 
>>from a file),
>>  >>  AN> the values for UserID start at 1, not 1000.
>>  >>
>>  >>  I guess that your UserID in the file begins from 1, not from 1000.
> You
>>  >>  can get values that are starting from 1000, if you insert into column
>>  >>  NULL or 0 or if you insert values from 1000 manually :)
>>  >
>>  >Yes, I understand that, but I'm trying to create the table and
>>  >initialize UserID by redirecting a .sql file into mysql (batch mode). I
>>  >don't want to insert a 'dummy' record starting at 1000, I want MySQL to
>>  >do that, like it says in "MySQL" by Paul DuBois [p.94, chap.2.]. Again,
>>  >this works in interactive mode, but NOT in batch mode. Can you or
>>  >someone explain how this can be done in batch mode?
>>  >
>>  >>  AN> The values I'm inserting for
>>  >>  AN> UserID are all 'null' (without quotes).
>>  >>
>>  >>  How did you inserted values? Manually or from a file?
>>  >
>>  >As I indicated above, from a file.
>>
>>  Let's see a sample of the file.  Without that, we're just guessing.
>>  mysql won't interpret INSERT statements differently in batch mode
>>  than in interactive mode.
>
>Hmm. I'm not using INSERT, but LOAD DATA. Maybe that's the culprit?

Maybe.  In data files loaded with LOAD DATA, NULL values should be specified
as \N, not as the word NULL.

I'm not sure that that will make a difference, though. MySQL will see NULL,
perform a string-to-number conversion and end up with a value of zero.
And inserting 0 into an AUTO_INCREMENT column should be the same as inserting
NULL.  Still, it'd be worth a try to convert NULL to \N and see what happens.

This might be interacting with another problem, which is that if your
data file likes are CRLF terminated, *you need to say so*.  The default
is LF-terminated, so your LOAD DATA statement needs to have a

LINES TERMINATED BY '\r\n'

clause at the end.

>
>Contents of 'create_tables.sql':
>CREATE TABLE Respondents (UserID SMALLINT UNSIGNED NOT NULL
>AUTO_INCREMENT PRIMARY KEY,
>                           UserLastName VARCHAR(25) NOT NULL,
>                           UserFirstName VARCHAR(20) NOT NULL,
>                           UserEmail VARCHAR(60) NOT NULL,
>                           UserPhoneAC CHAR(3) NOT NULL,
>                           UserPhoneNum VARCHAR(8) NOT NULL,
>                           UserPhoneExt VARCHAR(5),
>                           UserLevel VARCHAR(20) NOT NULL,
>                           UserFoundBy VARCHAR(40) NOT NULL,
>                           KeyDM ENUM('Y','N') NOT NULL,
>                           DMPositionTitle VARCHAR(30) NOT NULL,
>                           IPAddr VARCHAR(15) NOT NULL,
>                           IPName VARCHAR(100)
>                           )
>                           AUTO_INCREMENT=1000;
>---------------------------------------------------------
>Contents of 'populate.sql':
>DELETE FROM Respondents;
>LOAD DATA LOCAL INFILE "respondents.txt" INTO TABLE Respondents;
>--------------------------------------------------------------
>Contents of respondents.txt:
>NULL	Neely	Amer	aneely@stripped	519	438.5887 
>		Owner	Business card
>Y	Owner	127.0.0.1	localhost
>NULL	Silver	John	jsilver@stripped	000	000-0000 
>		Owner	Referral	Y	CEO
>209.84.23.19	somewhere.ca
>NULL	Day	Dennis	dday@stripped	123	987-6543 
>		President	Search
>engine	N		200.100.50.25	somewhereelse.com
>NULL	Marlatt	Ed	ed@stripped	789	123-0789 
>	Mid-Level	Search engine	N
>209.187.29.12	here.com
>NULL	Curiale	L.	lcuriale@stripped	987	456-3210 
>		CEO	Promotional
>Material	N		189.2.100.1	there.org
>NULL	Butler	Robert S.	rsbutler@stripped	456 
>	987-0123		Board
>Referral	Y	President	209.87.34.1	everywhere.ca
>NULL	Hobbes	Calvin	calvin@stripped	654	951-7562 
>		CHO	Referral	Y
>Manager	209.43.89.100	hobbes.com
>---------------------------------------------------
>NB: respondents.txt file is tab-delimited, ending with CRLF
>
>I load them thus:
>c:\mysql\data\mydb mysql mydb < create_tables.sql
>c:\mysql\data\mydb mysql mydb < populate.sql
>
>When I do a 'select userid from respondents' I get 1..7, not 1000..1006
>
>Eventually I need to have this generated by a Perl script.

Thread
AUTO_INCREMENT= ?Amer Neely13 May
  • Re: AUTO_INCREMENT= ?Victoria Reznichenko13 May
Re: AUTO_INCREMENT= ?Amer Neely13 May
  • Re: AUTO_INCREMENT= ?Luciano Barcaro13 May
  • Re: AUTO_INCREMENT= ?Paul DuBois13 May
  • Re: AUTO_INCREMENT= ?Curtis Maurand13 May
  • Re: AUTO_INCREMENT= ?Amer Neely13 May
    • Re: AUTO_INCREMENT= ?Paul DuBois13 May
  • Re: AUTO_INCREMENT= ?Amer Neely13 May
  • Re: AUTO_INCREMENT= ?Amer Neely13 May
    • Re: AUTO_INCREMENT= ?Paul DuBois13 May
  • Re: AUTO_INCREMENT= ?Amer Neely13 May
    • Re: AUTO_INCREMENT= ?Paul DuBois13 May