Re: Re: auto_increment field start value
I just tried it in 5.0.21, and found that it fails silently with zero (0). Works with 100. I did specify int, not unsigned int, in my test table. See http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html for some discussion about how you could get a zero in there; look for NO_AUTO_VALUE_ON_ZERO Dan On 9/22/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Thanks, Dan, but I can't get it to work. Defining a column like this: a int not null auto_increment=0 primary key throws an error, and while the alter table statement seems to work ok, whether the table is empty or not, it has no effect on subsequent inserts. I'm wondering if 4.0.16 has not implemented this feature. David - there's some info in the online docs here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html Specifically: To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this: mysql ALTER TABLE tbl AUTO_INCREMENT = 100; HTH, Dan On 9/22/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I seem to recall that when creating a table, you could designate an auto_increment field to begin counting at zero(0) instead of one (1), but I can't find an example in the documents. I'm using 4.0.16 and table type=myisam. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: auto_increment every year
sql,query,queries,smallint HI all I'm searching in how to generate a auto_increment number starting form 1 every year I mean ID DATE 1 2003 2 2003 1 2004 2 2004 etc maybe possible or I've to use some c code to make it possible ? Yes, this is possible. Use a MyISAM table and set up a composite index on the two columns, with the AUTO_INCREMENT column as the second column in the index. CREATE TABLE t ( yr YEAR NOT NULL, id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(yr,id) ) TYPE = MyISAM; This will generate an independent sequence of numbers for each distinct yr value. INSERT INTO t (yr) VALUES(2003),(2003),(2004),(2005),(2005); SELECT * FROM t; +--++ | yr | id | +--++ | 2003 | 1 | | 2003 | 2 | | 2004 | 1 | | 2005 | 1 | | 2005 | 2 | +--++ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Re: AUTO_INCREMENT max value...
Bill, Wednesday, October 09, 2002, 6:55:26 PM, you wrote: BH Nope - nothing close to 4GB or even 1GB... My greatest data file size BH is 80MB. The largest table is 1.7 million rows. I have databases with BH more and bigger files. I think my problem is that the table having the BH problem with auto increment is refreshed everyday (or even multiple BH times a day) by different automated programs running on various other BH machines. These updates run at different times and remove all of the BH records from the last run before repopulating the records. In most BH cases the largest value is pretty big so when a new recordis added the BH auto increment field just gets bigger and bigger since there is probably BH never a time where there are 0 records in the table. One would think BH that the value would not top out until 2^31 since I have it defined as a BH normal INT. I guess I can make it a BIGINT, but I think I will run into BH a problem eventually regardless how big I make the field... Can you provide some more info? What is your table structure? What is the output of SHOW TABLE STATUS? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RE: Auto_increment
David, Thursday, September 05, 2002, 12:45:33 AM, you wrote: DJ Can the AUTO_INCREMENT feature be used to control a sequential number list DJ that re-uses numbers that are subsequently removed ? You can insert another rows with the same auto_increment value as in deleted row, if you directly specify value in the INSERT statement (MyISAM and InnoDB tables). With BDB table if you delete row with maximum auto_increment value, this value will be reused. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RE: Auto_increment or manual??
For replication, yes, there will be a problem, but this has anything to do with mysql. You'd have the same problem with a Sybase identity field replicated to another database server. A possible solution is this: 1. have a separate table for generating counter fields. 2. make the replicated table's primary key char 3. Identify each replicated server with a character. For example, we have a server in NY that's identified with an 'N' and one in Miami, that's an 'M'. 4. When inserting a record get the next counter value from the counter table, concatenate it with the server identifier and voila, a key that's unique across replicated servers. NY server ids will look like N1000, N1001, N1002 and Miami M1000, M1001, M1002 and so on. The downside: you have a char field to index and that ain't as fast as an index on a numeric field. Christopher Lambrou, CGL Computer Services, Inc. Empire State Building, PMB 16J Suite 3304 New York, NY 10118 Tel: (212) 971-9723 Fax: (212) 564-1135 URL: http://www.cglcomputer.com Email: [EMAIL PROTECTED] On 5/3/2001 18:03:17, you said: This sounds encouraging, but are there any known problems with the MySQL replication model currently available?? Would the slave servers have the same auto_inc column attributes? Could this conflict somehow with the master server?? Thanks for the quick reply! Patrick # Personnaly, i have found autoincrement fields to be rock solid. # I use'em all over the place on a 1 Gb database, web based. # Don't do it manually . You'll end up doing the same thing # that mysql gives you for free. # # Christopher Lambrou, # CGL Computer Services, Inc. # Empire State Building, # PMB 16J Suite 3304 # New York, NY 10118 # Tel: (212) 971-9723 # Fax: (212) 564-1135 # URL: http://www.cglcomputer.com # Email: [EMAIL PROTECTED] # # On 5/3/2001 15:57:38, you said: # Hello! # I have a database with about 10 tables in it. In every table I have a # RECORD_ID # field so that I can at least uniquely identify a row if I # need to, also its # used in relationships. The question is should I use the # AUTO_INCREMENT for # this, or should I manually generate this value, getting the # next highest # number, then putting it in there. Is there any known # replication problems if # I use AUTO_INCREMENT??? Would I be safer in just doing this # manually myself # in my code?? This is going to be a web-based app, so many # users will be # using the db at the same time. # Thanks! # # Patrick # # - # Before posting, please check: #http://www.mysql.com/manual.php (the manual) #http://lists.mysql.com/ (the list archive) # # To request this thread, e-mail [EMAIL PROTECTED] # To unsubscribe, e-mail # [EMAIL PROTECTED] # Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
