![]() |
|
[ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top] | [Contents] | [Index] | [ ? ] |
By default, MySQL searches are case-insensitive (although there are
some character sets that are never case-insensitive, such as czech
).
That means that if you search with col_name LIKE 'a%'
, you will get all
column values that start with A
or a
. If you want to make this
search case-sensitive, use something like INSTR(col_name, "A")=1
to
check a prefix. Or use STRCMP(col_name, "A") = 0
if the column value
must be exactly "A"
.
Simple comparison operations (>=, >, = , < , <=
, sorting and
grouping) are based on each character's "sort value". Characters with
the same sort value (like E, e and �) are treated as the same character!
In older MySQL versions LIKE
comparisons were done on
the uppercase value of each character (E == e but E <> �). In newer
MySQL versions LIKE
works just like the other comparison
operators.
If you want a column always to be treated in case-sensitive fashion,
declare it as BINARY
. See section CREATE TABLE
.
If you are using Chinese data in the so-called big5 encoding, you want to
make all character columns BINARY
. This works because the sorting
order of big5 encoding characters is based on the order of ASCII codes.
DATE
Columns The format of a DATE
value is 'YYYY-MM-DD'
. According to
standard SQL, no other format is allowed. You should use this format in UPDATE
expressions and in the WHERE clause of SELECT
statements. For
example:
mysql> SELECT * FROM tbl_name WHERE date >= '1997-05-05'; |
As a convenience, MySQL automatically converts a date to a number if
the date is used in a numeric context (and vice versa). It is also smart
enough to allow a "relaxed" string form when updating and in a WHERE
clause that compares a date to a TIMESTAMP
, DATE
, or a
DATETIME
column. (Relaxed form means that any punctuation character
may be used as the separator between parts. For example, '1998-08-15'
and '1998#08#15'
are equivalent.) MySQL can also convert a
string containing no separators (such as '19980815'
), provided it
makes sense as a date.
The special date '0000-00-00'
can be stored and retrieved as
'0000-00-00'.
When using a '0000-00-00'
date through
MyODBC
, it will automatically be converted to NULL
in
MyODBC
Version 2.50.12 and above, because ODBC can't handle this kind of
date.
Because MySQL performs the conversions described above, the following statements work:
mysql> INSERT INTO tbl_name (idate) VALUES (19970505); mysql> INSERT INTO tbl_name (idate) VALUES ('19970505'); mysql> INSERT INTO tbl_name (idate) VALUES ('97-05-05'); mysql> INSERT INTO tbl_name (idate) VALUES ('1997.05.05'); mysql> INSERT INTO tbl_name (idate) VALUES ('1997 05 05'); mysql> INSERT INTO tbl_name (idate) VALUES ('0000-00-00'); mysql> SELECT idate FROM tbl_name WHERE idate >= '1997-05-05'; mysql> SELECT idate FROM tbl_name WHERE idate >= 19970505; mysql> SELECT MOD(idate,100) FROM tbl_name WHERE idate >= 19970505; mysql> SELECT idate FROM tbl_name WHERE idate >= '19970505'; |
However, the following will not work:
mysql> SELECT idate FROM tbl_name WHERE STRCMP(idate,'19970505')=0; |
STRCMP()
is a string function, so it converts idate
to
a string and performs a string comparison. It does not convert
'19970505'
to a date and perform a date comparison.
Note that MySQL does very limited checking whether the date is
correct. If you store an incorrect date, such as '1998-2-31'
, the
wrong date will be stored.
Because MySQL packs dates for storage, it can't store any given date as it would not fit onto the result buffer. The rules for accepting a date are:
DATE
and DATETIME
columns.
DATE
column and you only know part
of the date.
If the date cannot be converted to any reasonable value, a 0
is
stored in the DATE
field, which will be retrieved as
0000-00-00
. This is both a speed and convenience issue as we
believe that the database's responsibility is to retrieve the same date
you stored (even if the data was not logically correct in all cases).
We think it is up to the application to check the dates, and not the server.
NULL
Values The concept of the NULL
value is a common source of confusion for
newcomers to SQL, who often think that NULL
is the same thing as an
empty string ""
. This is not the case! For example, the following
statements are completely different:
mysql> INSERT INTO my_table (phone) VALUES (NULL); mysql> INSERT INTO my_table (phone) VALUES (""); |
Both statements insert a value into the phone
column, but the first
inserts a NULL
value and the second inserts an empty string. The
meaning of the first can be regarded as "phone number is not known" and the
meaning of the second can be regarded as "she has no phone".
In SQL, the NULL
value is always false in comparison to any
other value, even NULL
. An expression that contains NULL
always produces a NULL
value unless otherwise indicated in
the documentation for the operators and functions involved in the
expression. All columns in the following example return NULL
:
mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL); |
If you want to search for column values that are NULL
, you
cannot use the =NULL
test. The following statement returns no
rows, because expr = NULL
is FALSE, for any expression:
mysql> SELECT * FROM my_table WHERE phone = NULL; |
To look for NULL
values, you must use the IS NULL
test.
The following shows how to find the NULL
phone number and the
empty phone number:
mysql> SELECT * FROM my_table WHERE phone IS NULL; mysql> SELECT * FROM my_table WHERE phone = ""; |
Note that you can only add an index on a column that can have NULL
values if you are using MySQL Version 3.23.2 or newer and are using the
MyISAM
or InnoDB
table type.
In earlier versions and with other table types, you must declare such
columns NOT NULL
. This also means you cannot then insert
NULL
into an indexed column.
When reading data with LOAD DATA INFILE
, empty columns are updated
with ''
. If you want a NULL
value in a column, you should use
\N
in the text file. The literal word 'NULL'
may also be used
under some circumstances.
See section LOAD DATA
.
When using ORDER BY
, NULL
values are presented first, or
last if you specify DESC
to sort in descending order. Exception:
In MySQL versions 4.0.2 through 4.0.10, NULL
values sort first
regardless of sort order.
When using GROUP BY
, all NULL
values are regarded as equal.
Aggregate (summary) functions such as COUNT()
, MIN()
, and
SUM()
ignore NULL
values. The exception to this is
COUNT(*)
, which counts rows and not individual column values.
For example, the following statement would produce two counts.
The first is a count of the number of rows in the table, and the second
is a count of the number of non-NULL
values in the age
column:
mysql> SELECT COUNT(*), COUNT(age) FROM person; |
To help with NULL
handling, you can use the IS NULL
and
IS NOT NULL
operators and the IFNULL()
function.
For some column types, NULL
values are handled specially. If you
insert NULL
into the first TIMESTAMP
column of a table, the
current date and time is inserted. If you insert NULL
into an
AUTO_INCREMENT
column, the next number in the sequence is inserted.
alias
You can use an alias to refer to a column in the GROUP BY
,
ORDER BY
, or in the HAVING
part. Aliases can also be used
to give columns better names:
SELECT SQRT(a*b) as rt FROM table_name GROUP BY rt HAVING rt > 0; SELECT id,COUNT(*) AS cnt FROM table_name GROUP BY id HAVING cnt > 0; SELECT id AS "Customer identity" FROM table_name; |
Note that standard SQL doesn't allow you to refer to an alias in a
WHERE
clause. This is because when the WHERE
code is
executed the column value may not yet be determined. For example, the
following query is illegal:
SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0 GROUP BY id; |
The WHERE
statement is executed to determine which rows should
be included in the GROUP BY
part while HAVING
is used to
decide which rows from the result set should be used.
As MySQL doesn't support subqueries (prior to Version 4.1), nor the use of more
than one table in the DELETE
statement (prior to Version 4.0), you
should use the following approach to delete rows from 2 related tables:
SELECT
the rows based on some WHERE
condition in the main table.
DELETE
the rows in the main table based on the same condition.
DELETE FROM related_table WHERE related_column IN (selected_rows)
.
If the total number of characters in the query with
related_column
is more than 1,048,576 (the default value of
max_allowed_packet
, you should split it into smaller parts and
execute multiple DELETE
statements. You will probably get the
fastest DELETE
by only deleting 100-1000 related_column
ids per query if the related_column
is an index. If the
related_column
isn't an index, the speed is independent of the
number of arguments in the IN
clause.
If you have a complicated query that has many tables and that doesn't return any rows, you should use the following procedure to find out what is wrong with your query:
EXPLAIN
and check if you can find something that is
obviously wrong. See section EXPLAIN
.
WHERE
clause.
LIMIT 10
with the query.
SELECT
for the column that should have matched a row against
the table that was last removed from the query.
FLOAT
or DOUBLE
columns with numbers that
have decimals, you can't use '='
. This problem is common in most
computer languages because floating-point values are not exact values.
In most cases, changing the FLOAT
to a DOUBLE
will fix this.
See section Problems with Floating-Point Comparison.
mysql test < query.sql
that shows your problems.
You can create a test file with mysqldump --quick database tables > query.sql
. Open the file in an editor, remove some insert lines (if there are
too many of these), and add your select statement at the end of the file.
Test that you still have your problem by doing:
shell> mysqladmin create test2 shell> mysql test2 < query.sql |
Post the test file using mysqlbug
to the general MySQL mailing list.
See section The MySQL Mailing Lists.
floating-point numbers cause confusion sometimes, because these numbers are not stored as exact values inside computer architecture. What one can see on the screen usually is not the exact value of the number.
Field types FLOAT
, DOUBLE
and DECIMAL
are such.
CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2)); INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00), (2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40), (2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00), (4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00), (5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20), (6, 0.00, 0.00), (6, -51.40, 0.00); mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b -> FROM t1 GROUP BY i HAVING a <> b; +------+--------+-------+ | i | a | b | +------+--------+-------+ | 1 | 21.40 | 21.40 | | 2 | 76.80 | 76.80 | | 3 | 7.40 | 7.40 | | 4 | 15.40 | 15.40 | | 5 | 7.20 | 7.20 | | 6 | -51.40 | 0.00 | +------+--------+-------+ |
The result is correct. Although the first five records look like they shouldn't pass the comparison test, they may do so because the difference between the numbers show up around tenth decimal, or so depending on computer architecture.
The problem cannot be solved by using ROUND() (or similar function), because the result is still a floating-point number. Example:
mysql> SELECT i, ROUND(SUM(d1), 2) AS a, ROUND(SUM(d2), 2) AS b -> FROM t1 GROUP BY i HAVING a <> b; +------+--------+-------+ | i | a | b | +------+--------+-------+ | 1 | 21.40 | 21.40 | | 2 | 76.80 | 76.80 | | 3 | 7.40 | 7.40 | | 4 | 15.40 | 15.40 | | 5 | 7.20 | 7.20 | | 6 | -51.40 | 0.00 | +------+--------+-------+ |
This is what the numbers in column 'a' look like:
mysql> SELECT i, ROUND(SUM(d1), 2)*1.0000000000000000 AS a, -> ROUND(SUM(d2), 2) AS b FROM t1 GROUP BY i HAVING a <> b; +------+----------------------+-------+ | i | a | b | +------+----------------------+-------+ | 1 | 21.3999999999999986 | 21.40 | | 2 | 76.7999999999999972 | 76.80 | | 3 | 7.4000000000000004 | 7.40 | | 4 | 15.4000000000000004 | 15.40 | | 5 | 7.2000000000000002 | 7.20 | | 6 | -51.3999999999999986 | 0.00 | +------+----------------------+-------+ |
Depending on the computer architecture you may or may not see similar results. Each CPU may evaluate floating-point numbers differently. For example in some machines you may get 'right' results by multiplying both arguments with 1, an example follows.
WARNING: NEVER TRUST THIS METHOD IN YOUR APPLICATION, THIS IS AN EXAMPLE OF A WRONG METHOD!!!
mysql> SELECT i, ROUND(SUM(d1), 2)*1 AS a, ROUND(SUM(d2), 2)*1 AS b -> FROM t1 GROUP BY i HAVING a <> b; +------+--------+------+ | i | a | b | +------+--------+------+ | 6 | -51.40 | 0.00 | +------+--------+------+ |
The reason why the above example seems to be working is that on the particular machine where the test was done, the CPU floating-point arithmetics happens to round the numbers to same, but there is no rule that any CPU should do so, so it cannot be trusted.
The correct way to do floating-point number comparison is to first decide on what is the wanted tolerance between the numbers and then do the comparison against the tolerance number. For example, if we agree on that floating-point numbers should be regarded the same, if they are same with precision of one of ten thousand (0.0001), the comparison should be done like this:
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1 -> GROUP BY i HAVING ABS(a - b) > 0.0001; +------+--------+------+ | i | a | b | +------+--------+------+ | 6 | -51.40 | 0.00 | +------+--------+------+ 1 row in set (0.00 sec) |
And vice versa, if we wanted to get rows where the numbers are the same, the test would be:
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1 -> GROUP BY i HAVING ABS(a - b) < 0.0001; +------+-------+-------+ | i | a | b | +------+-------+-------+ | 1 | 21.40 | 21.40 | | 2 | 76.80 | 76.80 | | 3 | 7.40 | 7.40 | | 4 | 15.40 | 15.40 | | 5 | 7.20 | 7.20 | +------+-------+-------+ |
[ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] |
Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:44