changing an auto_increment value in innodb
Dear all, I have an auto_increment value in an innodb table. I recently introduced a new row by mistake and removed it. I beleive I read somewhere in the manual that an innodb table does not reuse this number by default. I also remember vaguely that it is possible to reset this next autoincrement value (I want the next insert to reuse this number). I cannot find it in the manual now I need it. I'm only on the digest list so please (b)cc to me directly. Gerben Gieling Patent Information Specialist Synthon BV P.O. Box 7071 6503 GN Nijmegen The Netherlands phone:+31 24 3727700 fax: +31 24 3727789 E-mail: mailto:[EMAIL PROTECTED] Internet: www.synthon.nl --- DISCLAIMER: This message (including any attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. Synthon shall not be liable for the improper or incomplete transmission of the information contained in this communication nor for any delay in its receipt or damage to your system. Synthon does not guarantee that the integrity of this communication has been maintained nor that this communication is free of viruses, interceptions or interference. Any views expressed in this message are those of the individual sender and may not necessarily reflect the views of the company. --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WoW!!!! SQL is something!
Sorry,
should be the lpad function:
http://www.mysql.com/doc/en/String_functions.html
LPAD(str,len,padstr)
Returns the string str, left-padded with the string padstr until str is
len characters long. If str is longer than len' then it will be
shortened to len characters.
mysql SELECT LPAD('hi',4,'??');
- '??hi'
-Original Message-
From: Norris, Joseph [mailto:[EMAIL PROTECTED]]
Sent: December 04, 2002 18:26
To: Mysql_List (E-mail)
Subject: WoW SQL is something!
I had a problem that required a proper sort of the following:
IT66
ITD38
IT68
ITD39
ITD40
IT69
ITD55
ITD56
IT72
ITD57
ITD58
ITD59
ITD60
IT73
ITD73
ITD67
IT74
ITD69
ITD70
IT78
IT79
IT83
ITD71
IT81
ITD75
ITD76
IT84
ITD96
ITD97
ITD98
ITD99
ITD100
ITD101
ITD102
ITD103
ITD104
ITD105
Because of the different lengths, this was a nightmare for
order by and so
it led me to my first experiments with case
and I go the following:
select req_number, case
when (length(req_number)=4) then concat('00', req_number)
when (length(req_number)=5) then concat('0', req_number)
when (length(req_number)=6) then req_number
END as sort_req
from req order by sort_req;
Now there might be other ways to handle this and I would love
to hear about
them but I thought that this taught me
something about sql that I had not understood until now. Just
would like to
give a little back.
Thanks to all.
---
DISCLAIMER:
This message, including attachments, is confidential and may be
privileged. If you are not an intended recipient, please notify the
sender then delete and destroy the original message and all copies. You
should not copy, forward and/or disclose this message, in whole or in
part, without permission of the sender.
---
-
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: WoW!!!! SQL is something!
What you want is the rpad function:
http://www.mysql.com/doc/en/String_functions.html
RPAD(str,len,padstr)
Returns the string str, right-padded with the string padstr until str is
len characters long. If str is longer than len' then it will be
shortened to len characters.
mysql SELECT RPAD('hi',5,'?');
- 'hi???'
-Original Message-
From: Norris, Joseph [mailto:[EMAIL PROTECTED]]
Sent: December 04, 2002 18:26
To: Mysql_List (E-mail)
Subject: WoW SQL is something!
I had a problem that required a proper sort of the following:
IT66
ITD38
IT68
ITD39
ITD40
IT69
ITD55
ITD56
IT72
ITD57
ITD58
ITD59
ITD60
IT73
ITD73
ITD67
IT74
ITD69
ITD70
IT78
IT79
IT83
ITD71
IT81
ITD75
ITD76
IT84
ITD96
ITD97
ITD98
ITD99
ITD100
ITD101
ITD102
ITD103
ITD104
ITD105
Because of the different lengths, this was a nightmare for
order by and so
it led me to my first experiments with case
and I go the following:
select req_number, case
when (length(req_number)=4) then concat('00', req_number)
when (length(req_number)=5) then concat('0', req_number)
when (length(req_number)=6) then req_number
END as sort_req
from req order by sort_req;
Now there might be other ways to handle this and I would love
to hear about
them but I thought that this taught me
something about sql that I had not understood until now. Just
would like to
give a little back.
Thanks to all.
---
DISCLAIMER:
This message, including attachments, is confidential and may be
privileged. If you are not an intended recipient, please notify the
sender then delete and destroy the original message and all copies. You
should not copy, forward and/or disclose this message, in whole or in
part, without permission of the sender.
---
-
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: MySQL as a desktop DB
The combination of Access front end with MySQL or PostgreSQL works great. However there is a disadvantage in using Microsoft Office applications. If you use Visual Basic code to program the behaviour of your front end (You probably will). The front end will run great until the next major update of MSOffice. It will again make a lot of changes without backward compatibility. This needn't be a problem If you are in control of the migration. But, if your IT department decides to migrate to a new version of MS Office catching you unprepared, your database will be without functional or with a misbehaving front end until you fix the code. I quit using Access and migrated my front end to Apache/PHP running on the same (linux) server as the database (an old pc found in the trashbin because it wasn't fast enough to run windows 95). -Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Sent: September 25, 2002 17:34 To: David Lloyd Cc: Adam Parker; [EMAIL PROTECTED] Subject: Re: MySQL as a desktop DB David Lloyd wrote: MySQL in a single user environment is a pain in the arse because of its lack of an Access Like front end that is actually useful and featureful (Access is crap, but it's a better database frontend than currently exists). I've never done it, but have you tried Access linked to MySQL through MyODBC? PS, SQL ... Query ... -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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: aggregate functions producing bad data
Perhaps you should create a new table with an id column and a flag column. Select the found calls 5 or = 5 into that new table and set the flag. Then do a left join of your t_callog_calls table on the new table selecting only the rows where the flag is NULL, these should be the rows not found by either query. Inspect the data in these rows manually to determine what is wrong. -Original Message- From: Richard Unger [mailto:[EMAIL PROTECTED]] Sent: September 17, 2002 21:00 To: [EMAIL PROTECTED] Subject: aggregate functions producing bad data My query: SELECT count(id), count( IF(call_end - call_start 5, id, NULL) ), count( IF(call_end - call_start = 5, id, NULL) ) FROM t_calllog_calls; My result: 1994 1956 35 However, 1956 + 35 != 1994. Running MySQL 3.23.49-nt Cheers, Rich - 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
