MYSQL FUNCTIONS
Hi All, While i was going through mysql reference manual. I saw that A query cannot be cached if it contains any of the functions shown below BENCHMARK() CONNECTION_ID() CONVERT_TZ() CURDATE() CURRENT_DATE() CURRENT_TIME() CURRENT_TIMESTAMP() CURTIME()DATABASE() ENCRYPT() with one parameter FOUND_ROWS() GET_LOCK() LAST_INSERT_ID() LOAD_FILE() MASTER_POS_WAIT() NOW() RAND() RELEASE_LOCK() UNIX_TIMESTAMP() with no paramet- SLEEP() SYSDATE() USER() On my production server, the following query is being used. select * from student where regis_date=now(); Then what should i do so that the query get cached. Thanks, Prajapati
Re: MYSQL FUNCTIONS
Krishna Chandra Prajapati schrieb: Hi All, While i was going through mysql reference manual. I saw that A query cannot be cached if it contains any of the functions shown below BENCHMARK() CONNECTION_ID() CONVERT_TZ() CURDATE() CURRENT_DATE() CURRENT_TIME() CURRENT_TIMESTAMP() CURTIME()DATABASE() ENCRYPT() with one parameter FOUND_ROWS() GET_LOCK() LAST_INSERT_ID() LOAD_FILE() MASTER_POS_WAIT() NOW() RAND() RELEASE_LOCK() UNIX_TIMESTAMP() with no paramet- SLEEP() SYSDATE() USER() On my production server, the following query is being used. select * from student where regis_date=now(); Then what should i do so that the query get cached. this would be like a time service would record once the current time, and than always just send this recorded time ... wired, not? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL FUNCTIONS
On Mon, 10 Mar 2008, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: While i was going through mysql reference manual. I saw that A query cannot be cached if it contains any of the functions shown below ... NOW() On my production server, the following query is being used. select * from student where regis_date=now(); Then what should i do so that the query get cached. http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/ is a paper that explains a bit about MySQL caching. It starts First let me clarify what MySQL Query Cache is - I've seen number of people being confused, thinking MySQL Query Cache is the same as Oracle Query Cache - meaning cache where execution plans are cached. MySQL Query Cache is not. It does not cache the plan but full result sets. That appears to be an expansion of the official text at http://dev.mysql.com/doc/refman/5.0/en/query-cache.html, which is The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. (6.0's page has the same sentence.) That leads me to think that the only way to cache the proposed query would be to stop time. Otherwise, the result of running NOW() will change from run to run, causing a different result set. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
declaration of constants in MySQL functions
Hello, is there a possibility to declare constants in MySQL functions? declare test_name varchar(50) DEFAULT 'test'; The variable test_name here ist changeable but it should not be. Oracle syntax: test_name CONSTANT VARCHAR2(50) := 'test'; Regards, Spiker -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kanns mit allen: http://www.gmx.net/de/go/multimessenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Functions
Hello.
See:
http://search.cpan.org/~spidb/Net-ext-1.011/lib/Net/Inet.pm
Mike Blezien [EMAIL PROTECTED] wrote:
Hello,
Sorry for the slightly OT question :)
Hoping we have some expert MySQL Function to perl function people on the list.
Is there an equivelant function in perl for converting IP's, the same as
MySQL
functions: INET_ATON('IP_ADDRESS_STR') and INET_NTOA('STR')
TIA,
--
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 Functions
Hello,
Sorry for the slightly OT question :)
Hoping we have some expert MySQL Function to perl function people on the list.
Is there an equivelant function in perl for converting IP's, the same as MySQL
functions: INET_ATON('IP_ADDRESS_STR') and INET_NTOA('STR')
TIA,
--
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Obtaining a List of Available MySQL Functions
Does anyone know an easy way to get a list of all MySQL Functions that can be used within queries? I've looked at the docs and tried all the obvious stuff (show func...etc). Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Obtaining a List of Available MySQL Functions
In the last episode (Mar 13), James Kiser said: Does anyone know an easy way to get a list of all MySQL Functions that can be used within queries? I've looked at the docs and tried all the obvious stuff (show func...etc). ? The documentation has a whole chapter on it. http://dev.mysql.com/doc/mysql/en/functions.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL functions
In MySQL versions before 5.0, you can write a user-defined function in C and compile it as a shared object. Beginning with MySQL 5.0, you can write stored routines. See these sections of the MySQL Manual: http://dev.mysql.com/doc/mysql/en/Adding_functions.html http://dev.mysql.com/doc/mysql/en/Adding_procedures.html Date: Sat, 30 Oct 2004 23:13:42 +0200 To: [EMAIL PROTECTED] From: Ferhat BINGOL [EMAIL PROTECTED] Subject: MySQL functions Message-ID: [EMAIL PROTECTED] Hi, How do I add a new function to MySQL SQL statement list. What I mean is AVG(), MIN() or MAX() is a ready function isnt it? Is there a way to add new functions without compiling all server? Regards... -- Jon Stephens, Technical Writer MySQL AB www.mysql.com Office: +61 (07) 3388 2228 Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL functions
Hi, How do I add a new function to MySQL SQL statement list. What I mean is AVG(), MIN() or MAX() is a ready function isnt it? Is there a way to add new functions without compiling all server? Regards... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[MYSQL]{FUNCTIONS]
Hi, I have designed a application (written in visual basic) to visualize projects in a treeview and to attach all kinds of information to those treenodes (for example hours used, budget avail, pictures of manufactoring parts/ bought-out parts etc). After using microsoft access for a short time, I have switched to mysql. After rewriting al the querys, this gave already a mutch better performance. To have one common language between designer, manufactoring users and financial users, I have intoduced the tooltree code as the position in the treeview. for example, we have a project called '2143' (this indicates the 43st project for customer with ID=21. In a project we can have multiple lines. For line 31 the tooltreecode will be 2143.31. In line 31 we can have multiple zones. For zone 020 the tooltreecode then will be 2143.31.020. In total we can go up to 8 levels. The tooltreecode a presented in the treeview is build out of seperate strings. Since this can take a lot of time (average number of nodes in a project = 15000), the tooltree is opened upto level 2 when starting. Then by clicking a node the next level is opened and the string are being build. The function I use in visual basic looks like: Public Function FullNodeName(anid As Long) As String Dim aRset As ADODB.Recordset Dim ParentId As Long Dim newname As String ParentId = 0 cond = SELECT NodeParent, NodeName FROM TTNode WHERE TTNodeId= CStr(anid) Set aRset = objDBConnection.Execute(cond, ErrStr) With aRset .MoveFirst If Not .EOF Then ParentId = .Fields(0) newname = .Fields(1) End If .Close End With Set aRset = Nothing If newname And ParentId 0 Then FullNodeName = FullNodeName(ParentId) . newname Else FullNodeName = newname End If End Function I'am triing to speed up the access time even more and was thinking that it should be possible to have the tooltree string returned by the mysql server as part of the resultset. I then have to make a new user function in mysql. I was hooping that such a function already excists. Can anybody help me. -- Peter Bruggink Manager mechanical Design +31 76 5792732 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] *Steelweld BV* Terheijdenseweg 169 The Netherlands www.steelweld.com http://www.steelweld.com/ *DISCLAIMER* The information transmitted is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed. If you received this in error, please contact the sender and delete the material from any computer. This mail has been checked for all known viruses by McAfee Virusscan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using Mysql functions in INSERTS and UPDATES in replication setup
We are using Mysql with one master and several slaves. In some INSERT and UPDATE queries we use Mysql functions like CURDATE() and RAND() . The problem that now occurs is that if a slave is reading the queries from the binary log, the resulting data is different from that on the master, because of delays. Apparently the literal queries including the function calls in the queries are recorded in the binary log. I expected the master writing the results from the functions into the binary log instead of the function calls themselves. In the manual of MySQL I did not find any hints about this issue. One solution is to first get the results from the mysql-function with a SELECT query, and then INSERT the results from the functions. But I would vote for a change in the writing method into the binary log. Can somebody comment on this? Arnoud Witt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Mysql functions in INSERTS and UPDATES in replication setup
Arnoud Witt (Marktplaats) [EMAIL PROTECTED] wrote: We are using Mysql with one master and several slaves. In some INSERT and UPDATE queries we use Mysql functions like CURDATE() = and RAND() . The problem that now occurs is that if a slave is reading the queries = from the binary log, the resulting data is different from that on the master, because of delays. Apparently the literal queries including the function calls in the queries are recorded in the binary log. I expected the master writing the results from the functions into the = binary log instead of the function calls themselves. In the manual of MySQL I = did not find any hints about this issue. One solution is to first get the results from the mysql-function with a = SELECT query, and then INSERT the results from the functions. But I = would vote for a change in the writing method into the binary log. Can somebody comment on this? What versions of MySQL do you use? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
vb.net aggregate MySQL functions
I'm having problems with vb.net and MySQL, only on reading records returned with requests for functions, i.e., such as the following SELECT max(field1) AS MaxValue FROM Table1 or SELECT concat( field1, ' ', field2) as FullName FROM Table1 The queries work fine when executed directly from mysqlfront. It is only when attempting to read them from vb using oledb datareader() that present a problem. Any queries not using functions work fine. dataReader claims no fields have been returned. I'm using MyOLEDB version 3.00 TIA, Charlie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql Functions
Hi Can anyone tell me how to call Mysql UDF functions from Java?? Thanks Amit - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
can i use mysql functions to sort an alpha-numeric description alphabetically?
Hi, I have looked at the online documentation and the mysql books that I have but can find no answer. I have a field that looks like this: (8+2) Landscape I want to sort on the alphabetic characters. My mysql query does an order by this field and that returns a numeric order (which for me is not terribly useful.) So - is there a function or way I can sort this array alphabetically rather than numerically? Thanks, Nicole -- Nicole Lallande [EMAIL PROTECTED] 760.753.6766 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: can i use mysql functions to sort an alpha-numeric description alphabetically?
Hi Nicole, Perhaps it's me, but I'm having trouble following you - as I did with the question I've just finished responding to... I have looked at the online documentation and the mysql books that I have but can find no answer. I have a field that looks like this: (8+2) Landscape I take it that this is the data value stored in the field. What does the schema look like? I want to sort on the alphabetic characters. My mysql query does an order by this field and that returns a numeric order (which for me is not terribly useful.) What do you mean by numeric order? If there was another row containing: (72+18) Landscape Then it would likely appear before the row you mentioned - and not in a numeric sequence at all. Do you mean that you want the parentheses, the digits, the plus sign, AND the space character ignored for the purposes od the sequence, and thereafter that the values should be treated as alpha? Is there any possibility of numerics appearing after the first alpha character? Would that matter or would they have to be ignored as well? So - is there a function or way I can sort this array alphabetically rather than numerically? Yes there are various ways and means, right up to the 'expensive' regular expression function. I think we can solve this one quickly enough - given a good understanding so we don't end up chasing red herrings! Please post the query you are using currently, together with a few rows of output. Then highlight the problem by showing the sequence that you would prefer to see. Please advise, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: can i use mysql functions to sort an alpha-numeric description alphabetically?
Sorry Dan, I had actually replied to Steve who had asked the same question but I forgot to reply to the list with this: here is the query: select catval,catdescr from embiteccat where catzid=$zid and catlid=$lid and catunder=$cat order by catdescr; Here is a some data: 36,1,1,,0,0,2(12+1) Long,,,0,0,prodsku,,6,:6:36:,0,0,1,10 38,1,1,,0,0,24+1 Landscape,,,0,0,prodsku,,6,:6:38:,0,0,1,10 41,1,1,,0,0,6 Portrait,,,0,0,prodsku,,6,:6:41:,0,0,1,10 43,1,1,,0,0,24+1 Long,,,0,0,prodsku,,6,:6:43:,0,0,1,10 44,1,1,,0,0,2(24+1) X-Long,,,0,0,prodsku,,6,:6:44:,0,0,1,10 where catdesc is the 7th field (ie, 2(12+1) Long etc. So far all I can think of is that I have to load it into an array, sort the array with some php function or grep and then spit it out... So - yes you are correct - I want everything before the alphabetic characters, (number, parenthesis, + sign) to be ignored and then to sort by the alpha. TIA, Nicole DL Neil wrote: Hi Nicole, Perhaps it's me, but I'm having trouble following you - as I did with the question I've just finished responding to... I have looked at the online documentation and the mysql books that I have but can find no answer. I have a field that looks like this: (8+2) Landscape I take it that this is the data value stored in the field. What does the schema look like? I want to sort on the alphabetic characters. My mysql query does an order by this field and that returns a numeric order (which for me is not terribly useful.) What do you mean by numeric order? If there was another row containing: (72+18) Landscape Then it would likely appear before the row you mentioned - and not in a numeric sequence at all. Do you mean that you want the parentheses, the digits, the plus sign, AND the space character ignored for the purposes od the sequence, and thereafter that the values should be treated as alpha? Is there any possibility of numerics appearing after the first alpha character? Would that matter or would they have to be ignored as well? So - is there a function or way I can sort this array alphabetically rather than numerically? Yes there are various ways and means, right up to the 'expensive' regular expression function. I think we can solve this one quickly enough - given a good understanding so we don't end up chasing red herrings! Please post the query you are using currently, together with a few rows of output. Then highlight the problem by showing the sequence that you would prefer to see. Please advise, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Nicole Lallande [EMAIL PROTECTED] 760.753.6766 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: can i use mysql functions to sort an alpha-numeric description alphabetically?
Nicole, RTFM: 6.3 Functions for Use in SELECT and WHERE Clauses in particular the String Functions If the first space (in every row) can be taken as the 'marker' of what to remove (including the space itself), then take a look at the following: SELECT catval, RIGHT( strcatdescr, POSITION( ' ' IN strcatdescr ) + 1 ) AS cd FROM embiteccat WHERE catzid=$zid AND catlid=$lid AND catunder=$cat ORDER BY cd; If however there may be more than one space prior to the commencement of the alpha data, then can we search for the last space (ie the first from the right-hand end of the field)? In which case substitute: SUBSTRING_INDEX( strcatdescr, ' ', -1 ) AS cd It's kind of fun to play games like this, but it isn't very 'relational'. Both of the assumptions (above) are not 'good form'. Even if one is acceptable to you, and of course 'in spades' if neither is, should be the consideration of adding another column to the table for sequencing purposes. How are we doing? =dn (David) Sorry Dan, I had actually replied to Steve who had asked the same question but I forgot to reply to the list with this: here is the query: select catval,catdescr from embiteccat where catzid=$zid and catlid=$lid and catunder=$cat order by catdescr; Here is a some data: 36,1,1,,0,0,2(12+1) Long,,,0,0,prodsku,,6,:6:36:,0,0,1,10 38,1,1,,0,0,24+1 Landscape,,,0,0,prodsku,,6,:6:38:,0,0,1,10 41,1,1,,0,0,6 Portrait,,,0,0,prodsku,,6,:6:41:,0,0,1,10 43,1,1,,0,0,24+1 Long,,,0,0,prodsku,,6,:6:43:,0,0,1,10 44,1,1,,0,0,2(24+1) X-Long,,,0,0,prodsku,,6,:6:44:,0,0,1,10 where catdesc is the 7th field (ie, 2(12+1) Long etc. So far all I can think of is that I have to load it into an array, sort the array with some php function or grep and then spit it out... So - yes you are correct - I want everything before the alphabetic characters, (number, parenthesis, + sign) to be ignored and then to sort by the alpha. TIA, Nicole DL Neil wrote: Hi Nicole, Perhaps it's me, but I'm having trouble following you - as I did with the question I've just finished responding to... I have looked at the online documentation and the mysql books that I have but can find no answer. I have a field that looks like this: (8+2) Landscape I take it that this is the data value stored in the field. What does the schema look like? I want to sort on the alphabetic characters. My mysql query does an order by this field and that returns a numeric order (which for me is not terribly useful.) What do you mean by numeric order? If there was another row containing: (72+18) Landscape Then it would likely appear before the row you mentioned - and not in a numeric sequence at all. Do you mean that you want the parentheses, the digits, the plus sign, AND the space character ignored for the purposes od the sequence, and thereafter that the values should be treated as alpha? Is there any possibility of numerics appearing after the first alpha character? Would that matter or would they have to be ignored as well? So - is there a function or way I can sort this array alphabetically rather than numerically? Yes there are various ways and means, right up to the 'expensive' regular expression function. I think we can solve this one quickly enough - given a good understanding so we don't end up chasing red herrings! Please post the query you are using currently, together with a few rows of output. Then highlight the problem by showing the sequence that you would prefer to see. Please advise, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Nicole Lallande [EMAIL PROTECTED] 760.753.6766 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ANSI SQL or MySQL Functions?
Can anyone help point me to a good resource for finding out if particular functions supported by MySQL are ANSI SQL standard functions or if they are MySQL specific? I'm particularly wondering about: concat() if() length() Thanks, -Chris --- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ANSI SQL or MySQL Functions?
At 9:08 AM -0700 5/10/01, Chris Nichols wrote: Can anyone help point me to a good resource for finding out if particular functions supported by MySQL are ANSI SQL standard functions or if they are MySQL specific? I'm particularly wondering about: concat() if() length() http://www.mysql.com/doc/C/o/Compatibility.html Thanks, -Chris --- [EMAIL PROTECTED] -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
