Ah, I see now. That makes sense. Sorry to have been so dense.
Michael
Paul DuBois wrote:
At 21:37 -0500 11/10/04, Michael Stassen wrote:
Paul DuBois wrote:
At 21:11 -0500 11/10/04, Michael Stassen wrote:
Toro Hill wrote:
Hi all.
I have question about how the function quote() works with NULL values. Here is what the mysql manual say:
---
QUOTE(str)
Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned surrounded by single quotes and with each instance of single quote (`''), backslash (`\'), ASCII NUL, and Control-Z preceded by a backslash. If the argument is NULL, the return value is the word ``NULL'' without surrounding single quotes. The QUOTE() function was added in MySQL 4.0.3.
mysql> SELECT QUOTE('Don\'t!');
-> 'Don\'t!'
mysql> SELECT QUOTE(NULL);
-> NULL
---
Now after reading this I thought that select quote(NULL) would return the string 'NULL' (without quotes) and not the NULL value. However, this is not true on the version of mysql that I'm using:
You've misunderstood, then. Without the quotes, NULL is the NULL value, not a string. You have to have quotes to be a string! NULL is NULL, 'NULL' is a string. MySQL is doing exactly what the manual says.
It's not. The manual says that if the argument is NULL, the return value is _the word_ NULL without quotes. In other words, it's a string but the string doesn't include surrounding quotes.
Without quotes, it's not a string. I agree the manual is not worded as clearly as it should be, but everywhere else in MySQL, NULL without quotes means NULL, not a string. Why should here be any different.
You're overthinking it. Strings don't need quotes unless you're writing them as string literals. The quotes aren't _part of the string_.
QUOTE() is intended for generating string values to be used for constructing SQL statements. Consider the following sequence of statements:
SET @a = 'abc', @b = 'def';
SET @stmt = CONCAT('INSERT INTO t VALUES(', QUOTE(@a), ',', QUOTE(@b), ');');
SELECT @stmt;
SET @a = 'abc', @b = NULL;
SET @stmt = CONCAT('INSERT INTO t VALUES(', QUOTE(@a), ',', QUOTE(@b), ');');
SELECT @stmt;
The intended result is:
+------------------------------------+ | @stmt | +------------------------------------+ | INSERT INTO t VALUES('abc','def'); | +------------------------------------+ +-----------------------------------+ | @stmt | +-----------------------------------+ | INSERT INTO t VALUES('abc',NULL); | +-----------------------------------+
That only works if QUOTE(NULL) returns the word NULL without quotes. That's why it's a bug for it to actually return a NULL value. The actual result from the preceding statements is:
+------------------------------------+ | @stmt | +------------------------------------+ | INSERT INTO t VALUES('abc','def'); | +------------------------------------+ +-------+ | @stmt | +-------+ | NULL | +-------+
INSERT INTO mytable VALUES ('NULL'), (NULL);
The first is a string, the second is NULL.
The manual is quite clear that QUOTE expects a string as input. Strings require quotes.
String literals do.
mysql> SELECT QUOTE(a string);
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 'string)' at line 1
mysql> SELECT QUOTE(string); ERROR 1054 (42S22): Unknown column 'string' in 'field list'
Interestingly, MySQL will attempt to convert non-string values to strings so that QUOTE can work on them.
mysql> SELECT QUOTE(2); +----------+ | QUOTE(2) | +----------+ | '2' | +----------+ 1 row in set (0.00 sec)
What string should NULL be converted to?
The string consisting of the four characters N U L L.
QUOTE() is supposed to produce values similar to what you get with the DBI quote() function.
And if the value should be NULL? 'NULL' won't do, then. I believe $dbh->quote(UNDEF) will return NULL, not 'NULL'.
It returns a string consisting of the four characters N U L L.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
