about auto_increment id

about auto_increment id

am 17.10.2010 03:05:52 von short cutter

Hello,

I have a table which has the ID key with auto_increment and which is a
primary key.

If I insert the table with the id value which is generated by the
program, for example,

insert table (id, user_name, age) values (1000, 'kenn', 30);


the value 1000 is inserted forcely, not generated by database automatically.

Will this cause problem?

Thanks.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: about auto_increment id

am 17.10.2010 03:55:10 von mos

At 08:05 PM 10/16/2010, you wrote:
>Hello,
>
>I have a table which has the ID key with auto_increment and which is a
>primary key.
>
>If I insert the table with the id value which is generated by the
>program, for example,
>
>insert table (id, user_name, age) values (1000, 'kenn', 30);
>
>
>the value 1000 is inserted forcely, not generated by database automatically.
>
>Will this cause problem?
>
>Thanks.

It is not going to cause a problem but it defeats the purpose of having an
auto-inc column. After executing the SQL statement, the next Id inserted
will be 1000. It is much better to let the table determine the next
auto-inc value by using:

>insert table (id, user_name, age) values (NULL, 'kenn', 30);

If you want to get the value of the Id that was used, simply reference the
variable Last_Insert_Id as in:

select Last_Insert_Id;

See:
http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.htm l

Mike



>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=mos99@fastmail.fm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: about auto_increment id

am 17.10.2010 03:56:40 von mos

At 08:55 PM 10/16/2010, you wrote:
>After executing the SQL statement, the next Id inserted will be 1000.

Oops. I meant :

After executing the SQL statement, the next Id inserted will be 1001.

Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: about auto_increment id

am 17.10.2010 07:03:54 von short cutter

Hi,

Is it possible to change this directive's value without modifition to
my.cnf and restart mysqld?
I remember there is a set @@variable syntax, but not sure.

Thanks.

2010/10/17 mos :
> At 08:55 PM 10/16/2010, you wrote:
>>
>> After executing the SQL statement, the next Id inserted will be 1000.
>
> Oops. I meant :
>
> After executing the SQL statement, the next Id inserted will be 1001.
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: about auto_increment id

am 17.10.2010 10:36:17 von Sander de Bruijne

ALTER TABLE sometable AUTO_INCREMENT = 1000;


On 10/17/2010 07:03 AM, short cutter wrote:
> Hi,
>
> Is it possible to change this directive's value without modifition to
> my.cnf and restart mysqld?
> I remember there is a set @@variable syntax, but not sure.
>
> Thanks.
>
> 2010/10/17 mos:
>
>> At 08:55 PM 10/16/2010, you wrote:
>>
>>> After executing the SQL statement, the next Id inserted will be 1000.
>>>
>> Oops. I meant :
>>
>> After executing the SQL statement, the next Id inserted will be 1001.
>>
>>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org