RE: replication
There is one issue. MySQL changed the way it parsed SQL join statements
in 5.03. Specifically JOINS in the older syntax using implicit joins
{tables seperated by ,} i.e.
FROM table_a, table_b
are parsed differently than they are in versions prior to 5.03. If you
have any statements that do any implicit joins the statements that ran
sucessfully on your 4.1 system may error out on your 5.0.? system.
Full details on the change and it's impact on query design can be found
here:
http://dev.mysql.com/doc/refman/5.0/en/join.html
-Original Message-
From: Prasanna Raj [mailto:[EMAIL PROTECTED]
Sent: Friday, March 03, 2006 6:28 AM
To: Octavian Rasnita
Cc: mysql@lists.mysql.com
Subject: Re: replication
Yes ..u can use older version as master and 5.0 version as slave
http://dev.mysql.com/doc/refman/5.1/en/replication-compatibility.html
http://linux.com.hk/penguin/developer/mysql/manual_Replication.html#Repl
ication_Compatibility
Ciao
Praj
On Fri, 3 Mar 2006 12:32:24 +0200
Octavian Rasnita [EMAIL PROTECTED] wrote:
Hi,
I have 2 servers. On one of them I have MySQL 4.1 (the main server)
and on
the second I have MySQL 5.0.
I want to use the second server to replicate the first server. Is it
possible or the servers should have the same version?
Or I will need to install one more MySQL 4.1 on the second server and
use it
for replicating the first server?
Thank you.
Teddy
--
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]
RE: MYSQL: Unlimited Forums, Scalablity Issues, advice please? - Bayesian Filter detected spam
Let's try to characterize the load and sizing. If the posts are mostly text. 100 forums X 100,000 posts X 2,000 bytes per post = 20GB large but not huge We have people reading the posts. Even speed readers will take minutes to read the new posts. Maybe 1 in 10 - 100 readers will do a new post. A person writing a new post will take at least 5 minutes to type the 2000 bytes. So, while this site might have 100's to 1000's of concurrent users, the load profile on the database is much lower than a dynamic database driven web site where users are constantly searching/linking and the time spent on a specific page is seconds. I suspect if you watch some typical forum activity and build a crude database interaction model, you will find that even with 1000's of connected users the database server will need to run less than 100 relatively simple selects per second and many of them will be served by the query cache. This is a very rushed analysis with lots of assumptions, but if close then I think you are looking at at most a pair of dual dual-core 4GB systems running Master Slave replication. -Original Message- From: J. Pow [mailto:[EMAIL PROTECTED] On Behalf Of jay Sent: Monday, February 27, 2006 6:06 PM To: Philip Hallstrom Cc: mysql@lists.mysql.com Subject: [SPAM] - Re: MYSQL: Unlimited Forums, Scalablity Issues, advice please? - Bayesian Filter detected spam Hi Philip, thanks for the reply. Single master + many read only slaves would only solve the problem of handling many many concurrent read accesses, by distributing the load across all slaves. However, I guess the real problem, is that the writes would still need to be performed across ALL databases, and the DB would be HUGE, would it not? Lets say I host 100 forums, with 100k posts each, every write would need to be replicated to as many slaves as I have. Thanks! Jay Philip Hallstrom wrote: Hi there, I am in the midst of creating a forums hosting site. 1. It has to be highly scalable, so I doubt a single MYSQL db with TONS of subforums that simulate full forums would do. 2. Also regarding scalablity, I hope to Add capacity as and when its needed. So i'll have one server running initially, and when it gets too crowded, i'll get two servers etc. 3. I will be providing a user with a dashboard that allows him to view all his subscribed posts across ALL forums. So lets say a user is a member of 25 forums, this dashboard view will allow the user to view all his posts across all the forums. Does anyone have advice that could point me in the right direction? I have solved the scalability issue WITHIN a forum (code can handle million + posts easy), but I havent solved the issue of scaling MULTIPLE separate forums. What about having a single write master with many read-only slaves? Then modify your code so that posts go to the master and everything else happens on the slaves? Also, does there exist any php package that helps ease the process of deciding which Server/database to connect to? For example, someone accesses FORUM A, so the script would automatically know to direct all queries to the DB in SERVER 1 etc, and if i try to access FORUM J, it would connect to SERVER 2 etc. I could easily hard code this, but I was thinking what if internal IP addresses change, or I decide to migrate a busy forum to a server of its own etc, so perhaps there is a better available packaged solution designed for this task. Create a table on a central server that contains this mapping. This server could also hold the login tables as well... Just a thought. -- 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]
RE: [SPAM] - Re: Inner join with left join - Bayesian Filter detected spam
If you want to see all of the products {even those that have never been
ordered} then you need to SELECT ... FROM products ... LEFT JOIN orders
I think you also have to do a LEFT JOIN on order_items
And pull prod_name from products {don't know what the column name in
products is}.
SELECT o.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
LEFT JOIN order_items as oi
ON (p.id = oi.product_id)
LEFT JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN 2005-01-01 00:00:00 AND 2006-02-22 23:59:59)
GROUP BY p.id
ORDER by qty ASC
-Original Message-
From: Scott Haneda [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 22, 2006 2:58 PM
To: MySql
Subject: [SPAM] - Re: Inner join with left join - Bayesian Filter
detected spam
Is this what you mean?
SELECT
p.prod_name,
count(oi.product_id) AS mycount
FROM ORDERS AS o
INNER JOIN products ON o.id=p.id
LEFT JOIN order_items AS oi ON (p.id = oi.product_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
GROUP BY oi.product_id
ORDER BY mycount;
Well, sort of, here is what I managed to coble together, which gets me
pretty close, it is just what I want, other than it is missing products
with
a zero count. This tells me those products have not been ordered ever,
but
I would like to know what they are.
SELECT o.id, oi.prod_name, sum(oi.quantity) as qty
FROM orders as o
INNER JOIN order_items as oi
ON (o.id = oi.order_id)
LEFT JOIN products as p
ON (p.id = oi.product_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN 2005-01-01 00:00:00 AND 2006-02-22 23:59:59)
GROUP BY oi.product_id
ORDER by qty ASC
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.
--
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]
RE: Byte Swapping (Re Post)
If the order of the bytes is opposite between big-endian and little-endian, then if you can get the bytes in a string REVERSE() should flip the order. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 14, 2006 3:25 PM To: gerald_clark Cc: mysql@lists.mysql.com; David Godsey Subject: Re: Byte Swapping (Re Post) gerald_clark [EMAIL PROTECTED] wrote on 02/14/2006 03:59:21 PM: [EMAIL PROTECTED] wrote: David Godsey [EMAIL PROTECTED] wrote on 02/14/2006 03:28:41 PM: Well, just thought I'd try one more time because I didn't get an answer to my question last time. So what I have is a random data stream that is sent in raw form, and based on some data definition, I can assemble with the correct data types and such. One of my requirements is that I have to store the data in raw form, and when I pull the data out, it displays based on the configuration (with the correct data types and such). So floats and doubles are IEEE standards so I don't have to worry about those, however with integer types, I may need to do some byte swapping (because this data can come from variouse systems that could be either big or little endian). So I am singling out the data I need, but now I need to add the ability to byte swap the data. Keep in mind that it would be best if I can do this in SQL so that it is portable. I realize that it can easily be done in C, but that makes my code less portable (which is also a requirement, to have it portable that is). So does anybody know of a MySQL function that is already implemented to do byte swapping? or know of a way to implement this in SQL? If not, is my only other option to write a UDF? Thanks for any help. Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey Native functions? No. Something you can cobble together? Yes. There should be several ways you can deal with your data as a string of binary characters. Just re-sequence those and you should have your bytes swapped. One idea is to use the substring functions directly on your BINARY string. Another is to use the substring functions in combination with HEX()/UNHEX() to work on an escaped version of your BINARY string. Would not the first zero value character terminate the substring, rendering it invalid? Sorry or the lame ideas but usually things like this are not handled at the database layer but rather in the application layer. Depending on which version of MySQL you are using you may be able to define a FUNCTION (a different creature than a UDF) or a STORED PROCEDURE to do the swapping. Both will be pure SQL and should meet your compatibility needs. Neither will be as fast as creating and registering a UDF, though. Shawn Green Database Administrator Unimin Corporation - Spruce Pine I don't know if it will or if it won't. The original poster (David Godsey) seems to have no trouble extracting specific subsections of raw data from his blob fields. I just assume that working with chunks of raw data that contained zeroes in them was no problem for him. His need is to somehow binarily invert sections of each number (the INET_... functions could also help) in order to convert big-endian to little-endian and vice versa. I was just trying to help point him to some possible functions that may help him to do that. Hopefully he will post back with what works and what doesn't. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - Trans.: Re: Finding the row number satisfying a conditon in a result set - Bayesian Filter detected spam
You can use a user variable [EMAIL PROTECTED] in the sample below} to number the rows in the result set. mysql set @row:=0; Query OK, 0 rows affected (0.00 sec) mysql select @row:[EMAIL PROTECTED], city_Name from citiesw limit 10; +--+-+ | @row:[EMAIL PROTECTED] | city_Name | +--+-+ |1 | !fajji !fasan | |2 | 'aadeissa | |3 | 'abas | |4 | 'abas | |5 | 'abasabad | |6 | 'abd al qader | |7 | 'abdullah kalay | |8 | 'abdullah kalay | |9 | 'abruyeh| | 10 | 'adel bagrou| +--+-+ 10 rows in set (0.00 sec) -Original Message- From: Jacques Brignon [mailto:[EMAIL PROTECTED] Sent: Monday, January 30, 2006 9:19 AM To: mysql@lists.mysql.com Subject: [SPAM] - Trans.: Re: Finding the row number satisfying a conditon in a result set - Bayesian Filter detected spam Oops! forgoten to include the list in the relply -- Jacques Brignon - Message transféré de Jacques Brignon [EMAIL PROTECTED] - Date : Mon, 30 Jan 2006 16:16:53 +0100 De : Jacques Brignon [EMAIL PROTECTED] Adresse de retour :Jacques Brignon [EMAIL PROTECTED] Sujet : Re: Finding the row number satisfying a conditon in a result set À : Jake Peavy [EMAIL PROTECTED] Selon Jake Peavy [EMAIL PROTECTED]: On 1/30/06, Jacques Brignon [EMAIL PROTECTED] wrote: I would like some advice on the various and best ways of finding the rank of the row which satisfies a given condition in a rsult set. Let's assume that the result set includes a field containing an identifier from one of the table used in the query and that not two rows have the same value for this identifier but that the result set does not contains all the sequential values for this identifier and/or the values are not sorted in any predictable order. The brute force method is to loop through all the rows of the result set, until the number is found to get the rank of the row. That does not seem very clever and it can be very time consuming if the set has a lot of rows. use ORDER BY with a LIMIT of 1 your subject line needs work though - a row number has no meaning in a relational database. -jp Thanks for the tip, I am going to think to it as I do not see right away how this solves the problem. I agree with your comment, This is precisely because the result row number is not in the database that I need to find it. The problem I am trying to solve is the following: A query returns a result set with a number of rows, lets say 15000 as an example. I have an application wich displays those 10 by 10 with arrows based navigation capabilities (first page, previous page, next page, last page). I also have a search capability and I need to find in which set of 10 results the row I search for will be diplayed in order to show directly the appropriate page and to know what is the rank of this row in the result set or in the page to show the searched result row selected. As an example the row having a customer id of 125, would have the row # 563 in the result set (not orderd by customer id but by some other criterion like name) and would therefore be displayed in the page showing result rows 561 to 570 When I say row I do not mean a row in any table but a row in the result set produced by the query which can touch several tables. None of the fields of the result set contains the row number, it is just the number of time I have to loop through the result set to get the row in the set which matches my criterion. I hope this makes my question clearer. I am sure this is a pretty common problem, but I have not yet figured out the clever way to tackle it! -- Jacques Brignon - Fin du message transféré - -- 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]
is UNION allowed in a MySQL stored procedure?
I have a simple stored procedure which works as intended. As soon as I add a UNION in the SELECT I get the error message ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select phon_Lvl INTO Lvl Are UNION's currently not allowed in a stored procedure? mysql delimiter // mysql create procedure ph() - BEGIN - DECLARE LVL Char(10); - select phon_Lvl INTO Lvl - FROM phones - - limit 1; - SET @Lvl:=Lvl; - END// Query OK, 0 rows affected (0.00 sec) mysql delimiter ; mysql mysql call ph(); Query OK, 0 rows affected (0.00 sec) mysql mysql Select @Lvl; +--+ | @Lvl | +--+ | locn | +--+ 1 row in set (0.00 sec) mysql mysql drop procedure if exists ph; Query OK, 0 rows affected (0.01 sec) mysql delimiter // mysql create procedure ph() - BEGIN - DECLARE LVL Char(10); - select phon_Lvl INTO Lvl - FROM phones - UNION - Select a into LVL - limit 1; - SET @Lvl:=Lvl; - END// ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select phon_Lvl INTO Lvl FROM phones UNION Select a into LVL limi' at line 4 mysql delimiter ; mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Show Description options??
If you are on 5.0.x you can use SELECT column_Name FROM INFORMATION_SCHEMA.columns; INFORMATION_SCHEMA is a set of VIEWS that lets you access the database structure. See http://dev.mysql.com/doc/refman/5.0/en/information-schema.html -Original Message- From: Mike OK [mailto:[EMAIL PROTECTED] Sent: Thursday, January 19, 2006 10:34 AM To: mysql@lists.mysql.com Subject: Show Description options?? Hi I was looking for a command that will list the names of my columns only. I have investigated show columns but there seems to be no way to return just the names. Any suggestions?? Thanks Mike -- 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]
RE: [SPAM] - convert help - Bayesian Filter detected spam
I just added a user variable @fdata to get visabilility outside of the procedure and this is what I get. mysql delimiter // mysql create procedure test20 () -BEGIN - DECLARE fdata BLOB; - DECLARE foffset INT UNSIGNED; - DECLARE flength INT UNSIGNED; - DECLARE tmp_int BIGINT UNSIGNED; - - SELECT 0xABCDEF0123456789 INTO fdata; - SELECT 14 INTO foffset; - SELECT 7 INTO flength; - - SELECT SUBSTR(BINARY(fdata), - FLOOR(foffset/8)+1, - CEIL((flength + (foffset %8 ))%8)) - INTO fdata; - set @fdata:=fdata; - END// Query OK, 0 rows affected (0.00 sec) mysql mysql delimiter ; mysql mysql call test20(); Query OK, 0 rows affected (0.00 sec) mysql mysql select @fdata, hex(@fdata) - - ; ++-+ | @fdata | hex(@fdata) | ++-+ | ═∩☺#E | CDEF012345 | ++-+ 1 row in set (0.00 sec) -Original Message- From: David Godsey [mailto:[EMAIL PROTECTED] Sent: Thursday, January 19, 2006 3:33 PM To: mysql@lists.mysql.com Subject: [SPAM] - convert help - Bayesian Filter detected spam I am trying to convert binary data to a bigint so I can do bitwise operations on the data, and I'm having trouble doing it. I noticed that if I have binary data and I: select data1; I get 0 (not what I'm expecting). Here is a test procedure I wrote: create procedure test20 () BEGIN DECLARE fdata BLOB; DECLARE foffset INT UNSIGNED; DECLARE flength INT UNSIGNED; DECLARE tmp_int BIGINT UNSIGNED; SELECT 0xABCDEF0123456789 INTO fdata; SELECT 14 INTO foffset; SELECT 7 INTO flength; SELECT SUBSTR(BINARY(fdata), FLOOR(foffset/8)+1, CEIL((flength + (foffset %8 ))%8)) INTO fdata; SELECT HEX(fdata); SELECT CONVERT(fdata,BIGINT) INTO tmp_int; SELECT HEX(tmp_int); END The last two selects are added to show what I would like to do, but have not been able to get it to work. Any help would be great. Thanks in advance. Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - Re: mysql 5 - disk bound - fixed - Email found in subject
One huge problem with this approach. The new table doesn't have any of the indexes that were present in the previous table. You may be better off to build a script that renames the current tables followed with the full CREATE TABLE statement(s). Do a SHOW CREATE TABLE current table; Then take that output and put it in a script RENAME TABLE current_table TO new_table_name; CREATE TABLE . ON 5.0 you can use PREPARED STATEMENTS if you want to control the new table names. See 13.7. SQL Syntax for Prepared Statements Beginning with MySQL 4.1.3, an alternative interface to prepared statements is available: SQL syntax for prepared statements. This interface is not as efficient as using the binary protocol through a prepared statement API, but requires no programming because it is available directly at the SQL level: -Original Message- From: Pooly [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 11, 2006 9:47 AM To: MySQL General Subject: [SPAM] - Re: mysql 5 - disk bound - fixed - Email found in subject 2006/1/11, George Law [EMAIL PROTECTED]: Hi All, [snip] I have to work on an automatic way to rotate these tables every week. Is there an easy way with SQL to create a new table based on the schema of an existing table? I believe CREATE TABLE newtbl SELECT blah... is what you're after : http://dev.mysql.com/doc/refman/5.0/en/create-table.html FTFM : You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement: CREATE TABLE new_tbl SELECT * FROM orig_tbl; -- Pooly Webzine Rock : http://www.w-fenec.org/ -- 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]
RE: [SPAM] - Adding data from one table to another - Bayesian Filter detected spam
You probably want a multi table update assuming you are running at least 4.0.x. Add the column(s) to A with an ALTER TABLE UPDATE A INNER JOIN B ON (... SETA.col_name = B.col_name, A.col_name_2 = B.col WHERE .. You can copy the join structure from the FROM section of your SELECT statement and paste it in the UPDATE section and move the WHERE section of the SELECT to the UPDATE. -Original Message- From: 2wsxdr5 [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 11, 2006 11:30 AM To: mysql@lists.mysql.com Subject: [SPAM] - Adding data from one table to another - Bayesian Filter detected spam I have two tables with data on people in them. Table A is a subset of table B, However, there is data about these people in table B that is not in table A. with a simple select I can do a join and get a result set with all the data I need to show, but what I would like to do is change table A so it also has one of the fields from table B. Adding the field to table A is trivial , but how do I then populate that new field with data from the table B? -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- 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]
RE: Converting decimal to binary
If by Decimal you mesn HEXIDECIMAL you can use CONV
where the 1st arg is the HEX value,
2nd arg is From Base
and 3rd arg is To Base.
You will have to suround the aliases with `'s if you really want
the names to be numeric.
mysql select MID(CONV('A5',16,2),1,1) AS `7`,
-MID(CONV('A5',16,2),2,1) AS `6`,
-MID(CONV('A5',16,2),3,1) AS `5`,
-MID(CONV('A5',16,2),4,1) AS `4`,
-MID(CONV('A5',16,2),5,1) AS `3`,
-MID(CONV('A5',16,2),6,1) AS `2`,
-MID(CONV('A5',16,2),7,1) AS `1`,
-MID(CONV('A5',16,2),8,1) AS `0` ;
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 1 | 0 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
1 row in set (0.00 sec)
-Original Message-
From: Ed Reed [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 12:16 PM
To: mysql@lists.mysql.com
Subject: Converting decimal to binary
Can anyone tell me if it's possible, in 4.1.11, to convert a decimal
number to binary and have the result be returned as a separate field for
each bit? For example, what I'd like to do is,
Select ConvertToBin(245);
And have a result that looked like this
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
- Thanks
--
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]
RE: [SPAM] - Re: Converting decimal to binary - Bayesian Filter detected spam
Actually CONV converts from any base to any base so if it is base 10
then just replace the 16's with 10's.
Too much time looking at dump's.
-Original Message-
From: Bill Dodson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 3:09 PM
To: Gordon Bruce
Cc: Ed Reed; mysql@lists.mysql.com
Subject: [SPAM] - Re: Converting decimal to binary - Bayesian Filter
detected spam
If you really do mean decimal (base 10) you could use Gordon's solution
like this:
SELECT
MID(CONV(HEX(245),16,2),1,1) AS `7`,
MID(CONV(HEX(245),16,2),2,1) AS `6`,
MID(CONV(HEX(245),16,2),3,1) AS `5`,
MID(CONV(HEX(245),16,2),4,1) AS `4`,
MID(CONV(HEX(245),16,2),5,1) AS `3`,
MID(CONV(HEX(245),16,2),6,1) AS `2`,
MID(CONV(HEX(245),16,2),7,1) AS `1`,
MID(CONV(HEX(245),16,2),8,1) AS `0` ;
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
Hope this helps.
Gordon Bruce wrote:
If by Decimal you mesn HEXIDECIMAL you can use CONV
where the 1st arg is the HEX value,
2nd arg is From Base
and 3rd arg is To Base.
You will have to suround the aliases with `'s if you really want
the names to be numeric.
mysql select MID(CONV('A5',16,2),1,1) AS `7`,
-MID(CONV('A5',16,2),2,1) AS `6`,
-MID(CONV('A5',16,2),3,1) AS `5`,
-MID(CONV('A5',16,2),4,1) AS `4`,
-MID(CONV('A5',16,2),5,1) AS `3`,
-MID(CONV('A5',16,2),6,1) AS `2`,
-MID(CONV('A5',16,2),7,1) AS `1`,
-MID(CONV('A5',16,2),8,1) AS `0` ;
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 1 | 0 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
1 row in set (0.00 sec)
-Original Message-
From: Ed Reed [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 12:16 PM
To: mysql@lists.mysql.com
Subject: Converting decimal to binary
Can anyone tell me if it's possible, in 4.1.11, to convert a decimal
number to binary and have the result be returned as a separate field
for
each bit? For example, what I'd like to do is,
Select ConvertToBin(245);
And have a result that looked like this
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
- Thanks
--
Bill Dodson
Parkline, Inc. http://www.parkline.com
phone: 304-586-2113 x149
fax: 304-586-3842
email: [EMAIL PROTECTED]
Email Disclaimer
The information in any email is confidential and may be legally
privileged. It is intended solely for the addressee. Access to the email
message by anyone else is unauthorized. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or
any action or omission taken by you in reliance on it, is prohibited and
may be unlawful. If you have received an email message in error, please
notify the sender immediately by email, facsimile or telephone and
return and/or destroy the original message.
Thank you.
--
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]
RE: [SPAM] - concat string and update question - Found word(s) remove list in the Text body
Try this UPDATE people SETphone = CASE WHEN LEFT(phone,4) = '405_' THEN MID(phone,5,20) WHEN LEFT(phone,3) = '405' THEN MID(phone,4,20) ELSE phone END FROM people WHERE LEFT(phone,3) = '405' AND LENGTH(phone) 7; This way you don't accidentally replace '405' contained in the rest of the phone number. Also, if the phone numbers contain punctuation you will need to change the '7' in the LENGTH criteria. You will have to replace 'people' and 'phone' with the appropriate table and column name respectively. -Original Message- From: 2wsxdr5 [mailto:[EMAIL PROTECTED] Sent: Friday, January 06, 2006 8:09 PM To: mysql@lists.mysql.com Subject: [SPAM] - concat string and update question - Found word(s) remove list in the Text body I have a table of people and their phone numbers, some have the area code and others do not. Everyone in this table lives in the same area code, so I would like to remove the area code from the phone number field. Basically replace '(405)' or '405-' with '' is there an easy way to do that in a query with out writing code? I know how to do it with code but would like an easier way if some one knows the SQL better than I do. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- 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]
RE: [SPAM] - Re: SQL Question - Bayesian Filter detected spam
Why not just use the ABS Function update ev98nv_tm set mome=ABS(b) where tm.tr=tr and tm.ra=ra and tm.ke=ke and tm.moti=moti ; 12.4.2. Mathematical Functions All mathematical functions return NULL in the event of an error. ABS(X) Returns the absolute value of X. mysql SELECT ABS(2); - 2 mysql SELECT ABS(-32); - 32 This function is safe to use with BIGINT values. -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Friday, January 06, 2006 12:19 PM To: Mester József; mysql Subject: [SPAM] - Re: SQL Question - Bayesian Filter detected spam - Original Message - From: Mester József [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql mysql@lists.mysql.com Sent: Friday, January 06, 2006 12:07 PM Subject: Re: SQL Question Hy If you know which values are supposed to be negative, wouldn't it be easier to do updates to your data to change all of those values to negatives? That should only need to be done once. Then use the normal SQL sum() function to add all of the values together. Thank you. Actually my first thing was update but my SQL knowledge is weak and I don't want to mess the database. I started a script which is update bad records on a copy of that database. However I didn't solve the update problem. My script is in (PL/SQL): integer a; integer b; varchar tr; varchar ra; varchar ke; varchar moti; begin select tm.tr,tm.ra,tm.ke,tm.moti,tm.mome into tr,ke,moti,a from ev98nv_tm tm where MOTI like 'Rakt.kozi-' or MOTI like 'Kiadas -' and tm.EV like '2005' if (a 0) then a=b; b = 0- b; update ev98nv_tm set mome=b where tm.tr=tr and tm.ra=ra and tm.ke=ke and tm.moti=moti ; end; But it is not working. The Primary index is tr+ra+ke+moti Rhino I'm sorry but I've just got too much to do to help you today. Perhaps Gleb's suggestion can help you do the summing as you originally wanted or perhaps someone else can jump in with suggestions. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.14/222 - Release Date: 05/01/2006 -- 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]
FW: Re: SQL Question
Why not just use the ABS Function update ev98nv_tm set mome=ABS(b) where tm.tr=tr and tm.ra=ra and tm.ke=ke and tm.moti=moti ; 12.4.2. Mathematical Functions All mathematical functions return NULL in the event of an error. ABS(X) Returns the absolute value of X. mysql SELECT ABS(2); - 2 mysql SELECT ABS(-32); - 32 This function is safe to use with BIGINT values. -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Friday, January 06, 2006 12:19 PM To: Mester József; mysql Subject: [SPAM] - Re: SQL Question - Bayesian Filter detected spam - Original Message - From: Mester József [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql mysql@lists.mysql.com Sent: Friday, January 06, 2006 12:07 PM Subject: Re: SQL Question Hy If you know which values are supposed to be negative, wouldn't it be easier to do updates to your data to change all of those values to negatives? That should only need to be done once. Then use the normal SQL sum() function to add all of the values together. Thank you. Actually my first thing was update but my SQL knowledge is weak and I don't want to mess the database. I started a script which is update bad records on a copy of that database. However I didn't solve the update problem. My script is in (PL/SQL): integer a; integer b; varchar tr; varchar ra; varchar ke; varchar moti; begin select tm.tr,tm.ra,tm.ke,tm.moti,tm.mome into tr,ke,moti,a from ev98nv_tm tm where MOTI like 'Rakt.kozi-' or MOTI like 'Kiadas -' and tm.EV like '2005' if (a 0) then a=b; b = 0- b; update ev98nv_tm set mome=b where tm.tr=tr and tm.ra=ra and tm.ke=ke and tm.moti=moti ; end; But it is not working. The Primary index is tr+ra+ke+moti Rhino I'm sorry but I've just got too much to do to help you today. Perhaps Gleb's suggestion can help you do the summing as you originally wanted or perhaps someone else can jump in with suggestions. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.14/222 - Release Date: 05/01/2006 -- 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]
RE: Can this SELECT go any faster?
Try this SELECT replace(left(history.time_sec,7),'-','') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC; This is what I get on 1 of my tables with no index on perm_user_ID , 80,000 rows in the table and 7,000 rows where perm_user_ID = 'CSRB' on version 5.0.17. mysql SELECT replace(left(pord_Timestamp,7),'-','') AS month - FROM product_order_main - WHERE perm_user_ID = 'CSRB' - GROUP BY month - ORDER BY pord_Timestamp DESC; ++ | month | ++ | 200511 | | 200510 | | 200509 | | 200508 | | 200507 | | 200506 | | 200505 | | 200504 | | 200503 | | 200502 | | 200501 | | 200412 | | 200411 | | 200410 | | 200409 | | 200408 | | 200407 | | 200406 | | 200405 | | 200404 | | 200403 | | 200402 | | 200401 | | 200312 | | 200311 | ++ 25 rows in set (0.08 sec) mysql select count(*) from product_order_main WHERE perm_user_ID = 'CSRB'; +--+ | count(*) | +--+ | 7095 | +--+ 1 row in set (0.05 sec) mysql select count(*) from product_order_main WHERE perm_user_ID = 'CSRB'; +--+ | count(*) | +--+ | 7095 | +--+ 1 row in set (0.05 sec) mysql select count(*) from product_order_main; +--+ | count(*) | +--+ |80774 | +--+ 1 row in set (0.05 sec) mysql select version(); +---+ | version() | +---+ | 5.0.17-nt | +---+ 1 row in set (0.00 sec) -Original Message- From: René Fournier [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 04, 2006 2:23 PM To: mysql@lists.mysql.com Subject: Can this SELECT go any faster? Hello, I have a table called (history) containing thousands of rows. Each row is UNIX time-stamped, and belong to a particular account. I would like to know which months a particular account has been active. (For example, maybe one account has been active since June 2004, so the SELECT should return every month since then.) Here's what I'm using: SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 7 (8 total, Query took 0.1975 sec) month 200601 200512 200511 200510 200509 200508 200507 200506 This account (216) has about 8000 rows. There are Indexes for account_id and time_sec. I'm running MySQL 5.0.16. When I run EXPLAIN, I am told: id: 1 select_type: SIMPLE table: history type: ref possible_keys: account_id key: account_id key_len: 4 ref: const rows: 6556 Extra: Using where; Using temporary; Using filesort Any ideas how I can speed this up more? (I am just starting to learn how to improve MySQL performance but clearly have a ways to go.) Thanks. ...Rene -- 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]
RE: Changing types on the fly in select queries?
Generally MySQL does format conversion for you to match data types. You can also force the CONCAT result to be integer by the following where concat(year,period,week) + 0 2007031 ^^^ -Original Message- From: Jay Paulson (CE CEN) [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 27, 2005 12:49 PM To: mysql@lists.mysql.com Subject: Changing types on the fly in select queries? I have a strange question for you all. I've inherated some code and the way the code works is that I can only mess with the WHERE part of a query. Therefore, I was wondering if something like this would be possible. WHERE where concat(year,period,week) as type int 2007031 Note that I'm trying to change the type of what the concat() is doing. Is this even possible? If so is it possible to do it in the WHERE? The reason why I think I need to do this is that 'period' is a char(2). I have to have the leading zero for every entry into the database so I can run my less than compare to it. Is there a better way of doing this than having the 'period' a char(2) type and trying to make whole concat() a type of int() on the fly? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is this a MySQL 5.0.x bug OR What am I missing?
I have 2 almost identical SQL statements {copied except 1 is a LEFT join and
the other is an INNER join}.
The INNER join gives me values for all of the fields. The LEFT join gives me
NULL's for all of the prec_... {the LEFT join table} fields.
If the INNER JOIN gives me values and not an empty set then why does the LEFT
join give me NULL's?
I have included the satements, SELECT * and CREATE TABLE for the 3 tables.
I have run this on 5.0.15 and 5.0.17.
mysql SELECT user.user_ID, sesn.user_ID, sesn.orgn_ID, prec3.orgn_ID,
- prec3.prec_Type, prec3.prec_Level,prec3.prec_Value,
prec3.prec_ID,
- if(isnull(prec3.prec_Replace_Level),
- 'sesn',
- prec3.prec_Replace_Level
- ) as pl3
- FROM users AS user
- INNER JOIN sessions AS sesn
- USING(user_ID
- )
- LEFT JOIN precedences AS prec3
- ON (prec3.orgn_ID = sesn.orgn_ID
- AND prec3.prec_Type = 'Phones'
- AND prec3.prec_Level = 'user'
- AND prec3.prec_Value = 'Primary'
- AND prec3.prec_ID = 3
- )
- where sesn.sesn_ID = 1;
+-+-+-+-+---+++-+--+
| user_ID | user_ID | orgn_ID | orgn_ID | prec_Type | prec_Level | prec_Value |
prec_ID | pl3 |
+-+-+-+-+---+++-+--+
| AGB1 | AGB1 | AXIS | NULL | NULL | NULL | NULL
| NULL | sesn |
+-+-+-+-+---+++-+--+
1 row in set (0.02 sec)
mysql
mysql
mysql SELECT user.user_ID, sesn.user_ID, sesn.orgn_ID, prec3.orgn_ID,
- prec3.prec_Type, prec3.prec_Level,prec3.prec_Value,
prec3.prec_ID,
- if(isnull(prec3.prec_Replace_Level),
- 'sesn',
- prec3.prec_Replace_Level
- ) as pl3
- FROM users AS user
- INNER JOIN sessions AS sesn
- USING(user_ID
- )
- INNER JOIN precedences AS prec3
- ON (prec3.orgn_ID = sesn.orgn_ID
- AND prec3.prec_Type = 'Phones'
- AND prec3.prec_Level = 'user'
- AND prec3.prec_Value = 'Primary'
- AND prec3.prec_ID = 3
- )
- where sesn.sesn_ID = 1;
+-+-+-+-+---+++-+--+
| user_ID | user_ID | orgn_ID | orgn_ID | prec_Type | prec_Level | prec_Value |
prec_ID | pl3 |
+-+-+-+-+---+++-+--+
| AGB1 | AGB1 | AXIS | AXIS | phones | user | Primary
| 3 | locn |
+-+-+-+-+---+++-+--+
1 row in set (0.00 sec)
mysql
mysql SELECT * FROM sessions;
+-+--+--+-+-+-+-+-+-+-+
| sesn_ID | perm_user_ID | perm_usgp_ID | user_ID | usgp_ID | acct_ID | locn_ID
| orgn_ID | sesn__Timestamp | sesn_Create |
+-+--+--+-+-+-+-+-+-+-+
| 1 | AGB1 | ADZZ | AGB1 | ADZZ | | AXIS
| AXIS | 2005-12-23 08:32:26 | 2005-12-23 08:30:02 |
| 2 | AGB1 | ADZZ | AGB1 | ADZZ | | AXIS
| AXIS | 2005-12-23 08:32:26 | 2005-12-23 08:30:07 |
+-+--+--+-+-+-+-+-+-+-+
2 rows in set (0.02 sec)
mysql
mysql SELECT * FROM users;
+-+-+-+-+--+--+---++++-+--+-+-+
| user_ID | orgn_ID | locn_ID | usgp_ID | prev_usgp_ID | user_Log_On_Name |
user_Pass | user_FName | user_LName | user_PName | user_Active | user_Who |
user_Timestamp | user_Create |
+-+-+-+-+--+--+---++++-+--+-+-+
| AGB1 | AXIS | AXIS | ADZZ | NULL | gbruce |
rgbjs1jc | Ralph | Bruce | Gordon | Yes | AGB1 |
2005-12-23 08:59:31 | NULL |
+-+-+-+-+--+--+---++++-+--+-+-+
1 row in set (0.00 sec)
mysql
mysql SELECT * FROM precedences;
RE: [SPAM] - Re: locating ibdata1 and *.ibd files in different directories. - Bayesian Filter detected spam
The following takes a little effort, but it should get you close to dump
file size.
On 5.0.x you can use Information_schema.columns to get average row
length for MyISAM tables. Take that plus the punctutation {~35 + 3* #
cols for insert per row if you enclose your columns in 's} in the
insert statements generated by mysqldump times the number of rows and
that will give you the size of the MyISAM tables.
For INNODB use
mysql select avg(length(concat( col1, col2,...))) AS Avg_Len, count(*)
- from table ;
+--+--+
| Avg_Len | count(*) |
+--+--+
| 107.5588 | 3514429 |
+--+--+
1 row in set (1 min 1.31 sec)
I would also use Information_schema.columns to get the column names so I
would not have to type them.
-Original Message-
From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 20, 2005 12:24 PM
To: mysql@lists.mysql.com
Subject: [SPAM] - Re: locating ibdata1 and *.ibd files in different
directories. - Bayesian Filter detected spam
Hello.
symbolic links! Thats a neat solution. Question: when you say
symbolic links for databases do you mean links to ibd files, ibdata1
file, either, or something else?
I've meant symbolic links for databases. See:
http://dev.mysql.com/doc/refman/5.0/en/symbolic-links.html
Is mysql smart enough not to use my indices when importing until after
the import, or should I de-activate my indices until after the import?
mysqldump from 5.0.17 distribution sets FOREIGN_KEY_CHECKS to 0; for
your version you can check this by yourself.
Also, is there a formula of what I can expect the size of the dumped
files to be?
For a pity, I don't know any formula, even approximate.
Nathan Gross wrote:
On 12/20/05, Gleb Paharenko [EMAIL PROTECTED] wrote:
Hello.
Please, next time answer to the list as well.
Sorry. I didn't realize Gmail's 'reply' didn't go to the list. First
time I noticed the 'reply to all' option in Gmail. Thanks.
As far as I know, you can't specify the location of ibd files,
they're s=
tored in the
database directory, however, you can use symbolic links for databases
to =
have
them in another place.
symbolic links! Thats a neat solution. Question: when you say
symbolic links for databases do you mean links to ibd files, ibdata1
file, either, or something else?
all databases? So the question is if I can locate the ibdata1 file
somew=
here else.
Have you tried just to change the value of innodb_home_dir to the new
location, and move there ibdata1 file?
And leave the original subdirs(databases) in the original place? I can
try. This means though, that the absolute db paths are coded into the
ibdata file.
ibd files. BUT, the ibdata1 file is still 7 gig and being
If you want do decrease the size of ibdata1 file, you should dump all
your InnoDB tables, stop the server, remove all existing tablespace
files, configure a new tablespace, restart the server, import the dump
files. In such a way you'll move all your tables which are in ibdata1
tablespace to may ibd files in databases. See:
http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html
Is mysql smart enough not to use my indices when importing until after
the import, or should I de-activate my indices until after the import?
Also, is there a formula of what I can expect the size of the dumped
files to be?
Thank you much!
-nat
--
For technical support contracts, goto
https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
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]
RE: copying data!!!
This is kind of ugly, but with the multitable limitations of 3.23 it probably works. CREATE TABLE TEMP SELECT table_1 columns except age, table_2 age FROM table_1 INNER JOIN table_2 USING (name); TRUNCATE table_1; INSERT INTO table_1 SELECT * FROM TEMP; DROP TABLE_1; -Original Message- From: Sachin Bhugra [mailto:[EMAIL PROTECTED] Sent: Friday, December 09, 2005 3:00 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Re: copying data!!! Tnx for the reply Jimmy. I also sent another question( i know its a very silly question for you all..but believe me i am tryin this for past three days and not able to get it) Pls hlp..(just give me hint in right direction, and i will try to do the rest) Tnx Sachin -- 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]
RE: copying data!!!
Sorry the DROP TABLE Should be TEMP not table1 -Original Message- From: Gordon Bruce [mailto:[EMAIL PROTECTED] Sent: Friday, December 09, 2005 3:51 PM To: Sachin Bhugra; mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: RE: copying data!!! This is kind of ugly, but with the multitable limitations of 3.23 it probably works. CREATE TABLE TEMP SELECT table_1 columns except age, table_2 age FROM table_1 INNER JOIN table_2 USING (name); TRUNCATE table_1; INSERT INTO table_1 SELECT * FROM TEMP; DROP TABLE_1; -Original Message- From: Sachin Bhugra [mailto:[EMAIL PROTECTED] Sent: Friday, December 09, 2005 3:00 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: Re: copying data!!! Tnx for the reply Jimmy. I also sent another question( i know its a very silly question for you all..but believe me i am tryin this for past three days and not able to get it) Pls hlp..(just give me hint in right direction, and i will try to do the rest) Tnx Sachin -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Format for saving date field.
What is the source of the data that is displayed on the screen. If it is
a field in a MySQL table and the data type for that field is either DATE
or DATETIME then it will intsert/update without any manipulation.
Try doing a
SELECT datefield
FROM table
Limit 15;
outside of your ASP.NET environment. {Command line, SQLYOG, Query
Browser etc.}
-Original Message-
From: Jesse Castleberry [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 10, 2005 1:49 PM
To: MySQL List
Subject: Format for saving date field.
When doing an update or insert into a database with a date field, the
format
for the data on the screen is m/d/. However, I believe that MySQL
is
expecting it in the format of -mm-dd. I'm using MySQL in an ASP.Net
application. Is there an EASY way to convert to the data to a format
that
MySQL will allow either with some MySQL function, or with an ASP.NET
function? I realize that I could rip the data apart, and put it back
in
the same format that MySQL is looking for, but there's got to be some
easier
way.
Thanks,
Jesse
--
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]
RE: Query producing default values
Try this
{I wasn't sure whether cd_nature_ltr is in ligne_trans
or transaction. This assumes ligne_trans. If it is in
transaction thatn move cd_nature_ltrsn = 2 into the ON clause.}
SELECT CASE id_ltrsn
WHEN NULL THEN 0
ELSE id_ltsrn
END AS id_ltrsn,
CASE id_ltrsn
WHEN NULL THEN NOW()
ELSE MAX(GREATEST(date_fin_ltrsn + INTERVAL 1 MONTH,
CURDATE()))
END AS subs_start
FROM ligne_trans
LEFT JOIN transaction
ON (transaction.id_trsn = ligne_trans.id_trans_ltrsn
AND transaction.id_pers_trsn = 278
)
WHERE cd_nature_ltrsn = 2
AND ligne_trans.id_cntxt_ltrsn = 1
GROUP BY ligne_trans.id_cntxt_ltrsn
When past subscipiton exixts it will produce as an example:
id_ltrsn | subs_start
-
79 | 2006-11-25
When no past subscription exists I would like to get today's date as
a
result
instead of nothing, example:
id_ltrsn | subs_start
-
0| 2005-11-09
-Original Message-
From: Jacques Brignon [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 09, 2005 9:19 AM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: Query producing default values
Thanks, that makes a lot of sense.
My only problem is that I am using here a standard piece of code on the
application side and I would hate to modify it, the thing I have all
liberty to
change is the query! Reason for trying to ask the query itself to tell
me if
there is nothing in the DB!
--
Jacques Brignon
Selon [EMAIL PROTECTED]:
Jacques Brignon [EMAIL PROTECTED] wrote on 11/09/2005 09:58:07
AM:
I have a query which scans a subscription databse to locte the most
recent
expiration date of the subscription to a given periodical or
serviceto
compute
the start date of a renewal.
It works fine when for a given person such a subscription exists. If
none
exists, as expected the query produces no results.
Any suggestion on how to transform this query to produce a default
value
set
when no past subscription exists?
Here is the query:
SELECT
id_ltrsn, MAX(GREATEST(date_fin_ltrsn + INTERVAL 1 MONTH,
CURDATE()))
AS
subs_start
FROM
ligne_trans, transaction
WHERE
transaction.id_trsn = ligne_trans.id_trans_ltrsn AND cd_nature_ltrsn
= 2
AND ligne_trans.id_cntxt_ltrsn = 1
AND transaction.id_pers_trsn = 278
GROUP BY
ligne_trans.id_cntxt_ltrsn
When past subscipiton exixts it will produce as an example:
id_ltrsn | subs_start
-
79 | 2006-11-25
When no past subscription exists I would like to get today's date as
a
result
instead of nothing, example:
id_ltrsn | subs_start
-
0| 2005-11-09
Thanks for any help you can provide
--
Jacques Brignon
You are asking the database to return with data it does not have. Can
you
not detect the fact that you found no records and use that in your
application code to supply a default date? That would be much easier
to
implement and maintain than any database-based solution. The SQL can
become quite convoluted when you start trying to simulate missing
values.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
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]
RE: Help optimize this simple find
Is it possible to change the geocodes table to look like CREATE TABLE `geocodes` ( `ip` int(10) unsigned zerofill NOT NULL default '00', `lat` double default NULL, `lon` double default NULL, PRIMARY KEY (`ip`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Then you could do SELECT lat,lon FROM geocodes WHERE ip BETWEEN 1173020467 AND 1173020467 ; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, November 04, 2005 10:33 PM To: Brian Dunning Cc: mysql@lists.mysql.com Subject: Re: Help optimize this simple find Brian Dunning [EMAIL PROTECTED] wrote on 11/04/2005 10:36:00 PM: This simple find is taking 4 to 7 seconds. Way too long!! (This is a geotargeting query using the database from IP2location.) select lat,lon from geocodes where ipFROM=1173020467 and ipTO=1173020467 The database looks like this (how IP2location recommends): CREATE TABLE `geocodes` ( `ipFROM` int(10) unsigned zerofill NOT NULL default '00', `ipTO` int(10) unsigned zerofill NOT NULL default '00', `lat` double default NULL, `lon` double default NULL, PRIMARY KEY (`ipFROM`,`ipTO`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; And there are 1.7 million records. Any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] I would bet that if you do an EXPLAIN on your query that you will see that you wound up with a full table scan. It did this because it takes fewer read operations to just scan the table than if you do an indexed lookup for any more than about 30% of the rows in any table. Can you not change the query to not use = or = ?? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sub Selects, Alias Names and stored procedures
After reading one of the recent posts from Gobi [EMAIL PROTECTED]
I took his successful query and modified it for one of my tables. It indeed
produce the correct result, but in the process raised some questions.
1. Why do list_ID and acct_ID not have to be qualified with a table
name or alias?
mysql SELECT list_ID, list_Name, acct_ID, list_Qty
- FROM lists
- INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
- FROM lists
- GROUP BY acct_id
- ) AS t
- USING (acct_ID, list_ID)
- WHERE list_Active = 'Yes'
- AND cpny_ID = 'RER1'
- LIMIT 100,10;
+-++-+--+
| list_ID | list_Name | acct_ID | list_Qty |
+-++-+--+
| 3 | Farm | BA8M | 0 |
| 10 | Woodbury | BA8Y | 100 |
| 2 | Brookview Heights 03-23-04 | BA9O | 278 |
| 4 | Magnet Mailing | BABA | 250 |
| 2 | Fall Back | BABM | 223 |
| 1 | Contact list | BACF | 71 |
| 4 | Friends/Family | BAE2 | 10 |
| 1 | St. Michael | BAE7 | 139 |
| 2 | JS Prospects | BAE8 | 196 |
| 1 | Home Focus | BAE9 | 55 |
+-++-+--+
10 rows in set (0.03 sec)
2. While the subselect does work, it appears to generate a cartesian
product. Initial guess with 5.0 and stored procedures would be that
CREATING TEMPORARY TABLE
INSERT max values in temporary
SELECT from main table joined with temporary
would run faster and still allow this to be done with 1 statement.
However, even though the explains would indicate that this was so {23508 *
7354 rows for subselect VS 6060 rows for temporary table}
actual times are {0.03 for subselect VS 0.19 for temporary table} . After doing
some playing, it is the INSERT into temporary that adds the
time even though the table was memory resident. Trying a similar request on a
table with 3.5M rows still favors the subselect
{27.50 sec for subselect VS 1 min 13.91 sec for temporary table}.
Has EXPLAIN just not caught up with SUBSELECT logic or is there something else
going on?
mysql EXPLAIN
- SELECT list_ID, list_Name, acct_ID, list_Qty
- FROM lists
- INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
- FROM lists
- GROUP BY acct_id
- ) AS t
- USING (acct_ID, list_ID)
- WHERE list_Active = 'Yes'
- AND cpny_ID = 'RER1'
- LIMIT 100,10;
++-+++--+-+-+-+---+-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
++-+++--+-+-+-+---+-+
| 1 | PRIMARY | derived2 | ALL | NULL | NULL |
NULL | NULL | 7354 | |
| 1 | PRIMARY | lists | eq_ref | PRIMARY,acct_list_ID | PRIMARY |
6 | t.acct_ID,t.list_ID | 1 | Using where |
| 2 | DERIVED | lists | index | NULL | PRIMARY |
6 | NULL | 23508 | Using index |
++-+++--+-+-+-+---+-+
3 rows in set (0.01 sec)
mysql SELECT list_ID, list_Name, acct_ID, list_Qty
- FROM lists
- INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
- FROM lists
- GROUP BY acct_id
- ) AS t
- USING (acct_ID, list_ID)
- WHERE list_Active = 'Yes'
- AND cpny_ID = 'RER1'
- LIMIT 100,10;
+-++-+--+
| list_ID | list_Name | acct_ID | list_Qty |
+-++-+--+
| 3 | Farm | BA8M | 0 |
| 10 | Woodbury | BA8Y | 100 |
| 2 | Brookview Heights 03-23-04 | BA9O | 278 |
| 4 | Magnet Mailing | BABA | 250 |
| 2 | Fall Back | BABM | 223 |
| 1 | Contact list | BACF | 71 |
| 4 | Friends/Family | BAE2 | 10 |
| 1 | St. Michael | BAE7 | 139 |
| 2 | JS Prospects | BAE8 | 196 |
| 1 | Home Focus | BAE9
RE: SQL Statement Conversion
If your MySQL server is a *nix system than table names are case sensitive. SELECT A.*, CASE CounselorOnly WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS CO FROM Activities A ORDER BY Activity I also just noticed, remove the CO = and add AS CO following the END of the case statement. -Original Message- From: Jesse Castleberry [mailto:[EMAIL PROTECTED] Sent: Friday, November 04, 2005 3:54 PM To: MySQL List Subject: SQL Statement Conversion I'm converting an application from SQL Server to MySQL. I have the following query that I need to convert: SELECT A.*, CO = CASE CounselorOnly WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END FROM Activities a ORDER BY Activity What is the proper syntax for this in MySQL? Thanks, Jesse -- 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]
RE: [SPAM] - Query help - Bayesian Filter detected spam
You might try UNION with the 1st statement pulling all products with
groupid = 0 and the 2nd pulling 1 product with groupid 1.
-Original Message-
From: John Nichel [mailto:[EMAIL PROTECTED]
Sent: Monday, October 10, 2005 3:31 PM
To: MySQL List
Subject: [SPAM] - Query help - Bayesian Filter detected spam
Hi, I have this query below, and I have been pulling my hair out for the
past couple of hours trying to get it to do what I want. As is, it
works, but I need it to consider other conditions. One of the columns
in the products table is called groupid. I need it to pull all products
with a groupid of 0 and only 1 product with a groupid 0 (doesn't
matter which one). Any help will save my sanity. ;)
SELECT products.*, MIN(pricing.price) as price , products_lng.product as
product_lng, products_lng.descr as descr_lng, products_lng.full_descr as
fulldescr_lng, IF(variants.variantid IS NOT NULL,'Y','') as is_variant,
IF(classes.classid IS NOT NULL,'Y','') as is_product_options,
MIN(v_pricing.price) as v_price, products_lng.product as product_lng,
products_lng.descr as descr_lng, products_lng.full_descr as
fulldescr_lng, IF(variants.variantid IS NOT NULL,'Y','') as is_variant,
IF(classes.classid IS NOT NULL,'Y','') as is_product_options,
MIN(v_pricing.price) as v_price FROM products, pricing ,
products_categories, categories LEFT JOIN products_lng ON
products_lng.productid = products.productid AND products_lng.code = 'US'
LEFT JOIN classes ON classes.productid = products.productid LEFT JOIN
variants ON variants.productid = products.productid LEFT JOIN pricing as
v_pricing ON v_pricing.variantid = variants.variantid AND
v_pricing.quantity = 1 AND v_pricing.membership IN ('','') WHERE
pricing.productid=products.productid AND pricing.quantity=1 AND
pricing.membership IN ('','') AND products.product_type 'C' AND
products.product_type 'B' AND (pricing.variantid = 0 OR
(variants.variantid = pricing.variantid AND variants.avail 0)) AND
products_categories.productid=products.productid AND
products_categories.categoryid = categories.categoryid AND
categories.membership IN ('','') AND
products_categories.categoryid='412' AND (products_categories.main='Y'
OR products_categories.main!='Y') AND products.forsale='Y' AND
(products.avail0 OR products.product_type NOT IN ('','N')) GROUP BY
products.productid ORDER BY products_categories.orderby ASC,
products.product ASC LIMIT 10, 10
--
John C. Nichel
KegWorks.com
716.856.9675
[EMAIL PROTECTED]
--
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]
RE: how to list foreign keys
IF you are on 5.0.x you can use INFORMATION_SCHEMA 21.1.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table http://dev.mysql.com/doc/mysql/en/key-column-usage-table.html -Original Message- From: Operator [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 05, 2005 3:02 PM To: mysql@lists.mysql.com Subject: how to list foreign keys HI everybody I'm tryng to find a way to know if a field is a foreign key, by example if I run this describe tablename; in the Key colum I got PRI for the primary key field, somebody know a way to get the foreign keys ? Regards Daniel
RE: strange order by problem
Try this
mysql select distinct secname, date
- from optresult
- where secname like 'swap%'
-and date like '2005-09-2%'
- order by if(secname like 'swap%',
- (mid(secname,5,20)+0),
- secname);
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-23 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-23 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-21 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP3| 2005-09-21 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
| SWAP5| 2005-09-21 |
| SWAP5| 2005-09-26 |
+--++
18 rows in set (0.00 sec)
-Original Message-
From: Claire Lee [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 27, 2005 2:48 PM
To: mysql@lists.mysql.com
Subject: strange order by problem
I need to order a few names by the number following
the main name. For example swap2, swap3, swap10 in the
order of swap2, swap3, swap10, not in swap10, swap2,
swap3 as it will happen when I do an order by.
So I came up with the following query:
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname,lengt
h(secname)-locate('p',secname))+0), secname);
I was hoping it will order by the number following
each 'swap' in the secname, it doesn't work. It was
ordered instead by secname.
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-23 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-23 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-21 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-21 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
+--++
However, if I replace the second expression in the if
statement by date, like the following, it's ordered by
date as I would expect.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',date, secname);
+--++
| secname | date |
+--++
| SWAP3| 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP5| 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP10 | 2005-09-21 |
| SWAP2| 2005-09-21 |
| SWAP0.25 | 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP3| 2005-09-22 |
| SWAP5| 2005-09-22 |
| SWAP10 | 2005-09-23 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP10 | 2005-09-26 |
| SWAP2| 2005-09-26 |
| SWAP3| 2005-09-26 |
| SWAP5| 2005-09-26 |
+--++
So I tried different combinations of the second and
third expressions in the if statement in the query,
the next one is the only one I can get it to order my
way, which is not what I wanted of course since I
don't want other secnames than swap% to order this
way.
mysql select distinct secname, date from optresult
where secname like 'swap%' and date like '2005-09-2%'
order by if (secname like 'swap%',(right(secname, leng
th(secname)-locate('p', secname))+0),
right(secname,length(secname)-locate('p',secname))+0);
+--++
| secname | date |
+--++
| SWAP0.25 | 2005-09-21 |
| SWAP0.5 | 2005-09-21 |
| SWAP1| 2005-09-21 |
| SWAP2| 2005-09-22 |
| SWAP2| 2005-09-26 |
| SWAP2| 2005-09-21 |
| SWAP2| 2005-09-23 |
| SWAP3| 2005-09-22 |
| SWAP3| 2005-09-26 |
| SWAP3| 2005-09-21 |
| SWAP3| 2005-09-23 |
| SWAP5| 2005-09-23 |
| SWAP5| 2005-09-22 |
| SWAP5| 2005-09-26 |
| SWAP5| 2005-09-21 |
| SWAP10 | 2005-09-26 |
| SWAP10 | 2005-09-21 |
| SWAP10 | 2005-09-23 |
+--++
Can anyone see what problems I have in my query? I'm
really stuck here. Thanks.
Claire
__
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
--
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]
RE: insert subquery
What am I missing INSERT INTO table1 (column names.) SELECT VALUES.. FROM table2 WHERE primary id = insert value You will have to put in your real table name and column names. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, September 23, 2005 11:59 AM To: DJ Cc: mysql@lists.mysql.com Subject: Re: insert subquery DJ [EMAIL PROTECTED] wrote on 09/23/2005 12:49:35 PM: [EMAIL PROTECTED] wrote: DJ wrote on 09/23/2005 12:22:58 PM: i want to insert a row into table1 only if the value being inserted on table1 exists on table2 primary id. can i do this with subquery? thanx. Depending on what version MySQL you are using, probably not. A very robust method of doing what you propose is to allow MySQL to do it for you by establishing a Foreign Key from table1 to table2. One drawback is that both tables need to be InnoDB (which you may not want to support). What version are you using and what is the possibility of using InnoDB with your appliation? Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS: always CC the list on all responses (unless you intentionally mean to take the conversation off-list) i am using 4.1.x hmm.. maybe it's easier if i just check the id is in table2 before inserting into table1. not really a big deal just looking to create shortcuts without running multiple queries.. With a foreign key defined, you only need to run one query. That's why I mentioned it. ;-) Your way works, too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Compare two tables
If you have the 5.0.x version of MySQL then INFROMATION SCHEMA can give you what you want. i.e. SELECT a.*, b.* FROM INFORMATION_SCHEMA.COLUMNS AS a INNER JOIN _SCHEMA.COLUMNS AS b ON (a.column_name = b.column_name) WHERE a.TABLE_NAME = 'foo_1' AND b.TABLE_NAME = 'foo_2' If you look up INFORMATION SCHEMA in the documentation you will find the table definitions to chose the columns you need for your comparison. 21. The INFORMATION_SCHEMA Information Database 21.1. INFORMATION_SCHEMA Tables -Original Message- From: Alfredo Cole [mailto:[EMAIL PROTECTED] Sent: Friday, August 26, 2005 9:08 AM To: mysql@lists.mysql.com Subject: Compare two tables Hi: I need to compare the structure of two tables (fields, field types, field lengths, indices, etc.) to determine if they have the same schema, even if the fields may be in a different order. Is there a command in mysql that will do this? This will be used to determine if the tables are basically the same, or if they need to be upgraded based on the table structures of a central office. Thank you. -- Alfredo J. Cole Grupo ACyC -- 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]
RE: Union vs OR
It's getting late on Friday, but couldn't you build a table with all of the parameter combinations and then just join against that table? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, August 26, 2005 4:25 PM To: mysql@lists.mysql.com Subject: Union vs OR I have a table that holds attributes for users. This is the structure: TABLE properties ( id int(11) NOT NULL, userid int(11) NOT NULL, attrType int(11) NOT NULL, attrValue text NOT NULL, FULLTEXT KEY propValue (propValue) ) TYPE=MyISAM; The table is used to find people based on criteria. A simple query: select userID, attrType, attrValue from properties where propType = 1 and propValue= 'some value' The problem I'm running into is that the number of attributes could be over 50. Would a query with many sets of (propType = 1 and propValue= 'some value') or (propType = 2 and propValue= 'some other value') or ... work better than doing the same thing with unions? Or does anyone have an alternate solution? Thanks for any help! -- Avi -- 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]
RE: Treating Two Fields Like One
I think you misunderstand how auto_increment works. Primary keys using auto_increment are NOT row numbers. If your table has a primary key that is an auto_increment field then when you add a row to the table the value of the primary key of the new row is 1 greater than the max(Value) before the row was added. Once added the value in the field does not change. Let's say you have ID V1 V2 1 a b 2 x y 3 x u 4 b a Now you delete the row with ID = 2. The row where V1=x and V2=u still has a value of 3 in the ID field. From reading the post I think to need to look at some refernces on handling tree/hierarchie structures in a relational table. Here are 2 references out of many. http://www.sitepoint.com/article/hierarchical-data-database http://www.intelligententerprise.com/001020/celko1_1.jhtml They should help you understand your 2nd question So I need a way to distinguish one leo from the other. -Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 24, 2005 2:31 PM To: mysql@lists.mysql.com Subject: Re: Treating Two Fields Like One --- Peter Brawley [EMAIL PROTECTED] wrote: As you note, the names [of animal taxons] aren't guaranteed to be unique, or to stay the same . . . One way out is to give every table an auto-incrementing integer PK, and use those keys, which will never change, to mark parent-child relationships. I wanted to follow up on this. I can easily substitute integers from my primary key for names, but how do I substitute them for parents? For example: ID | NAME | PARENT 10 | Canidae | Carnivora 11 | Canis | Canidae 12 | Vulpes |Canidae I can easily replace Canis with 11, Vulpes with 12. But they both have the same family - Canidae, which translates as 10. I could create a new field and manually, like this: ID | NAME | PARENT | PARENTID 10 | Canidae | Carnivora | 9 11 | Canis | Canidae | 10 12 | Vulpes |Canidae | 10 But if I add or delete a row, the numerals in my primary key will change, messing up the values in PARENTID. Along similar lines, I have another question... Consider the database table code below, which displays animal names (representing all taxonomic heirarchies) in a child-parent relationship: ID | NAME | PARENT 1 | Mammalia | (NULL) 2 | Carnivora | Mammalia 3 | Canidae | Carnivora 4 | Canis | Canidae 5 | leo | Canis 6 | Felidae | Carnivora 7 | Panthera | Felidae 8 | leo | Panthera Rows 5 and 8 represent identical species names, leo. If I type http://geozoo/stacks/leo/ into my browser, it defaults to Mammalia Carnivora Canidae Canis leo, rather than the lion, Mammalia Carnivora Felidae Panthera leo So I need a way to distinguish one leo from the other. Would it be possible to somehow combine my auto-incrementing primary key with the field Name, converting leo / leo to 5leo / 8leo? There are two things I'd have to deal with... 1. I'd need to weed the numerals out of the display, which should look like this... a href=http://geozoo/stacks/leo/;leo/a not this... a href=http://geozoo/stacks/8leo/;8leo/a 2. The numerals would have to be fluid, as I will be adding and deleting rows. Thus, the lion could be 8leo one day and 9leo the next. I can take this to a PHP forum to learn how to implement it. But I thought someone on this forum might tell me if it can be done in the first place. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- 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]
RE: Date arithmetic: 2005-08-31 - 1
Do You know about INTERVAL? Use it in an exprecssion or funtion as ..INTERVAL expr type where expr is any numerical value * The INTERVAL keyword and the type specifier are not case sensitive. The following table shows how the type and expr arguments are related: type Value Expected expr Format MICROSECOND MICROSECONDS SECOND SECONDS MINUTE MINUTES HOUR HOURS DAY DAYS WEEK WEEKS MONTH MONTHS QUARTER QUARTERS YEAR YEARS SECOND_MICROSECOND 'SECONDS.MICROSECONDS' MINUTE_MICROSECOND 'MINUTES.MICROSECONDS' MINUTE_SECOND 'MINUTES:SECONDS' HOUR_MICROSECOND 'HOURS.MICROSECONDS' HOUR_SECOND 'HOURS:MINUTES:SECONDS' HOUR_MINUTE 'HOURS:MINUTES' DAY_MICROSECOND 'DAYS.MICROSECONDS' DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS' DAY_MINUTE 'DAYS HOURS:MINUTES' DAY_HOUR 'DAYS HOURS' YEAR_MONTH 'YEARS-MONTHS' mysql select min(addr_id) from addresses; +--+ | min(addr_id) | +--+ |2 | +--+ 1 row in set (0.00 sec) mysql select now() + INTERVAL min(addr_ID) Day from addresses; +---+ | now() + INTERVAL min(addr_ID) Day | +---+ | 2005-08-25 15:38:15 | +---+ 1 row in set (0.00 sec) mysql select now() - ; +-+ | now() | +-+ | 2005-08-23 15:38:31 | +-+ 1 row in set (0.00 sec) -Original Message- From: Barbara Deaton [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 23, 2005 2:37 PM To: mysql@lists.mysql.com Subject: Date arithmetic: 2005-08-31 - 1 All, I know MySQL comes with all sorts of wonderful functions to do date arithmetic, the problem is the context that my application is being called in I don't know if a user wants me to add or subtract days. I'm just given the number of days that need to be either added or subtracted from the date given. So for example, if your table was mysql select * from dtinterval; + | datecol + 2005-09-01 2005-08-30 2005-08-31 +-- a user could enter: select count(*) from dtinterval where datecol - 1 = '30AUG2005'd; Which is our applications SQL, my part of the product is only give the value 1, I have to transform that into something MySQL will understand as 1 day and then pass that back into the SQL statement to be passed down to the MySQL database. I transform our applications SQL into select COUNT(*) from `dtinterval` where (`dtinterval`.`datecol` - 1) = '1974-12-04' I know that just doing the -1 is wrong, since select '2005-08-31' - 1 and that just gives me a year mysql select '2005-08-31' - 1; +--+ | '2005-08-31' - 1 | +--+ | 2004 | +--+ What do I need to translate the 1 into in order to get back the value '2005-08-30' ? Thanks for your help. Barbara -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT ... INTO OUTFILE ... and LOAD DATA INFILE ... with a key
If you want to have all values except the primary key be the same and
say your is foo_ID
You can simply do
INSERT INTO foo
(foo_ID... {rest of columns list})
SELECT new primary key value,
{rest of columns list}
FROM foo
WHERE foo_ID = {primary key value of row you want to copy}
If your PRIMARY KEY is an auto_increment field, just omit foo_ID from
the columns list in both the INSERT and SELECT.
-Original Message-
From: suomi [mailto:[EMAIL PROTECTED]
Sent: Friday, August 19, 2005 7:08 AM
To: mysql@lists.mysql.com
Subject: SELECT ... INTO OUTFILE ... and LOAD DATA INFILE ... with a key
Hi listers
I once asked if there is an SQL syntax permitting to copy a row in the
same table. I got no answer, so there is no such syntax.
now i meant to have found a work-around using (see subject).
problem is, that when i do a SELECT * ... INTO OUTFILE .. i will also
catch the PRIMARY KEY column if there is one and the LOAD DATA INFILE
... of this file will fail because of duplicate keys. i tried to use the
FOREIGN_KEY_CHECKS=0 but obiousely this works on foreign keys not on the
primary key.
certainly, i can very very clumsily construct a SELECT at1, ... atn INTO
OUTFILE statement which selects all columns except the primary key.
the REPLACE and IGNORE constructs are not what i want either, because i
want to add a row in any case, not replace an existing one nore ignore
the action.
is there a more elegant way then the clumsy making of an attr list,
which includes alle columns except the primary key column?
thanks very much for your interest and understanding.
suomi
--
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]
RE: query
You can use INTERVAL i.e. Lets say you have a table mysql CREATE TABLE foo (bar int(14), fdate date ); Query OK, 0 rows affected (0.27 sec) mysql INSERT INTO foo VALUES (1, now()), (25,now() - INTERVAL 1 DAY), (15,now() - INTERVAL 2 DAY); mysql SELECT f1.bar - f2.bar AS diff , f2.fdate - FROM foo AS f1 -INNER JOIN foo AS f2 -ON (f1.fdate = f2.fdate + INTERVAL 1 DAY); +--++ | diff | fdate | +--++ | -24 | 2005-08-15 | | 10 | 2005-08-14 | +--++ 2 rows in set (0.00 sec) -Original Message- From: Octavian Rasnita [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 16, 2005 7:51 AM To: mysql@lists.mysql.com; Felix Geerinckx Subject: Re: query Hi, I define the previous record by date. For each record corresponds a date which is unique and the previous record is that that contains the yesterday date. Thank you. Teddy - Original Message - From: Felix Geerinckx [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 16, 2005 2:45 PM Subject: Re: query On 16/08/2005, Octavian Rasnita wrote: I want to create a query that selects the diference between the value of a field from the current record and the value of the same field from the previous record. How do you define current record and previous record? (relational databases are not spreadsheets) -- felix -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Index - max key length is 1024 bytes
If you are on a version prior to 4.1.2 the max index size is 500 bytes
{not sure why the error mentions 1024}
From section 14.1 of documention
The maximum key length is 1000 bytes (500 before MySQL 4.1.2). This can
be changed by recompiling. For the case of a key longer than 250 bytes,
a larger key block size than the default of 1024 bytes is used.
-Original Message-
From: javabuddy [mailto:[EMAIL PROTECTED]
Sent: Friday, August 12, 2005 12:30 PM
To: mysql@lists.mysql.com
Subject: Index - max key length is 1024 bytes
I am trying to create an index with multiple fields.
The sixe of each of the column is listed and thier sum is 560bytes. But
when I try to create an index with the colums, it complains on the size
exceeded 1024 bytes. Below is the query and the size of each..
create index selectTechnologyClubsThread_idx on content
(club_id, date_update, subject, message_id, id, date_published,
comment_count_d, display_usr_name_d, short_content)
COLUMN_NAME TYPESIZE
club_id Bigint 8
date_update Bigint 8
Subject Varchar(120)121
message_id varchar(120)121
Id Bigint 8
date_published Datetime8
comment_count_d Int 4
short_content Varchar(250)251
display_usr_name_d Varchar(30) 31
TOTAL : 560
Any sort of help would be great
- javabuddy
People are conversing... without posting their email or filling up their
mail box. ~~1123867827435~~
roomity.com http://roomity.com/launch.jsp No sign up to read or search
this Rich Internet App
--
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]
RE: JOIN QUERY - UPDATE ... help?!
Multi Table UPDATES are first supported in 4.0.x -Original Message- From: Brendan Gogarty [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 09, 2005 12:16 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: JOIN QUERY - UPDATE ... help?! Brendan Gogarty [EMAIL PROTECTED] wrote on 08/09/2005 05:30:51 AM: Hi, We are running mysql 3.23.58 and I want to do a query with joins from two tables and then insert the results into the column of a third. This appears to be harder than I realised with this version of mysql and I am banging my head against a wall. Please Help! ok first query. [snip] any ideas? Start from here: http://dev.mysql.com/doc/mysql/en/update.html Updates *are* allowed to use JOINED tables as the thing to be updated. Which means that an UPDATE statement can look VERY MUCH like a SELECT statement turned on it's head. In your case, I think you are trying to figure out how to flip this: select link_ID,of_group FROM page_elements LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID WHERE content_type='text' into this (while adding the `links_db` table into the mix: UPDATE links_db INNER JOIN page_elements ON page_elements.link_ID=links_DB.link_ID LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID SET *** see note*** WHERE content_type='text'; *** note: your SET clause can reference ANY column from ANY table defined in your UPDATE clause. You are not limited to just changing one table at a time. Just make sure you properly identify the columns you want to get data from and which ones you want to set. Now, you didn't say exactly what you wanted to update with what or I would have filled in more of the SET clause. If you want to flip a SELECT ... GROUP BY statement into an UPDATE statement, you have to go through a temporary table first. That is because the GROUP BY eliminates any one-to-one row-to-value mappings 99.9% of the time. There is no UPDATE ... GROUP BY command for any RDBMS that I know of. However, if you save the results of the SELECT...GROUP BY statement, you can JOIN to that table in an UPDATE statement just like any other data. Let me know if you run into any more issues and I can help you work it out. Hi Shaun, I'm afraid after a few hours of testing various things it doesn't work. I am pretty sure its a version issue as even the simplest query such as UPDATE links_DB LEFT JOIN page_elements SET links_DB.in_group=0 brings up an error ' MySQL said: You have an error in your SQL syntax near 'LEFT JOIN page_elements SET links_DB.in_group = 0' at line 1 ' cheers, brendan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Number of SQL Queries curiosity
We have a relatively small web site running 4.0.20 on a Dell 2850 running RedHat. We rarely see any SQL statements visible when we run SHOW PROCESSLIST and typically use 30 - 40 concurrent connections. The Number of SQL Queries graph in MySQL Administrator usually is in the 0 to 10 range with occaisional spikes to 40 - 50. Lately I have been noticing the Max value in the Number of SQL Queries setting at 2,000 +. This happens maybe once or twice a day and I have only been looking at the display when it happened 1 time. There does not seem to be any unusal difference in the rest of the graphs, page hits on the web site, network traffic etc. Nothing seems to be impacted when this activity occurs. I just don't understand what could cause this kind of activity on the server with our web site usage profile. We do have some people using access through ODBC, but I have not been able to recreate the event. Does anyone have any ideas on what could cause this? Shouild I be concerned?
RE: top one row
This is out of the MySQL class and is called the Max-Concat trick. What is the biggest country in each continent? The Rows Holding the Group-wise Maximum of a Certain Field MySQL Select Continent, - SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS Country, - 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS Population - From Country - Group By Continent - Order By Population DESC; +---+--+ + | Continent | Country | Population | +---+--+ + | Asia | China| 1277558000 | | North America | United States| 278357000 | | South America | Brazil | 170115000 | | Europe| Russian Federation | 146934000 | | Africa| Nigeria | 111506000 | | Oceania | Australia| 18886000 | | Antarctica| South Georgia and the South Sandwich Islands | 0 | +---+--+ It looks ugly but what you have to do is tie the data you want together and let the max work on the collection and then split it back out again in the display. So in your case SELECT col1, LEFT(MAX(CONCAT(LPAD(col2,10,'0'),col3)),10) + 0 AS col2, MID((MAX(CONCAT(LPAD(col2,10,'0'),col3)),11,50) + 0 As col3 FROM sample GROUP BY col1 -Original Message- From: Kemin Zhou [mailto:[EMAIL PROTECTED] Sent: Friday, August 05, 2005 12:38 PM To: mysql@lists.mysql.com Subject: top one row I have a simple table col1 col2col3 A 2 3 A 100 70 A 100080 B20 90 B7080 To select the top one row for each unique value of col1 select distinct on (col1), col1, col2, col3 from table order by col1, col2 desc, col3 desc; What I want is A 1000 80 B 70 80 How do you do it in mysql? Kemin -- 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]
RE: advanced group by
Something like this SELECT CompanyName, WhatToShip, SUM(IF(TrackingNumber = '', IF(SerialNumber = '', 1, 0), 0) ) AS READY, SUM(IF(TrackingNumber '', IF(SerialNumber = '', 1, 0), 0) ) AS Almost, SUM(IF(TrackingNumber '', IF(SerialNumber '', 1, 0), 0) ) AS Done FROM shipments GROUP BY 1,2 -Original Message- From: James M. Gonzalez [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 03, 2005 5:59 AM To: mysql@lists.mysql.com Subject: advanced group by Hello list! little GROUP BY problem here: Table 'shipments' ID int(10) CompanyName char(50) WhatToShip char(50) TrackingNumber char(50) SerialNumber char(50) I would like to obtain the following results: CompanyName - WhatToShip - Ready - Almost - Done Foo- car - 26 - 2- 23 Foo-elephant - 43 - 0- 15 Foo-acuarium - 12 - 6- 47 Bar- mobile- 9- 0- 52 Bar- fan - 15 - 4- 43 Ready: items with empty TrackingNumber and empty SerialNumber Almost: items with popullated TrackingNumber AND empty SerialNumber Done: items with popullated TrackingNumber and popullated SerialNumber I have been reading around and trying lots of things. I believe the answer lies on how to group by an empty field. This means, I believe I can make this work if I find a way to group by a field's emptiness or not, instead of the actual content. Google is tired of seeinf me search around for ' advanced grouping by ' and so on and on. Any help will be greatly apprecieted. (Im begging for help) James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Impossible join?
Here is one way. Some time ago I set up a table named count with one field named count and built 5000 rows of incrementing values . I think I originally populated it by originally createing it with a 2nd field CREATE TABLE `count` ( `count` int(10) unsigned NOT NULL auto_increment, `addr_ID` int(10) NULL, this field does not really matter PRIMARY KEY (`count`) ) ENGINE=InnoDB DEFAULT CHARSET=; and then doing something like INSERT INTO count(addr_ID) SELECT addr_ID any primary key out of any table with 5000 entries FROM addresses LIMIT 5000; and then droping the 2nd field. Just put an INSERT in front of the select and set the value = to the # of dates you want to populate and the set value to 1 day pior to where you want to start. mysql set @d:='2004-12-31 00:00:00'; Query OK, 0 rows affected (0.00 sec) mysql select @d:[EMAIL PROTECTED] + interval 1 day as date from count where count = 10; +-+ | date| +-+ | 2005-01-01 00:00:00 | | 2005-01-02 00:00:00 | | 2005-01-03 00:00:00 | | 2005-01-04 00:00:00 | | 2005-01-05 00:00:00 | | 2005-01-06 00:00:00 | | 2005-01-07 00:00:00 | | 2005-01-08 00:00:00 | | 2005-01-09 00:00:00 | | 2005-01-10 00:00:00 | +-+ 10 rows in set (0.00 sec) -Original Message- From: Jonathan Mangin [mailto:[EMAIL PROTECTED] Sent: Monday, July 18, 2005 8:03 AM To: Michael Stassen Cc: mysql@lists.mysql.com Subject: Re: Impossible join? Jonathan Mangin wrote: Hello all, I'm storing data from a series of tests throughout each 24-hour period. I thought to create a table for each test. (There are six tests, lots more cols per test, and many users performing each test.) But each test is performed no more than once per day by a given user? Correct. select test1.date, test1.time, test2.date, test2.time from test1 left join test2 on test2.date=test1.date where test1.date between '2005-07-01' and '2005-07-16' and uid='me'; Something is strange here. Doesn't uid exist in both tables? I'll assume it does. Oops. Also correct. ++--++--+ | date | time | date | time | ++--++--+ | 2005-07-13 | 6:30 | 2005-07-13 | 7:30 | | 2005-07-14 | 6:32 | 2005-07-14 | 7:45 | | 2005-07-15 | 6:30 | 2005-07-15 | 7:42 | | 2005-07-16 | 6:35 | NULL | NULL | ++--++--+ Is there a join, or some other technique, that would return (nearly) these same results if test1 (or any test) has not been performed? Using 4.1.11. TIA, Jon [ SNIP! ] A better solution would be to add a table: CREATE TABLE `testdates` (`date` date default NULL, UNIQUE KEY `date_idx` (`date`) ); Insert one row into testdates for each day. Now you can use something like this: SELECT testdates.date, test1.time AS 'Test 1', test2.time AS 'Test 2' FROM testdates LEFT JOIN test1 on testdates.date = test1.date AND test1.uid = 'me' LEFT JOIN test2 on testdates.date = test2.date AND test2.uid = 'me' WHERE testdates.date BETWEEN '2005-07-11' AND '2005-07-16'; ++--+--+ | date | Test 1 | Test 2 | ++--+--+ | 2005-07-11 | NULL | NULL | | 2005-07-12 | NULL | 07:28:00 | | 2005-07-13 | 06:30:00 | 07:30:00 | | 2005-07-14 | 06:32:00 | 07:45:00 | | 2005-07-15 | 06:30:00 | 07:42:00 | | 2005-07-16 | 06:35:00 | NULL | ++--+--+ 6 rows in set (0.01 sec) Much better, don't you think? This generalizes pretty well, too. SELECT testdates.date, test1.time AS 'Test 1', test2.time AS 'Test 2', test3.time AS 'Test 3', test4.time AS 'Test 4' FROM testdates LEFT JOIN test1 on testdates.date = test1.date AND test1.uid = 'me' LEFT JOIN test2 on testdates.date = test2.date AND test2.uid = 'me' LEFT JOIN test3 on testdates.date = test3.date AND test3.uid = 'me' LEFT JOIN test4 on testdates.date = test4.date AND test4.uid = 'me' WHERE testdates.date BETWEEN '2005-07-11' AND '2005-07-16'; ++--+--+--+--+ | date | Test 1 | Test 2 | Test 3 | Test 4 | ++--+--+--+--+ | 2005-07-11 | NULL | NULL | NULL | 08:12:00 | | 2005-07-12 | NULL | 07:28:00 | 07:14:00 | 08:14:00 | | 2005-07-13 | 06:30:00 | 07:30:00 | 07:16:00 | 08:29:00 | | 2005-07-14 | 06:32:00 | 07:45:00 | 07:14:00 | 08:26:00 | | 2005-07-15 | 06:30:00 | 07:42:00 | 07:19:00 | NULL | | 2005-07-16 | 06:35:00 | NULL | NULL | NULL | ++--+--+--+--+ 6 rows in set (0.00 sec) Michael I'm guessing this is a common solution. Shame on me. How does one swiftly populate a table with an entire year (or more) of dates? Thanks very much, Jon
RE: Renaming a database
A database in MySQL is simply a directory. So just rename the directory with appropriate tool for your platform. On my test box this becomes mysql show databases; ++ | Database | ++ | information_schema | | lois | | mailprint | | mysql | | test | ++ 5 rows in set (0.22 sec) mysql show databases; ++ | Database | ++ | information_schema | | lois | | mailprint | | mysql | | test1 | ++ 5 rows in set (0.00 sec) -Original Message- From: Scott Hamm [mailto:[EMAIL PROTECTED] Sent: Monday, July 18, 2005 11:02 AM To: 'Mysql ' Subject: Renaming a database How do I rename a database? The help online is pretty ambigous. -- Power to people, Linux is here.
RE: Count(*)
select count(distinct ordr_ID) from store -Original Message- From: Gana [mailto:[EMAIL PROTECTED] Sent: Thursday, July 07, 2005 3:01 PM To: mysql@lists.mysql.com Subject: Count(*) select count(*) from store group by orederId. For the above sql, I am not getting the count of unique order ids.. help!! -- 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]
RE: question about field length for integer
If you really need more than 20 digits of accuracy and can move to 5.0.3 + you can use the Decimal data type without losing precision. This is out of Chapter 23. Precision Math The maximum value of 64 for M means that calculations on DECIMAL values are accurate up to 64 digits. This limit of 64 digits of precision also applies to exact-value numeric literals, so the maximum range of such literals is different from before. (Prior to MySQL 5.0.3, decimal values could have up to 254 digits. However, calculations were done using floating-point and thus were approximate, not exact.) This change in the range of literal values is another possible source of incompatibility for older applications. Values for DECIMAL columns no longer are represented as strings that require one byte per digit or sign character. Instead, a binary format is used that packs nine decimal digits into four bytes. This change to DECIMAL storage format changes the storage requirements as well. Storage for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires four bytes, and the leftover digits require some fraction of four bytes. For example, a DECIMAL(18,9) column has nine digits on each side of the decimal point, so the integer part and the fractional part each require four bytes. A DECIMAL(20,10) column has 10 digits on each side of the decimal point. Each part requires four bytes for nine of the digits, and one byte for the remaining digit. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, June 27, 2005 10:34 AM To: Eko Budiharto Cc: mysql@lists.mysql.com Subject: Re: question about field length for integer Eko Budiharto [EMAIL PROTECTED] wrote on 06/26/2005 11:02:30 AM: Hi, is there anyway that I can have more than 20 digits for integer (bigInt)? If not, what I can use for database index? BIGINT UNSIGNED can range from 0 to 18446744073709551615 (http://dev.mysql.com/doc/mysql/en/numeric-types.html) Are you actually saying that you have a database with more than 1.8e+19 records in it? I don't think you do. I think you are combining several pieces of information into something that looks like a number and it's exceeding the storage limits of even BIGINT. What you have is actually a good idea but you are physically limited by the capacity of the column types available. In this case if you cannot create all of your key values so that they look like numbers smaller than 18446744073709551615, it can't fit into a BIGINT UNSIGNED column. You do have some options: a) change the way you create your server keys so that they fit in the value allowed b) use a character-based column to store your server key values c) use some other value to identify your servers (IP address, for example) d) create a table of server keys: CREATE TABLE server ( ID int auto_increment , name varchar(25) not null , ip int unsigned , ... (any other fields you could define to describe this server) , PRIMARY KEY (ID) , UNIQUE(name) ) Then, refer to your servers using server.id instead of your composited key. e) ...? (I am sure there are more ideas from others on the list) To answer your literal question: No, MySQL cannot store integer values that contain more than 20 digits. Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Intersting MySQL / Access Issue
When I have seen this error it was caused by a field defined in the MySQL database as NOT NULL in the Create table and the value in Access is NULL or usually for us an empty field in EXCEL which is appears to be intreped as NULL when you do a PASTE APPEND. Ours is often times a datetime field but I don't think its limited to data time fields defined as NOT NULL. I have not really found an easy wasy to find the offending field except by process of elimination. Hope this helps. -Original Message- From: Edward Maas [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 21, 2005 10:31 PM To: mysql@lists.mysql.com Subject: Intersting MySQL / Access Issue Dear Community, My team and I have been experiencing an interesting mysql error during the past few weeks of testing. Here is the scenario we are trying to accomplish. We are essentially working to use MsAccess as a windows client for a linux based mysql databases. We have installed myODBC 3.51 and are using that for communication. We seem to be able to create a linked table just find and select queries work great. The problem arises when we try to update or insert data. Updates yield the following error: You Tried to assign the Null value to a variable that is not a Variant data type. From my searching, I was unable to find how to set fields to variant data types. Secondly, I am not sure which field is causing the error. The second issue was with inserts. On insert of just one field (none are required other than the primary key), ALL of the fields of type text are set to NULL. This is particularly odd and occurs also in the mysql command line utilty. If anyone has any ideas or experience, please send emails to [EMAIL PROTECTED] We will definitely summarize the solution for the educaitonal purposes of the list. Again many thanks in advance. Sincerely,, Ed Maas -- 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]
RE: Backup database with MyISAM and InnoDB tables together
If you are runing binary log and do a FLUSH LOGS mysqldump --opt --skip-lock-tables MyISAM table names FLUSH LOGS mysqldump --opt --single-transaction INNODB table names You have a recoverable state with the combination of the mysqldump file and the binary log file that was started by the 1st FLUSH LOGS command. The recovered database wil be restored to the date time that the 2nd FLUSH LOGS command was issued instead of the start time of the backup, but you won't have to lock all of your tables and it wil give you a consistent state across a mixed INNODB MyISAM environment. The downside is -you have 3 files to deal with -you have to maintain the table names in the mysqldump commands -you have a small risk of a change ocurring in the MyISAM tables between time the 2nd FLUSH LOGS is executed and the 2nd mysqldump command is executed -Original Message- From: Scott Plumlee [mailto:[EMAIL PROTECTED] Sent: Friday, June 17, 2005 10:21 AM To: mysql@lists.mysql.com Subject: Backup database with MyISAM and InnoDB tables together I'm not clear on best practice to use on a database containing both MyISAM and InnoDB tables. For the MyISAM tables, it seems better to use mysqldump --opt, thus getting the --lock-tables option, but for the InnoDB the --single-transaction is preferred. Since they are mutually exclusive, is there a best practice to get consistent state of the tables when the database dump is performed? Would `mysqldump --opt --skip-lock-tables --single-transaction` be best for a database that is mostly InnoDB tables, but does have a few MyISAM tables? WOuld I be better off locking the database from any updates/inserts, and specifying particular commands for individual tables? Any advice appreciated, including RTFMs with links. -- 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]
RE: Backup database with MyISAM and InnoDB tables together
There are 3 things [that are exclusivly MyISAM}.
Full Text index
autoincrement column as the last column in a multi column primary key
MERGE tables
Tables which don't require these features can be INNODB tables. We have
a few tables that use these, otherwise we are exclusively INNODB.
There is another way to do backups. It's what we use.
Capture the file names in your database
Extract the .frm files and build select into outfile and coresponding
load data infile statements for each file name {i.e.table}
Sample
select * into outfile '/usr/data/mailprint/day/user.txt' from
mysql.user;
load data infile 'user.txt' ignore into table user;
put the select statements in a file with flush logs before and after the
set of select statements
run the file through a CRON as mysql -h ... filename
Now we have a text file for each table in a directory which we can
zip/tar, move to a different machine snd selectively restore the tables
via the load data commands, restore a single table in a test database
and recover/rebuild a specific tabel in the live database, etc.
Our 4GB database takes 5 minutes to save every night. A full restore
takes 30 minutes including moving the data files to the right place. We
keep 1 month of the nightly copies and day 1 of each month for a year.
We have development, stage and live servers and started doing this to
give us better granularity for selectively synching tables or parts of
tables. {I need these 500 rows from this table to move the dev project
to staging}. It has also been invaluable in the time when a developer
was on the wrong server and inadvertantly corupted an entire column of
the user table. We did not want to take down the site and do a
restore/rollforward to right before the stupid command was executed.
We just needed to fix the data in this one column in one table.
Sorry I started to ramble.
Oh I almost forgot, we also periodically dump the structure with
mysqldump --no-datato capture the structure.
Our table defintitions are relatively stable so we don't do it every
night. You could put it in the cron job to do it with the backup.
-Original Message-
From: Scott Plumlee [mailto:[EMAIL PROTECTED]
Sent: Friday, June 17, 2005 12:36 PM
To: mysql@lists.mysql.com
Subject: Re: Backup database with MyISAM and InnoDB tables together
-Original Message-
From: Scott Plumlee [mailto:[EMAIL PROTECTED]
Sent: Friday, June 17, 2005 10:21 AM
To: mysql@lists.mysql.com
Subject: Backup database with MyISAM and InnoDB tables together
I'm not clear on best practice to use on a database containing both
MyISAM and InnoDB tables. For the MyISAM tables, it seems better to
use
mysqldump --opt, thus getting the --lock-tables option, but for the
InnoDB the --single-transaction is preferred. Since they are
mutually
exclusive, is there a best practice to get consistent state of the
tables when the database dump is performed?
Would `mysqldump --opt --skip-lock-tables --single-transaction` be
best
for a database that is mostly InnoDB tables, but does have a few
MyISAM
tables?
WOuld I be better off locking the database from any updates/inserts,
and
specifying particular commands for individual tables?
Any advice appreciated, including RTFMs with links.
Gordon Bruce wrote:
If you are runing binary log and do a
FLUSH LOGS
mysqldump --opt --skip-lock-tables MyISAM table names
FLUSH LOGS
mysqldump --opt --single-transaction INNODB table names
You have a recoverable state with the combination of the mysqldump
file
and the binary log file that was started by the 1st FLUSH LOGS
command.
The recovered database wil be restored to the date time that the 2nd
FLUSH LOGS command was issued instead of the start time of the backup,
but you won't have to lock all of your tables and it wil give you a
consistent state across a mixed INNODB MyISAM environment.
The downside is
-you have 3 files to deal with
-you have to maintain the table names in the mysqldump commands
-you have a small risk of a change ocurring in the MyISAM
tables between time the 2nd FLUSH LOGS is executed and the 2nd
mysqldump command is executed
Thanks for the tip. I haven't looked into binary logs too much, just
learned about them the other day when I had to correct my own mistake
and restore a table.
Is is best practice to go with tables of all one sort to allow for
consistent state when doing backups like this, or are mixed tables the
norm in most databases? I went with the InnoDB in order to not have to
do row level locking on the tables as transactions were performed and
I've been very pleased with the results. I might consider just going
with all InnoDB to make it easy, as those are the majority of my tables
in this case.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED
RE: alter only an enum label
If you have c values in the table currently you can just do an
ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','c','x')
DEFAULT a NOT NULL
then
UPDATE tablename SET columname = 'x' WHERE columname = 'c'
Then
ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','x')
DEFAULT a NOT NULL
-Original Message-
From: Gabriel B. [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 16, 2005 12:54 PM
To: LISTA mysql
Subject: alter only an enum label
If i have a table with about 800M records. and one of the fields is a
enum(a, b, c) and i want to change it to enum(a,b,x) will
it fall into some optimization and be instant?
and what if previously i've never used the c value? isn't there any
optimization for that? ...leaving blank labels on a enum? or another
command to add new labels to a enum?
thanks,
Gabriel
--
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]
RE: alter only an enum label
The ALTER TABLE is going to copy the entire table when it executes the
ALTER TABLE so it will take some time. Depends on your server, diaks,
table type etc..
One alternative might be to do a
SELECT a, enumcolumn INTO OUTFILE 'x' FROM tablename;
TRUNCATE tablename;
ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','c')
DEFAULT a NOT NULL;
LOAD DATA INFILE 'x' INTO TABLE tablename;
I know this seems obtuse, but load data infile and select into outfile
seem to run very fast and for what ever reason may just be faster than
the ALTER TABLE on the fully populated table.
-Original Message-
From: Gabriel B. [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 16, 2005 1:18 PM
To: mysql@lists.mysql.com
Subject: Re: alter only an enum label
hum... clever. i liked that solution.
but do have experience on how long it will take with milions of records?
all records havin only a int(11) as unique key and the enum field..
suposing now i have enum(a, b) only, and did a ALTER TABLE
tablename CHANGE columnname columnname ENUM('a','b','c');
thanks,
Gabriel
2005/6/16, Gordon Bruce [EMAIL PROTECTED]:
If you have c values in the table currently you can just do an
ALTER TABLE tablename CHANGE columnname columnname
ENUM('a','b','c','x')
DEFAULT a NOT NULL
then
UPDATE tablename SET columname = 'x' WHERE columname = 'c'
Then
ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','x')
DEFAULT a NOT NULL
-Original Message-
From: Gabriel B. [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 16, 2005 12:54 PM
To: LISTA mysql
Subject: alter only an enum label
If i have a table with about 800M records. and one of the fields is a
enum(a, b, c) and i want to change it to enum(a,b,x) will
it fall into some optimization and be instant?
and what if previously i've never used the c value? isn't there any
optimization for that? ...leaving blank labels on a enum? or another
command to add new labels to a enum?
thanks,
Gabriel
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Foreign key constraint problem
In some hierarchies I have seen people put the the current id in the
parent_ID Field {basicaly pointing to them self} to represent the top of
the hierarchy.
I don't know how much this would affect the rest of your application but
it would get rid of the null's
-Original Message-
From: Marcus Bointon [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 14, 2005 11:37 AM
To: mysql@lists.mysql.com
Subject: Foreign key constraint problem
I have a table that uses a self join to represent simple hierarchies.
I have a parent_id field that contains a reference to the same
table's id field. Not all items have a parent, so parent_id is nullable.
The problem I run into is in defining the foreign key constraint - if
a parent item is deleted, I want all the children to cascade delete.
But it seems I can't combine cascade deletion with nullable - I can
never have more than one record with a parent_id of null because the
insert causes the foreign key constraint to fail. This means I can
never have more than one tree stored in the table. How should I set
up this relation so it works how I want? I'd really prefer not to
maintain it manually...
Marcus
--
Marcus Bointon
Synchromedia Limited: Putting you in the picture
[EMAIL PROTECTED] | http://www.synchromedia.co.uk
--
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]
RE: discuss: user management
It always helpd me to change MySQL's user to connection in my head
when I begin to think about access control. Then in most database
designs that I have seen, row access control is just as important as
database/table/column. Then the question becomes does the user have
direct access to the database or is there an intervening filter
[application code].
Now on a development environment I typically group the developers {i.e.
roles} and let each user {person} in a group use a common connection.
Even then the # of connections has to be small and relatively generic
{Select on these 20 tables, Select/ Update on these 45 tables, Select/
Insert/ Update/ Delete on these 5 tables}. I have yet to find the DBA
that can define unique MySQL users for 500 people.
In an Accountiing environment I still will have groups/roles but much
of the identification/enforcement will be done through a combination of
application code and the use of specific database connections. This way
I can enforce things like population of last changed by and timestamp
fields, application navigation recording as well as row level access
control. {i.e. I should only be able to see my own employee data or some
parts of the data for people reporting to me.} Direct access via SQL
would be extremely limited.
-Original Message-
From: Danny Stolle [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 14, 2005 2:05 PM
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: discuss: user management
Hi Kevin,
i started this discussion to find out, how most database administrators
or users involved in managing MySql, would deal with a topic as User
Management. So the question(s) is(are) more hypothetical, e.g. What if
(...) 'you would have a development site and an accounting site' how
would you plan your user management?
I like the way you state your opinion on User Management and the
examples you give. It is not so that i would stick on these options, if
there are other ideas, please let us discuss them.
but if you have given some examples, i would like to give an example on
the 3th option: it is not so that you have to create a user with these
prefixes (_dev, _arch); why not having departmentnames as userID's or
perhaps fantasynames as userID's (which could be uses as role names).
your question on the role-part: 'why would somebody create roles?' is an
interesting question. i have no direct answer to this question. the only
thing i would come up with is: when you have a lot of tables and you
have to change a privilege on several tables. you have the choice for
changing that for 40 users each or 5 roles each.
Best Regards,
Danny Stolle
EmoeSoft, Netherlands
Kevin Struckhoff wrote:
Danny,
I would stay away from option 3 for exactly the example you provided.
You have 1 user with 2 roles. What if you had 30 users with 2 roles? I
would choose option 2 because I would only have to maintain 2 users in
MySQL, not 60 as you would in option 3. For option 1, you would have
30
users, but then you would to give them the 'most permissible'
privileges
of the 2 roles.
What I don't know is why you need to have roles in the first place. Do
you have a large number of users and a large number of roles?
Kevin Struckhoff
Customer Analytics Mgr.
NewRoads West
Office 818.253.3819 Fax 818.834.8843
[EMAIL PROTECTED]
-Original Message-
From: Danny Stolle [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 14, 2005 11:12 AM
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: discuss: user management
Hi Kevin,
yes it is a complex matter, i agree completely. but how would you plan
this as a dba or the person involved on administrating MySql. For
instance: You would choose option 2 as the preferable one. But what
would you do if somebody would change its role or that the person
would
get other privileges? he will get a new or already created role
userID,
but would still be able to logon using the previous user id.
why wouldn't you choose for the 3th option or 1st option? what
disadvantages do you think would option 1 and 3 have?
Best regards,
Danny Stolle
EmoeSoft, Netherlands
Kevin Struckhoff wrote:
Danny,
Although my experience with MySQL user management is limited to just
maintaining a handful of users, I find it rather overly-complex
because
of the need to maintain a table of users and 'from where' they can
have
access, and to what databases they can have access to. For example, I
just installed MySQL Administrator on my laptop and then I had to add
rows allowing me to access MySQL from my laptop. The ODBC connection
setup should suffice. For every instance of MySQL, you have to have an
entry in the user table for every user from every access point. Then
multiply that by the number of databases in each instance and you can
see that administration of the users can get out of hand.
If I had to choose between the 3 methods listed below, I would choose
#2
if
RE: CREATE TABLE and specifying DEFAULT
I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows and they
both seem to look fine {see the SHOW CREATE TABLE's following the CREATE
TABLE statements}
RUN ON 4.0.20
mysql CREATE TABLE ID (
- mat INT UNIQUE PRIMARY KEY,
- ID_firstname CHAR(35) DEFAULT 'filler',
- ID_lastname CHAR(35) DEFAULT 'filler',
- ID_ramqnb CHAR(12) DEFAULT 'filler',
- ID_numciv_hosp CHAR(10) DEFAULT '-9',
- ID_appt_hosp CHAR(10) DEFAULT '-9',
- ID_streetname_hosp CHAR(75) DEFAULT '-9',
- ID_streettype_hosp CHAR(6) DEFAULT '-9',
- ID_streettype_spec_hosp CHAR(25) DEFAULT 'humbug',
- ID_direction_hosp CHAR(2) DEFAULT '-9',
- ID_city_hosp CHAR(50) DEFAULT '-9',
- ID_city_spec_hosp CHAR(150) DEFAULT 'filler',
- ID_province_hosp CHAR(2) DEFAULT 'QC',
- ID_postal_code_hosp CHAR(7) DEFAULT '-9',
- ID_phone_number_hosp CHAR(12) DEFAULT '-9',
- ID_work_number_hosp CHAR(20) DEFAULT '-9',
- ID_cell_number_hosp CHAR(12) DEFAULT '-9',
- ID_numciv_study CHAR(10) DEFAULT '-9'
- );
Query OK, 0 rows affected (0.03 sec)
mysql show create table ID;
+---+---
-
-
| Table | Create Table
+---+---
-
-
| ID| CREATE TABLE `ID` (
`mat` int(11) NOT NULL default '0',
`ID_firstname` char(35) default 'filler',
`ID_lastname` char(35) default 'filler',
`ID_ramqnb` char(12) default 'filler',
`ID_numciv_hosp` char(10) default '-9',
`ID_appt_hosp` char(10) default '-9',
`ID_streetname_hosp` char(75) default '-9',
`ID_streettype_hosp` char(6) default '-9',
`ID_streettype_spec_hosp` char(25) default 'humbug',
`ID_direction_hosp` char(2) default '-9',
`ID_city_hosp` char(50) default '-9',
`ID_city_spec_hosp` char(150) default 'filler',
`ID_province_hosp` char(2) default 'QC',
`ID_postal_code_hosp` char(7) default '-9',
`ID_phone_number_hosp` char(12) default '-9',
`ID_work_number_hosp` char(20) default '-9',
`ID_cell_number_hosp` char(12) default '-9',
`ID_numciv_study` char(10) default '-9',
PRIMARY KEY (`mat`),
UNIQUE KEY `mat` (`mat`)
) TYPE=MyISAM |
+---+---
-
-
1 row in set (0.00 sec)
___
RUN ON 5.0.6
mysql CREATE TABLE ID (
- mat INT UNIQUE PRIMARY KEY,
- ID_firstname CHAR(35) DEFAULT 'filler',
- ID_lastname CHAR(35) DEFAULT 'filler',
- ID_ramqnb CHAR(12) DEFAULT 'filler',
- ID_numciv_hosp CHAR(10) DEFAULT '-9',
- ID_appt_hosp CHAR(10) DEFAULT '-9',
- ID_streetname_hosp CHAR(75) DEFAULT '-9',
- ID_streettype_hosp CHAR(6) DEFAULT '-9',
- ID_streettype_spec_hosp CHAR(25) DEFAULT 'humbug',
- ID_direction_hosp CHAR(2) DEFAULT '-9',
- ID_city_hosp CHAR(50) DEFAULT '-9',
- ID_city_spec_hosp CHAR(150) DEFAULT 'filler',
- ID_province_hosp CHAR(2) DEFAULT 'QC',
- ID_postal_code_hosp CHAR(7) DEFAULT '-9',
- ID_phone_number_hosp CHAR(12) DEFAULT '-9',
- ID_work_number_hosp CHAR(20) DEFAULT '-9',
- ID_cell_number_hosp CHAR(12) DEFAULT '-9',
- ID_numciv_study CHAR(10) DEFAULT '-9'
- );
Query OK, 0 rows affected (0.91 sec)
mysql
mysql CREATE TABLE ID1 (
- mat INT PRIMARY KEY UNIQUE,
- ID_firstname CHAR(35),
- ID_lastname CHAR(35),
- ID_ramqnb CHAR(12),
- ID_numciv_hosp CHAR(10) DEFAULT '-9',
- ID_appt_hosp CHAR(10) DEFAULT '-9',
- ID_streetname_hosp CHAR(75) DEFAULT '-9',
- ID_streettype_hosp CHAR(6) DEFAULT '-9',
- ID_streettype_spec_hosp CHAR(25),
- ID_direction_hosp CHAR(2) DEFAULT '-9',
- ID_city_hosp CHAR(50) DEFAULT '-9',
- ID_city_spec_hosp CHAR(150),
- ID_province_hosp CHAR(2) DEFAULT 'QC',
- ID_postal_code_hosp CHAR(7) DEFAULT '-9',
- ID_phone_number_hosp CHAR(12) DEFAULT '-9',
- ID_work_number_hosp CHAR(20) DEFAULT '-9',
- ID_cell_number_hosp CHAR(12) DEFAULT '-9'
-
- );
Query OK, 0 rows affected (0.16 sec)
mysql show create table ID;
+---+---
-
-
| Table | Create Table
RE: How to control database size in MySQL Windows?
If you are on 5.0.n there is an INFORMATION_SCHEMA which you can query like this. A casual scan of the mysql tables don't show any sizes and I don't know of a way to get table/database size via SQL. mysql select table_schema, sum(DATA_LENGTH) from information_schema.tables group by 1; ++--+ | table_schema | sum(DATA_LENGTH) | ++--+ | information_schema | 0| | mailprint | 2523448288 | | mysql | 275126 | | test | 16510| ++--+ 4 rows in set, 79 warnings (6.22 sec) -Original Message- From: Salama hussein [mailto:[EMAIL PROTECTED] Sent: Friday, June 10, 2005 1:31 PM To: mysql@lists.mysql.com Subject: How to control database size in MySQL Windows? I think the answer to this is You can't. So I guess what I can do is run a query once every while and get the sizes of all the databases and if any exceeds a predetermined size, revoke insert and update privilages. What's is the SQL query like to get a database size and the SQL to get the names of all the databases? Salama -- 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]
RE: Seriously.. When are we going to get subqueries?!
You can have any number of timestamp columns, but only one of them can be set to autoupdate. As of 4.1 you are not limited to this being the 1st one in the table and CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(), or NOW() can be used in the DEFAULT. Read http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html The DATETIME, DATE, and TIMESTAMP Types for MUCH more detail. -Original Message- From: Greg Whalin [mailto:[EMAIL PROTECTED] Sent: Thursday, June 09, 2005 2:54 PM To: Jeff Smelser Cc: mysql@lists.mysql.com Subject: Re: Seriously.. When are we going to get subqueries?! Jeff Smelser wrote: On Thursday 09 June 2005 01:26 pm, George L. Sexton wrote: Another limitation in MySQL is that you can only have one timestamp column with a default of CURRENT_TIMESTAMP. How many friggin times do I have to say that this is not an issue with 4.1 and above? Which, BTW, is production mysql.. Why do you keep bringing this up? Jeff Are you sure? I don't see that from http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html It seems that w/ 4.1, you can specify any ONE timestamp col w/ default of CURRENT_TIMESTAMP. You are not limited to the 1st one, but still seems you are limited to a max of 1 timestamp. Or am I reading this wrong? -- 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]
RE: Are partial searches possible?
You can try
if($queryID = mysql_query(SELECT *
FROM WhInventory
WHERE Booking like ('%15%')
ORDER BY Booking,$dbLink))
-Original Message-
From: Don [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 08, 2005 1:44 PM
To: mysql@lists.mysql.com
Subject: Are partial searches possible?
Using MySQL 4.0.24 with PHP 4.3.11
I have the following code:
if($queryID = mysql_query(SELECT * FROM WhInventory WHERE
Match(Booking)
AGAINST (' . mysql_escape_string($form['booking']) . ' IN BOOLEAN
MODE)
ORDER BY Booking,$dbLink)) {
Which works fine if I have an exact entry but fails for a partial entry.
For
example. If I have an entry where Booking is 'TSIN15' and I search
on
the string 'TSIN15', it is found.
How can I get it to find a partial match if the string I enter is only
'15' ?
Thanks,
Don
--
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]
