Re: Auto_Increment Stating Number - Garry Williams - com.mysql.lists.perl

3 messages in com.mysql.lists.perlRe: Auto_Increment Stating Number

From	Sent On	Attachments
PathFinder Software	20 Jul 2002 17:35
Garry Williams	20 Jul 2002 19:32
Niño Fision	22 Jul 2002 07:04

Subject:	Re: Auto_Increment Stating Number
From:	Garry Williams (gar...@zvolve.com)
Date:	07/20/2002 07:32:22 PM
List:	com.mysql.lists.perl

On Sat, Jul 20, 2002 at 05:35:32PM -0700, PathFinder Software wrote:

What is the syntax for starting an Auto_Increment at a certain value like 1500 for example?

I have tried this string but MySQL gives me an error.

CREATE TABLE Test (TestID int not null auto_increment auto_increment = 1500,Year INT,Month INT,Day INT,Name VARCHAR(50));

(An AUTO INCREMENT column *must* be a PRIMARY KEY.)

The syntax is defined in section 7.7 of the manual:

7.7 CREATE TABLE Syntax

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement]

create_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [reference_definition] or PRIMARY KEY (index_col_name,...) ...

An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

So what you are looking for doesn't exist.

You could insert a starting record in the table *after* creating it:

CREATE TABLE Test ( TestID INT AUTO_INCREMENT PRIMARY KEY, Year INT, Month INT, Day INT, Name VARCHAR(50) ); INSERT INTO Test (TestID, Year, Month, Day, Name) VALUES (1500, 1970, 1, 1, 'dummy');

Also can an auto_increment value contain characters like 1500AB?

No. It must be an integer according to Section 7.7.