Why can't I delete these records?
Hi all! Who can tell me why the following does not work...? When I do: SELECT COUNT(*) AS counter FROM table1 WHERE condition1 = 'A' AND condition2 IS NULL; it returns: +-+ | counter | +-+ | 2 | +-+ Then I do: DELETE FROM table1 WHERE condition1 = 'A' AND condition2 IS NULL; it returns: Query OK, 0 rows affected (0.00 sec) But... when I give a SELECT COUNT(*) AS counter FROM table1 WHERE condition1 = 'A' AND condition2 IS NULL; How come the 2 records did not get deleted...? Regards, Evert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I delete these records?
Hi, Query OK, 0 rows affected (0.00 sec) It did not find/delete any rows - please double-check your conditions and make sure you are using the same set as for the SELECT statement. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I delete these records?
Hi! I'm entering the statements exactly as listed in my message (only done a search/replace on table-name). So SELECT says there are 2 records, but then DELETE does not see those same 2 records... :-/ Is there something like a verbose/debug mode in which I can see more precise why my DELETE doesn't work? Regards, Evert Aleksandar Bradaric wrote: Hi, Query OK, 0 rows affected (0.00 sec) It did not find/delete any rows - please double-check your conditions and make sure you are using the same set as for the SELECT statement. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why does MySQL accept fake date?
Dear MySQL-ers, Using MySQL 4.1.20, in the function DAYOFWEEK(), why does MySQL accept a bogus date like '2006-02-30'? It says the 30th of February (yeah, right) starts on a the 5th day. I was going to use this to create a table of how many days there are in each month, but that's completely unusable now. Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does MySQL accept fake date?
Hello Mark, in Versions of MySQL prior to 5.0.2 it is only checked that the year-part ranges from 1000-, the month-part from 1-12 and the day-part ranges from 1-31 within the date column. With 5.0.2 of MySQL the Dates must be legal, so 2006-02-31 is no more possible by default. You can however turn on that behavior again by using |the option ALLOW_INVALID_DATES when starting the MySQL Server. So, either you take care that valid dates are entered in your Column by checking before inserting data with your favorite scripting/programming language or upgrade to MySQL 5.0.2 which however will prevent you from inserting invalid dates in the first place. Greets Rocco | Mark wrote: Dear MySQL-ers, Using MySQL 4.1.20, in the function DAYOFWEEK(), why does MySQL accept a bogus date like '2006-02-30'? It says the 30th of February (yeah, right) starts on a the 5th day. I was going to use this to create a table of how many days there are in each month, but that's completely unusable now. Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anyone tried solidDB for MySQL?
I've installed the beta and imported data. It took longer to import than InnoDB does so I think at least inserts are slower. I haven't benchmarked reads or anything yet. I like the idea of having MVCC instead of locks. We would benefit more from better query planning so I'm unsure whether we will bother changing from InnoDB. On 25/08/06, Cory Robin [EMAIL PROTECTED] wrote: I've heard absolutely wonderful things about this transaction-safe storage engine. We're using InnoDB now and are always looking for the best solution as we scale our DB operations. Has anyone tried the SolidDB for MySQL beta stuff yet? What do you think of it? Cory.
Re: Why can't I delete these records?
Hello Evert, i just entered your example and had no unusal behaviour on MySQL 5.0: mysql describe table1; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | condition1 | char(1)| YES | | NULL| | | condition2 | tinyint(4) | YES | | NULL| | +++--+-+-+---+ 2 rows in set (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | A | NULL | | A | NULL | | A | NULL | | B | NULL | | C | NULL | +++ 5 rows in set (0.00 sec) mysql SELECT count(*) as counter from table1 WHERE condition1='A' and condition2 IS NULL; +-+ | counter | +-+ | 3 | +-+ 1 row in set (0.00 sec) mysql DELETE FROM table1 WHERE condition1='A' AND condition2 IS NULL; Query OK, 3 rows affected (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | B | NULL | | C | NULL | +++ 2 rows in set (0.00 sec) Maybe check again the Table definition. There is nothing wrong with with Queries you have posted in your message. Greets Rocco Evert wrote: Hi! I'm entering the statements exactly as listed in my message (only done a search/replace on table-name). So SELECT says there are 2 records, but then DELETE does not see those same 2 records... :-/ Is there something like a verbose/debug mode in which I can see more precise why my DELETE doesn't work? Regards, Evert Aleksandar Bradaric wrote: Hi, Query OK, 0 rows affected (0.00 sec) It did not find/delete any rows - please double-check your conditions and make sure you are using the same set as for the SELECT statement. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why does MySQL accept fake date?
Hello Rocco, Thank you for your reply. Clear and simple. :) I couldn't upgrade the MySQL server on my production server just yet, but I upgraded the local test MySQL server on a Windoze machine. And it worked like a charm. :) I just needed to create a one-time table of how many days there are in each month for the next ten years (that's what the 'invalid date' test was supposed to do), and at what day of the week they start. In MySQL 5.0.2 this really worked very well. Thank you! :) Hello Mark, in Versions of MySQL prior to 5.0.2 it is only checked that the year-- part ranges from 1000-, the month-part from 1-12 and the day-part ranges from 1-31 within the date column. With 5.0.2 of MySQL the Dates must be legal, so 2006-02-31 is no more possible by default. You can however turn on that behavior again by us- ing |the option ALLOW_INVALID_DATES when starting the MySQL Server. So, either you take care that valid dates are entered in your Column by checking before inserting data with your favorite scripting/programming language or upgrade to MySQL 5.0.2 which however will prevent you from inserting invalid dates in the first place. Greets Rocco Mark wrote: Dear MySQL-ers, Using MySQL 4.1.20, in the function DAYOFWEEK(), why does MySQL accept a bogus date like '2006-02-30'? It says the 30th of February (yeah, right) starts on a the 5th day. I was going to use this to create a table of how many days there are in each month, but that's completely unusable now. Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I delete these records?
I'm using MySQL 4.1.21 (on Gentoo). Should this make any difference? Regards, Evert Rocco wrote: Hello Evert, i just entered your example and had no unusal behaviour on MySQL 5.0: mysql describe table1; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | condition1 | char(1)| YES | | NULL| | | condition2 | tinyint(4) | YES | | NULL| | +++--+-+-+---+ 2 rows in set (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | A | NULL | | A | NULL | | A | NULL | | B | NULL | | C | NULL | +++ 5 rows in set (0.00 sec) mysql SELECT count(*) as counter from table1 WHERE condition1='A' and condition2 IS NULL; +-+ | counter | +-+ | 3 | +-+ 1 row in set (0.00 sec) mysql DELETE FROM table1 WHERE condition1='A' AND condition2 IS NULL; Query OK, 3 rows affected (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | B | NULL | | C | NULL | +++ 2 rows in set (0.00 sec) Maybe check again the Table definition. There is nothing wrong with with Queries you have posted in your message. Greets Rocco Evert wrote: Hi! I'm entering the statements exactly as listed in my message (only done a search/replace on table-name). So SELECT says there are 2 records, but then DELETE does not see those same 2 records... :-/ Is there something like a verbose/debug mode in which I can see more precise why my DELETE doesn't work? Regards, Evert Aleksandar Bradaric wrote: Hi, Query OK, 0 rows affected (0.00 sec) It did not find/delete any rows - please double-check your conditions and make sure you are using the same set as for the SELECT statement. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Batch Update of records
Hi I've am updating a database with about 20,000 records. What is the best way to perform these updates. Can I use batch updating ? Thanks Neil _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d
Re: Why can't I delete these records?
Hello Evert, i actually do not know and don't have access to a 4.1 Machine. What you could try is to use the alternative way of testing if a value is NULL: Maybe the behavior of IS NULL has been changed in 5.0 . DELETE FROM table1 WHERE condition1='A' AND condition2=NULL Greets Rocco Evert wrote: I'm using MySQL 4.1.21 (on Gentoo). Should this make any difference? Regards, Evert Rocco wrote: Hello Evert, i just entered your example and had no unusal behaviour on MySQL 5.0: mysql describe table1; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | condition1 | char(1)| YES | | NULL| | | condition2 | tinyint(4) | YES | | NULL| | +++--+-+-+---+ 2 rows in set (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | A | NULL | | A | NULL | | A | NULL | | B | NULL | | C | NULL | +++ 5 rows in set (0.00 sec) mysql SELECT count(*) as counter from table1 WHERE condition1='A' and condition2 IS NULL; +-+ | counter | +-+ | 3 | +-+ 1 row in set (0.00 sec) mysql DELETE FROM table1 WHERE condition1='A' AND condition2 IS NULL; Query OK, 3 rows affected (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | B | NULL | | C | NULL | +++ 2 rows in set (0.00 sec) Maybe check again the Table definition. There is nothing wrong with with Queries you have posted in your message. Greets Rocco Evert wrote: Hi! I'm entering the statements exactly as listed in my message (only done a search/replace on table-name). So SELECT says there are 2 records, but then DELETE does not see those same 2 records... :-/ Is there something like a verbose/debug mode in which I can see more precise why my DELETE doesn't work? Regards, Evert Aleksandar Bradaric wrote: Hi, Query OK, 0 rows affected (0.00 sec) It did not find/delete any rows - please double-check your conditions and make sure you are using the same set as for the SELECT statement. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Batch Update of records
Hallo Neil, take a look at the utility mysqlimport which will exactly fit your needs importing those records into a database table. The basic syntax is: mysqlimport options database_name table_name.txt Tablename.txt would be your datafile with the records. The filename must match the name of the table in the database you like to import the records to. Depending on the delimiters you use to separate fields in your text file, you have to set appropriate options. The documentation on what options you have to use for type of data file can be obtained on mysql.com. Greets Rocco Neil Tompkins wrote: Hi I've am updating a database with about 20,000 records. What is the best way to perform these updates. Can I use batch updating ? Thanks Neil _ Be one of the first to try Windows Live Mail. http://ideas.live.com/programpage.aspx?versionId=5d21c51a-b161-4314-9b0e-4911fb2b2e6d -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I delete these records?
Hi! I tried your tip, but = gives the same result as = when I execute the query... :-/ Regards, Evert Rocco wrote: Hello Evert, i actually do not know and don't have access to a 4.1 Machine. What you could try is to use the alternative way of testing if a value is NULL: Maybe the behavior of IS NULL has been changed in 5.0 . DELETE FROM table1 WHERE condition1='A' AND condition2=NULL Greets Rocco Evert wrote: I'm using MySQL 4.1.21 (on Gentoo). Should this make any difference? Regards, Evert Rocco wrote: Hello Evert, i just entered your example and had no unusal behaviour on MySQL 5.0: mysql describe table1; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | condition1 | char(1)| YES | | NULL| | | condition2 | tinyint(4) | YES | | NULL| | +++--+-+-+---+ 2 rows in set (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | A | NULL | | A | NULL | | A | NULL | | B | NULL | | C | NULL | +++ 5 rows in set (0.00 sec) mysql SELECT count(*) as counter from table1 WHERE condition1='A' and condition2 IS NULL; +-+ | counter | +-+ | 3 | +-+ 1 row in set (0.00 sec) mysql DELETE FROM table1 WHERE condition1='A' AND condition2 IS NULL; Query OK, 3 rows affected (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | B | NULL | | C | NULL | +++ 2 rows in set (0.00 sec) Maybe check again the Table definition. There is nothing wrong with with Queries you have posted in your message. Greets Rocco Evert wrote: Hi! I'm entering the statements exactly as listed in my message (only done a search/replace on table-name). So SELECT says there are 2 records, but then DELETE does not see those same 2 records... :-/ Is there something like a verbose/debug mode in which I can see more precise why my DELETE doesn't work? Regards, Evert Aleksandar Bradaric wrote: Hi, Query OK, 0 rows affected (0.00 sec) It did not find/delete any rows - please double-check your conditions and make sure you are using the same set as for the SELECT statement. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I delete these records?
Hello Evert, the = Operator can not work with NULL values , so you have to use = or IS NULL. I have no clue why its not working at your side, but i assure you that it must be a rather simply problem (typo, table definition). Are you sure your definition2-column allows NULL values in the first place? Greets Rocco Evert wrote: Hi! I tried your tip, but = gives the same result as = when I execute the query... :-/ Regards, Evert Rocco wrote: Hello Evert, i actually do not know and don't have access to a 4.1 Machine. What you could try is to use the alternative way of testing if a value is NULL: Maybe the behavior of IS NULL has been changed in 5.0 . DELETE FROM table1 WHERE condition1='A' AND condition2=NULL Greets Rocco Evert wrote: I'm using MySQL 4.1.21 (on Gentoo). Should this make any difference? Regards, Evert Rocco wrote: Hello Evert, i just entered your example and had no unusal behaviour on MySQL 5.0: mysql describe table1; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | condition1 | char(1)| YES | | NULL| | | condition2 | tinyint(4) | YES | | NULL| | +++--+-+-+---+ 2 rows in set (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | A | NULL | | A | NULL | | A | NULL | | B | NULL | | C | NULL | +++ 5 rows in set (0.00 sec) mysql SELECT count(*) as counter from table1 WHERE condition1='A' and condition2 IS NULL; +-+ | counter | +-+ | 3 | +-+ 1 row in set (0.00 sec) mysql DELETE FROM table1 WHERE condition1='A' AND condition2 IS NULL; Query OK, 3 rows affected (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | B | NULL | | C | NULL | +++ 2 rows in set (0.00 sec) Maybe check again the Table definition. There is nothing wrong with with Queries you have posted in your message. Greets Rocco Evert wrote: Hi! I'm entering the statements exactly as listed in my message (only done a search/replace on table-name). So SELECT says there are 2 records, but then DELETE does not see those same 2 records... :-/ Is there something like a verbose/debug mode in which I can see more precise why my DELETE doesn't work? Regards, Evert Aleksandar Bradaric wrote: Hi, Query OK, 0 rows affected (0.00 sec) It did not find/delete any rows - please double-check your conditions and make sure you are using the same set as for the SELECT statement. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I delete these records?
Oops, that was just a typo in my previous reply. My script does use 'IS NULL' and not '= NULL'. I also tried '= NULL'. No luck... Evert Rocco wrote: Hello Evert, the = Operator can not work with NULL values , so you have to use = or IS NULL. I have no clue why its not working at your side, but i assure you that it must be a rather simply problem (typo, table definition). Are you sure your definition2-column allows NULL values in the first place? Greets Rocco Evert wrote: Hi! I tried your tip, but = gives the same result as = when I execute the query... :-/ Regards, Evert Rocco wrote: Hello Evert, i actually do not know and don't have access to a 4.1 Machine. What you could try is to use the alternative way of testing if a value is NULL: Maybe the behavior of IS NULL has been changed in 5.0 . DELETE FROM table1 WHERE condition1='A' AND condition2=NULL Greets Rocco Evert wrote: I'm using MySQL 4.1.21 (on Gentoo). Should this make any difference? Regards, Evert Rocco wrote: Hello Evert, i just entered your example and had no unusal behaviour on MySQL 5.0: mysql describe table1; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | condition1 | char(1)| YES | | NULL| | | condition2 | tinyint(4) | YES | | NULL| | +++--+-+-+---+ 2 rows in set (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | A | NULL | | A | NULL | | A | NULL | | B | NULL | | C | NULL | +++ 5 rows in set (0.00 sec) mysql SELECT count(*) as counter from table1 WHERE condition1='A' and condition2 IS NULL; +-+ | counter | +-+ | 3 | +-+ 1 row in set (0.00 sec) mysql DELETE FROM table1 WHERE condition1='A' AND condition2 IS NULL; Query OK, 3 rows affected (0.00 sec) mysql SELECT * FROM table1; +++ | condition1 | condition2 | +++ | B | NULL | | C | NULL | +++ 2 rows in set (0.00 sec) Maybe check again the Table definition. There is nothing wrong with with Queries you have posted in your message. Greets Rocco Evert wrote: Hi! I'm entering the statements exactly as listed in my message (only done a search/replace on table-name). So SELECT says there are 2 records, but then DELETE does not see those same 2 records... :-/ Is there something like a verbose/debug mode in which I can see more precise why my DELETE doesn't work? Regards, Evert Aleksandar Bradaric wrote: Hi, Query OK, 0 rows affected (0.00 sec) It did not find/delete any rows - please double-check your conditions and make sure you are using the same set as for the SELECT statement. Best regards, Aleksandar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
displaying a sing thumbnail
I have a database of images, http://www.thethistlehouse.com/db.jpg What I want to do is select ONLY ONE image to display as a the image link for that gallery. As you can see galleries are numbered dynamcially but galleries can also be added and deleted so the galleries no's I have now (7, 8) will change. I have the code to display the thubnail but am stuck with the query. I want to use mysql and php to (i) determine how many unique galleries there are. (ii) Retrieve display a single thumbnail from each gallery to act as the link to that gallery Ross
Re: displaying a sing thumbnail
[EMAIL PROTECTED] wrote: I have a database of images, http://www.thethistlehouse.com/db.jpg What I want to do is select ONLY ONE image to display as a the image link for that gallery. As you can see galleries are numbered dynamcially but galleries can also be added and deleted so the galleries no's I have now (7, 8) will change. I have the code to display the thubnail but am stuck with the query. I want to use mysql and php to (i) determine how many unique galleries there are. To list the galleries: SELECT DISTINCT gallery FROM yourpicturetable; To count them: SELECT COUNT(DISTINCT(gallery)) FROM yourpicturetable; (ii) Retrieve display a single thumbnail from each gallery to act as the link to that gallery That should be easy, but first you must tell us how you determine which picture in each gallery is the desired thumbnail. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
feature request, optimize myisam with concurrent read only
Would like to see this, there is a TMD temp table created during an optimize is there any reason read only access cant take place during the optimize process? Update/delete/insert shouldn't, but read access should be allowed on myisam any thoughts on this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IP Address Function?
Hi, I need to make *allot* of queries from a application dealing with IP Addresses. At the moment, I'm storing IP addresses as a VARCHAR(16). I would *like* to store them as Integers by converting the IP to it's numerical equivalent. I believe this would also save a enormous amount of table space. The problem is, I *must* do the conversion as part of my SQL Query. Either during the SELECT/UPDATE/INSERT, or via a Procedure... And I must obviously also be able to convert the Integer back into a IP address during queries Is this at all possible??? It's all explained very nicely at http://www.aboutmyip.com/AboutMyXApp/IP2Integer.jsp But how to do this as part of a query... *frown* Any help appreciated... Regards, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IP Address Function?
Chris Knipe wrote:
Hi,
I need to make *allot* of queries from a application dealing with IP
Addresses. At the moment, I'm storing IP addresses as a VARCHAR(16).
I would *like* to store them as Integers by converting the IP to it's
numerical equivalent. I believe this would also save a enormous
amount of table space.
The problem is, I *must* do the conversion as part of my SQL Query.
Either during the SELECT/UPDATE/INSERT, or via a Procedure... And I
must obviously also be able to convert the Integer back into a IP
address during queries
Is this at all possible???
mytable
MySQL has dotted IP string to integer (and vice versa) conversion
functions. You'd use them in queries like:
insert into mytable set IPaddress = funcName('192.168.191.34');
select funcName(IPaddress) as strIPaddress, IPaddress as intIPaddress
from mytable;
I suspect you posted to the list before attempting to the functions in
the online documentation so I'll simply confirm they exist and leave
you to: Do Your Own Research by Reading The Fine Manual.
Nigel Wood
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IP Address Function?
RTFM! Let that be a good lesson for me now :) INET_ATON() and INET_NTOA() Brilliant!!! Regards, Chris. - Original Message - From: Chris Knipe [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, August 26, 2006 9:03 PM Subject: IP Address Function? Hi, I need to make *allot* of queries from a application dealing with IP Addresses. At the moment, I'm storing IP addresses as a VARCHAR(16). I would *like* to store them as Integers by converting the IP to it's numerical equivalent. I believe this would also save a enormous amount of table space. The problem is, I *must* do the conversion as part of my SQL Query. Either during the SELECT/UPDATE/INSERT, or via a Procedure... And I must obviously also be able to convert the Integer back into a IP address during queries Is this at all possible??? It's all explained very nicely at http://www.aboutmyip.com/AboutMyXApp/IP2Integer.jsp But how to do this as part of a query... *frown* Any help appreciated... Regards, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How a VIEW is stored
I was working with my version 5 and made a couple of views which are very useful. Looking in the db I was able to see the VIEW's saved as TABLE :-) This was a surprise and not sure if this is the expected result or not. Then I did some SELECT that involved the VIEW and it does work a lot like another TABLE, but it can and mine does take data from many TABLE's into a VIEW. You can write a SELECT or even another VIEW using a VIEW. But if you keep track of the time used by a query, it starts to get too long if you use a SELECT of a VIEW that has within it another VIEW. Karl Larsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How a VIEW is stored
In the last episode (Aug 26), Karl Larsen said: I was working with my version 5 and made a couple of views which are very useful. Looking in the db I was able to see the VIEW's saved as TABLE :-) What command did you run to determine this? SHOW TABLES does list them but that's to be expected since views act like tables. SHOW TABLE STATUS and selecting from information_schema.tables both clearly distinguish tables from views. This was a surprise and not sure if this is the expected result or not. Then I did some SELECT that involved the VIEW and it does work a lot like another TABLE, but it can and mine does take data from many TABLE's into a VIEW. You can write a SELECT or even another VIEW using a VIEW. But if you keep track of the time used by a query, it starts to get too long if you use a SELECT of a VIEW that has within it another VIEW. Mysql's optimization of views is currently very rudimentary. If your view is simple (adding a computed column, etc) it directly modifies your query to match the view and runs it on the view's parent table. Otherwise it has to create a temporary table containing the entire view's contents and then run your query on that. Nested views using temptables could certainly be very slow. http://dev.mysql.com/doc/refman/5.0/en/create-view.html http://dev.mysql.com/doc/refman/5.0/en/view-restrictions.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlimport csv file import problem
Hi, I download a csv file from Yahoo in this format: ABIAX 20.63 2006-08-3 ACEIX 8.78 2006-08-3 CIGAX 10.08 2006-08-3 FSCTX 22.25 2006-08-3 GGOAX 20.55 2006-08-3 HWLAX 23.3 2006-08-3 HWMAX 28.74 2006-08-3 MLEIX 96.37 2006-08-3 NBPBX 18.98 2006-08-3 PSVIX 32.43 2006-08-3 PTRAX 10.3 2006-08-3 RGACX 30.89 2006-08-3 ^DJI 11242.6 2006-08-3 ^IXIC 2092.34 2006-08-3 ^GSPC 1280.27 2006-08-3 My table for this data is in this format | 2006-08-02 | 20.72 | 8.81 | 10.08 | 22.19 | 20.48 | 23.19 | 28.52 | 96.21 | 18.87 | 32.14 | 10.31 | 30.95 | 11199.93 | 2078.81 | 1278.55 | Is there a way to get mysqlimport to pull the data from specific column/row to insert into a specified field? Trying to find an easier way than typing all of the data into a text file for import. Thanks, -- Jim Seymour [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
