This section describes how MySQL relates to the ANSI SQL standards. MySQL server has many extensions to the ANSI SQL standards, and here you will find out what they are and how to use them. You will also find information about functionality missing from MySQL server, and how to work around some differences.
Our goal is to not, without a very good reason, restrict MySQL server usability for any usage. Even if we don’t have the resources to do development for every possible use, we are always willing to help and offer suggestions to people who are trying to use MySQL server in new territories.
One of our main goals with the product is to continue to work toward ANSI 99 compliancy, but without sacrificing speed or reliability. We are not afraid to add extensions to SQL or support for non-SQL features if this greatly increases the usability of MySQL server for a big part of our users. (The new HANDLER interface in MySQL server 4.0 is an example of this strategy. See Section 6.4.2.)
We will continue to support transactional and non-transactional databases to satisfy both heavy web/logging usage and mission-critical 24/7 usage.
MySQL server was designed from the start to work with medium size databases (10-100 million rows, or about 100M per table) on small computer systems. We will continue to extend MySQL server to work even better with terabyte-size databases, as well as to make it possible to compile a reduced MySQL version that is more suitable for hand-held devices and embedded usage. The compact design of the MySQL server makes both of these directions possible without any conflicts in the source tree.
We are currently not targeting realtime support or clustered databases (even if you can already do a lot of things with our replication services).
We don’t believe that one should have native XML support in the database, but will instead add the XML support our users request from us on the client side. We think it’s better to keep the main server code as “lean and clean” as possible and instead develop libraries to deal with the complexity on the client side. This is part of the strategy mentioned previously of not sacrificing speed or reliability in the server.
Entry-level SQL92. ODBC levels 0-3.51.
We are aiming toward supporting the full ANSI SQL99 standard, but without concessions to speed and quality of the code.
If you start mysqld with the --ansi option, the following behavior of MySQL server changes:
|| is string concatenation instead of OR.
You can have any number of spaces between a function name and the
(. This forces all function names to be treated as reserved words." will be an identifier quote character (like the MySQL server
`quote character) and not a string quote character.REAL will be a synonym for FLOAT instead of a synonym for DOUBLE.
The default transaction isolation level is SERIALIZABLE. See Section 6.7.3.
This is the same as using --sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES, IGNORE_SPACE,SERIALIZE,ONLY_FULL_GROUP_BY.
MySQL server includes some extensions that you probably will not find in other SQL databases. Be warned that if you use them, your code will not be portable to other SQL servers. In some cases, you can write code that includes MySQL extensions, but is still portable, by using comments of the form /*! ... */. In this case, MySQL server will parse and execute the code within the comment as it would any other MySQL statement, but other SQL servers will ignore the extensions. For example:
SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ...
If you add a version number after the '!', the syntax will be executed only if the MySQL version is equal to or newer than the used version number:
CREATE /*!32302 TEMPORARY */ TABLE (a int);
This means that if you have Version 3.23.02 or newer, MySQL Server will use the TEMPORARY keyword.
The following is a list of MySQL extensions:
The field types MEDIUMINT, SET, ENUM, and the different BLOB and TEXT types.
The field attributes AUTO_INCREMENT, BINARY, NULL, UNSIGNED, and ZEROFILL.
All string comparisons are case-insensitive by default, with sort ordering determined by the current character set (ISO-8859-1 Latin1 by default). If you don’t like this, you should declare your columns with the BINARY attribute or use the BINARY cast, which causes comparisons to be done according to the ASCII order used on the MySQL server host.
MySQL server maps each database to a directory under the MySQL data directory, and tables within a database to filenames in the database directory.
This has a few implications:
Database names and table names are case-sensitive in MySQL server on operating systems that have case-sensitive filenames (like most Unix systems). See Section 6.1.3.
Database, table, index, column, or alias names may begin with a digit (but may not consist solely of digits).
You can use standard system commands to back up, rename, move, delete, and copy tables. For example, to rename a table, rename the
.MYD,.MYI, and.frmfiles to which the table corresponds.
In SQL statements, you can access tables from different databases with the db_name.tbl_name syntax. Some SQL servers provide the same functionality but call this User space. MySQL server doesn’t support tablespaces as in: create table ralph.my_table...IN my_tablespace.
LIKE is allowed on numeric columns.
Use of INTO OUTFILE and STRAIGHT_JOIN in a SELECT statement. See Section 6.4.1.
The SQL_SMALL_RESULT option in a SELECT statement.
EXPLAIN SELECT to get a description on how tables are joined.
Use of index names, indexes on a prefix of a field, and use of INDEX or KEY in a CREATE TABLE statement. See Section 6.5.3.
Use of TEMPORARY or IF NOT EXISTS with CREATE TABLE.
Use of COUNT(DISTINCT list) where list is more than one element.
Use of CHANGE col_name, DROP col_name, or DROP INDEX, IGNORE or RENAME in an ALTER TABLE statement. See Section 6.5.4.
Use of RENAME TABLE. See Section 6.5.5.
Use of multiple ADD, ALTER, DROP, or CHANGE clauses in an ALTER TABLE statement.
Use of DROP TABLE with the keywords IF EXISTS.
You can drop multiple tables with a single DROP TABLE statement.
The LIMIT clause of the DELETE statement.
The DELAYED clause of the INSERT and REPLACE statements.
The LOW_PRIORITY clause of the INSERT, REPLACE, DELETE, and UPDATE statements.
Use of LOAD DATA INFILE. In many cases, this syntax is compatible with Oracle’s LOAD DATA INFILE. See Section 6.4.9.
The ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements.
The SHOW statement. See Section 4.5.6.
Strings may be enclosed by either " or ', not just by ‘.
Use of the escape
\character.The SET OPTION statement. See Section 5.5.6.
You don’t need to name all selected columns in the GROUP BY part. This gives better performance for some very specific, but quite normal queries. See Section 6.3.7.
One can specify ASC and DESC with GROUP BY.
To make it easier for users who come from other SQL environments, MySQL server supports aliases for many functions. For example, all string functions support both ANSI SQL syntax and ODBC syntax.
MySQL server understands the || and && operators to mean logical OR and AND, as in the C programming language. In MySQL server, || and OR are synonyms, as are && and AND. Because of this nice syntax, MySQL server doesn’t support the ANSI SQL || operator for string concatenation; use CONCAT( ) instead. Because CONCAT( ) takes any number of arguments, it’s easy to convert use of the || operator to MySQL server.
CREATE DATABASE or DROP DATABASE. See Section 6.5.1.
The % operator is a synonym for MOD( ). That is, N % M is equivalent to MOD(N,M). % is supported for C programmers and for compatibility with PostgreSQL.
The =, <>, <= ,<, >=,>, <<, >>, <=>, AND, OR, or LIKE operators may be used in column comparisons to the left of the FROM in SELECT statements. For example:
mysql> SELECT col1=1 AND col2=2 FROM tbl_name;
The LAST_INSERT_ID( ) function. See Section 8.4.3.126.
The REGEXP and NOT REGEXP extended regular expression operators.
CONCAT( ) or CHAR( ) with one argument or more than two arguments. (In MySQL server, these functions can take any number of arguments.)
The BIT_COUNT( ), CASE, ELT( ), FROM_DAYS( ), FORMAT( ), IF( ), PASSWORD( ), ENCRYPT( ), MD5( ), ENCODE( ), DECODE( ), PERIOD_ADD( ), PERIOD_DIFF( ), TO_DAYS( ), or WEEKDAY( ) functions.
Use of TRIM( ) to trim substrings. ANSI SQL only supports removal of single characters.
The GROUP BY functions STD( ), BIT_OR( ), and BIT_AND( ).
Use of REPLACE instead of DELETE + INSERT. See Section 6.4.8.
The FLUSH, RESET and DO statements.
The ability to set variables in a statement with :=:
SELECT @a:=SUM(total),@b=COUNT(*),@a/@b AS avg FROM test_table; SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
We try to make MySQL server follow the ANSI SQL standard and the ODBC SQL standard, but in some cases MySQL server does things differently:
For VARCHAR columns, trailing spaces are removed when the value is stored. See Section 1.7.5.
In some cases, CHAR columns are silently changed to VARCHAR columns. See Section 6.5.3.1.
Privileges for a table are not automatically revoked when you delete a table. You must explicitly issue a REVOKE to revoke privileges for a table. See Section 4.3.1.
NULL AND FALSE will evaluate to NULL and not to FALSE. This is because we don’t think it’s good to have to evaluate a lot of extra conditions in this case.
For a prioritised list indicating when new extensions will be added to MySQL server, you should consult the online MySQL TODO list at http://www.mysql.com/documentation/manual.php?section=TODO. That is the latest version of the TODO list in this manual. See Section 1.8.
MySQL server currently only supports nested queries of the form INSERT ... SELECT ... and REPLACE ... SELECT .... You can, however, use the function IN( ) in other contexts. Sub-selects are scheduled for implementation in Version 4.x.
Meanwhile, you can often rewrite the query without a sub-select:
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
This can be rewritten as:
SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;
The queries:
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2
WHERE table1.id=table2.id);Can be rewritten as:
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;For more complicated subqueries you can often create temporary tables to hold the subquery. In some cases, however, this option will not work. The most frequently encountered of these cases arises with DELETE statements, for which standard SQL does not support joins (except in sub-selects). For this situation there are two options available until subqueries are supported by MySQL server.
The first option is to use a procedural programming language (such as Perl or PHP) to submit a SELECT query to obtain the primary keys for the records to be deleted, and then use these values to construct the DELETE statement (DELETE FROM ... WHERE ... IN (key1, key2, ...)).
The second option is to use interactive SQL to construct a set of DELETE statements automatically, using the MySQL extension CONCAT( ) (in lieu of the standard || operator). For example:
SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', tab1.pkid, ';')
FROM tab1, tab2
WHERE tab1.col1 = tab2.col2;You can place this query in a script file and redirect input from it to the mysql command-line interpreter, piping its output back to a second instance of the interpreter:
shell> mysql --skip-column-names mydb < myscript.sql | mysql mydb
MySQL server 4.0 supports multi-table deletes that can be used to efficiently delete rows based on information from one table or even from many tables at the same time.
MySQL server doesn’t yet support the Oracle SQL extension: SELECT ... INTO TABLE .... MySQL server supports instead the ANSI SQL syntax INSERT INTO ... SELECT ..., which is basically the same thing. See Section 6.4.3.1.
INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID
FROM tblTemp1 WHERE tblTemp1.fldOrder_ID > 100;Alternatively, you can use SELECT INTO OUTFILE... or CREATE TABLE ... SELECT.
MySQL server supports transactions with the InnoDB and BDB Transactional table handlers. See Chapter 7. InnoDB provides ACID compliancy.
However, the non-transactional table types in MySQL server such as MyISAM follow another paradigm for data integrity called "Atomic Operations.” Atomic operations often offer equal or even better integrity with much better performance. With MySQL server supporting both paradigms, the user is able to decide if he needs the speed of atomic operations or if he need to use transactional features in his applications. This choice can be made on a per-table basis.
How does one use the features of MySQL server to maintain rigorous integrity and how do these features compare with the transactional paradigm?
In the transactional paradigm, if your applications are written in a way that is dependent on the calling of ROLLBACK instead of COMMIT in critical situations, transactions are more convenient. Transactions also ensure that unfinished updates or corrupting activities are not committed to the database; the server is given the opportunity to do an automatic rollback and your database is saved.
MySQL server, in almost all cases, allows you to resolve potential problems by including simple checks before updates and by running simple scripts that check the databases for inconsistencies and automatically repair or warn if such an inconsistency occurs. Note that just by using the MySQL log or even adding one extra log, one can normally fix tables perfectly with no data integrity loss.
More often than not, fatal transactional updates can be rewritten to be atomic. Generally speaking, all integrity problems that transactions solve can be done with LOCK TABLES or atomic updates, ensuring that you never will get an automatic abort from the database, which is a common problem with transactional databases.
Even a transactional system can lose data if the server goes down. The difference between different systems lies in just how small the time-lap is where they could lose data. No system is 100% secure, only “secure enough.” Even Oracle, reputed to be the safest of transactional databases, is reported to sometimes lose data in such situations.
To be safe with MySQL server, whether using transactional tables or not, you only need to have backups and have the update logging turned on. With this you can recover from any situation that you could with any other transactional database. It is, of course, always good to have backups, independent of which database you use.
The transactional paradigm has its benefits and its drawbacks. Many users and application developers depend on the ease with which they can code around problems where an abort appears to be, or is necessary. However, even if you are new to the atomic operations paradigm, or more familiar with transactions, do consider the speed benefit that non-transactional tables can offer on the order of three to five times the speed of the fastest and most optimally tuned transactional tables.
In situations where integrity is of highest importance, MySQL server offers transaction-level or better reliability and integrity even for non-transactional tables. If you lock tables with LOCK TABLES, all updates will stall until any integrity checks are made. If you only obtain a read lock (as opposed to a write lock), reads and inserts are still allowed to happen. The new inserted records will not be seen by any of the clients that have a read lock until they release their read locks. With INSERT DELAYED you can queue inserts into a local queue, until the locks are released, without having the client wait for the insert to complete. See Section 6.4.4.
“Atomic,” in the sense that we mean it, is nothing magical. It only means that you can be sure that while each specific update is running, no other user can interfere with it, and there will never be an automatic rollback (which can happen with transactional tables if you are not very careful). MySQL server also guarantees that there will not be any dirty reads.
Following are some techniques for working with non-transactional tables:
Loops that need transactions normally can be coded with the help of LOCK TABLES, and you don’t need cursors when you can update records on the fly.
To avoid using ROLLBACK, you can use the following strategy:
Use LOCK TABLES ... to lock all the tables you want to access.
Test conditions.
Update if everything is okay.
Use UNLOCK TABLES to release your locks.
This is usually a much faster method than using transactions with possible ROLLBACKs, although not always. The only situation this solution doesn’t handle is when someone kills the threads in the middle of an update. In this case, all locks will be released but some of the updates may not have been executed.
You can also use functions to update records in a single operation. You can get a very efficient application by using the following techniques:
Modify fields relative to their current value.
Update only those fields that actually have changed.
For example, when we are doing updates to some customer information, we update only the customer data that has changed and test only that none of the changed data, or data that depends on the changed data, has changed compared to the original row. The test for changed data is done with the WHERE clause in the UPDATE statement. If the record wasn’t updated, we give the client a message: “Some of the data you have changed has been changed by another user.” Then we show the old row versus the new row in a window, so the user can decide which version of the customer record he should use.
This gives us something that is similar to column locking but is actually even better because we only update some of the columns, using values that are relative to their current values. This means that typical UPDATE statements look something like these:
UPDATE tablename SET pay_back=pay_back+'relative change'; UPDATE customer SET customer_date='current_date', address='new address', phone='new phone', money_he_owes_us=money_he_owes_us+'new_money' WHERE customer_id=id AND address='old address' AND phone='old phone';As you can see, this is very efficient and works even if another client has changed the values in the pay_back or money_he_owes_us columns.
In many cases, users have wanted ROLLBACK and/or LOCK TABLES for the purpose of managing unique identifiers for some tables. This can be handled much more efficiently by using an AUTO_INCREMENT column and either the SQL function LAST_INSERT_ID( ) or the C API function mysql_insert_id( ). See Section 8.4.3.126.
You can generally code around row-level locking. Some situations really need it, but they are very few. InnoDB tables support row-level locking. With MyISAM, you can use a flag column in the table and do something like the following:
UPDATE tbl_name SET row_flag=1 WHERE id=ID;
MySQL returns 1 for the number of affected rows if the row was found and row_flag wasn’t already 1 in the original row.
You can think of it as though MySQL server changed the preceding query to:
UPDATE tbl_name SET row_flag=1 WHERE id=ID AND row_flag <> 1;
A stored procedure is a set of SQL commands that can be compiled and stored in the server. Once this has been done, clients don’t need to keep re-issuing the entire query but can refer to the stored procedure. This provides better performance because the query has to be parsed only once, and less information needs to be sent between the server and the client. You can also raise the conceptual level by having libraries of functions in the server.
A trigger is a stored procedure that is invoked when a particular event occurs. For example, you can install a stored procedure that is triggered each time a record is deleted from a transaction table and that automatically deletes the corresponding customer from a customer table when all his transactions are deleted.
The planned update language will be able to handle stored procedures. Our aim is to have stored procedures implemented in MySQL server around version 4.1. We are also looking at triggers.
Note that foreign keys in SQL are not used to join tables, but are used mostly for checking referential integrity (foreign key constraints). If you want to get results from multiple tables from a SELECT statement, you do this by joining tables:
SELECT * FROM table1,table2 WHERE table1.id = table2.id;
Section 6.4.1.1. See Section 3.5.6.
In MySQL server 3.23.44 and up, InnoDB tables support checking of foreign key constraints. See Section 7.5. For other table types, MySQL server does parse the FOREIGN KEY syntax in CREATE TABLE commands, but without further action being taken.
The FOREIGN KEY syntax without ON DELETE ... is mostly used for documentation purposes. Some ODBC applications may use this to produce automatic WHERE clauses, but this is usually easy to override. FOREIGN KEY is sometimes used as a constraint check, but this check is unnecessary in practice if rows are inserted into the tables in the right order.
In MySQL server, you can work around the problem of ON DELETE ... not being implemented by adding the appropriate DELETE statement to an application when you delete records from a table that has a foreign key. In practice this is as quick (in some cases quicker) and much more portable than using foreign keys.
In MySQL server 4.0 you can use multi-table delete to delete rows from many tables with one command. See Section 6.4.6.
In the near future we will extend the FOREIGN KEY implementation so that the information will be saved in the table specification file and may be retrieved by mysqldump and ODBC. At a later stage we will implement the foreign key constraints for applications that can’t easily be coded to avoid them.
Do keep in mind that foreign keys are often misused, which can cause severe problems. Even when used properly, it is not a magic solution for the referential integrity problem, although it does make things easier in some cases.
Some advantages of foreign key enforcement:
Assuming proper design of the relations, foreign key constraints will make it more difficult for a programmer to introduce an inconsistency into the database.
Using cascading updates and deletes can simplify the client code.
Properly designed foreign key rules aid in documenting relations between tables.
Disadvantages:
Mistakes, which are easy to make in designing key relations, can cause severe problems—for example, circular rules, or the wrong combination of cascading deletes.
A properly written application will make sure internally that it is not violating referential integrity constraints before proceding with a query. Thus, additional checks on the database level will only slow down performance for such an application.
It is not uncommon for a DBA to make such a complex topology of relations that it becomes very difficult, and in some cases impossible, to back up or restore individual tables.
It is planned to implement views in MySQL server around version 4.1.
Views are mostly useful for letting users access a set of relations as one table (in read-only mode). Many SQL databases don’t allow one to update any rows in a view, but you have to do the updates in the separate tables.
As MySQL server is mostly used in applications and on web systems where the application writer has full control on the database usage, most of our users haven’t regarded views to be very important. (At least no one has been interested enough in this to be prepared to finance the implementation of views.)
One doesn’t need views in MySQL server to restrict access to columns, as MySQL server has a very sophisticated privilege system. See Section 4.2.
Some other SQL databases use -- to start comments.
MySQL server has # as the start comment character. You can also use
the C comment style /* this is a comment */ with MySQL server. See Section 6.1.5.
MySQL server Version 3.23.3 and above support the -- comment style,
provided the comment is followed by a space. This is because this
comment style has caused many problems with automatically generated
SQL queries that have used something like the following code, where
we automatically insert the value of the payment for
!payment!:
UPDATE tbl_name SET credit=credit-!payment!
Think about what happens if the value of payment is negative.
Because 1--1 is legal in SQL, the consequences of allowing
comments to start with -- are terrible.
Using our implementation of this method of commenting in MySQL server Version 3.23.3 and up, 1-- This is a comment is actually safe.
Another safe feature is that the mysql command-line client
removes all lines that start with --.
The following information is relevant only if you are running a MySQL version earlier than 3.23.3:
If you have a SQL program in a text file that contains --
comments you should use:
shell> replace " --" " #" < text-file-with-funny-comments.sql \
| mysql databaseinstead of the usual:
shell> mysql database < text-file-with-funny-comments.sql
You can also edit the command file “in place” to change the --
comments to # comments:
shell> replace " --" " #"—text-file-with-funny-comments.sql
Change them back with this command:
shell> replace " #" " --"—text-file-with-funny-comments.sql
The following problems are known and have a very high priority to get fixed:
ANALYZE TABLE on a BDB table may in some case make the table unusable until one has restarted mysqld. When this happens you will see errors like the following in the MySQL error file:
001207 22:07:56 bdb: log_flush: LSN past current end-of-log
Don’t execute ALTER TABLE on a BDB table on which you are running multi-statement transactions until all those transactions complete. (The transaction will probably be ignored.)
ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE may cause problems on tables for which you are using INSERT DELAYED.
Doing a LOCK TABLE ... and FLUSH TABLES ... doesn’t guarantee that there isn’t a half-finished transaction in progress on the table.
BDB tables are a bit slow to open. If you have many BDB tables in a database, it will take a long time to use the mysql client on the database if you are not using the -A option or if you are using rehash. This is especially notable when you have a big table cache.
The current replication protocol cannot deal with LOAD DATA INFILE and line terminator characters of more than 1 character.
The following problems are known and will be fixed in due time:
When using SET CHARACTER SET, one can’t use translated characters in database, table, and column names.
DELETE FROM merge_table used without a WHERE will only clear the mapping for the table, not delete everything in the mapped tables.
You cannot build the server in another directory when using MIT-pthreads. Because this requires changes to MIT-pthreads, we are not likely to fix this. See Section 2.3.6.
BLOB values can’t “reliably” be used in GROUP BY or ORDER BY or DISTINCT. Only the first max_sort_length bytes (default 1024) are used when comparing BLOBs in these cases. This can be changed with the -O max_sort_length option to mysqld. A workaround for most cases is to use a substring: SELECT DISTINCT LEFT(blob,2048) FROM tbl_name.
Calculation is done with BIGINT or DOUBLE (both are normally 64 bits long). It depends on the function which precision one gets. The general rule is that bit functions are done with BIGINT precision, IF, and ELT( ) with BIGINT or DOUBLE precision and the rest with DOUBLE precision. One should try to avoid using unsigned long long values if they resolve to be bigger than 63 bits (9223372036854775807) for anything else than bit fields! MySQL server 4.0 has better BIGINT handling than 3.23.
All string columns, except BLOB and TEXT columns, automatically have all trailing spaces removed when retrieved. For CHAR types this is okay, and may be regarded as a feature according to ANSI SQL92. The bug is that in MySQL server, VARCHAR columns are treated the same way.
You can only have up to 255 ENUM and SET columns in one table.
safe_mysqld redirects all messages from mysqld to the mysqld log. One problem with this is that if you execute mysqladmin refresh to close and reopen the log, stdout and stderr are still redirected to the old log. If you use --log extensively, you should edit safe_mysqld to log to
'hostname'.errinstead of'hostname'.logso you can easily reclaim the space for the old log by deleting the old one and executing mysqladmin refresh.In the UPDATE statement, columns are updated from left to right. If you refer to an updated column, you will get the updated value instead of the original value. For example:
mysql> UPDATE tbl_name SET KEY=KEY+1,KEY=KEY+1;
This will update KEY with 2 instead of with 1.
You can’t use temporary tables more than once in the same query. For example, the following doesn’t work:
mysql> SELECT * FROM temporary_table, temporary_table AS t2;
RENAME doesn’t work with TEMPORARY tables or tables used in a MERGE table.
The optimiser may handle DISTINCT differently if you are using ‘hidden’ columns in a join or not. In a join, hidden columns are counted as part of the result (even if they are not shown) while in normal queries hidden columns don’t participate in the DISTINCT comparison. We will probably change this in the future to never compare the hidden columns when executing DISTINCT.
An example of this is:
SELECT DISTINCT mp3id FROM band_downloads WHERE userid = 9 ORDER BY id DESC;and
SELECT DISTINCT band_downloads.mp3id FROM band_downloads,band_mp3 WHERE band_downloads.userid = 9 AND band_mp3.id = band_downloads.mp3id ORDER BY band_downloads.id DESC;In the second case you may in MySQL server 3.23.x get two identical rows in the result set (because the hidden id column may differ).
Note that this happens only for queries where you don’t have the ORDER BY columns in the result, something that you are not allowed to do in ANSI SQL.
Because MySQL server allows you to work with table types that don’t support transactions, and thus can’t rollback data, some things behave a little differently in MySQL server than in other SQL servers. This is just to ensure that MySQL server never needs to do a rollback for a SQL command. This may be a little awkward at times as column values must be checked in the application, but this will actually give you a nice speed increase as it allows MySQL server to do some optimisations that otherwise would be very hard to do.
If you set a column to an incorrect value, MySQL server will, instead of doing a rollback, store the best possible value in the column:
If you try to store a value outside the range in a numerical column, MySQL server will instead store the smallest or biggest possible value in the column.
If you try to store a string that doesn’t start with a number into a numerical column, MySQL server will store 0 into it.
If you try to store NULL into a column that doesn’t take NULL values, MySQL server will store 0 or " (empty string) in it instead. (This behavior can, however, be changed with the -DDONT_USE_DEFAULT_FIELDS compile option.)
MySQL allows you to store some wrong date values into DATE and DATETIME columns (like 2000-02-31 or 2000-02-00). If the date is totally wrong, MySQL server will store the special 0000-00-00 date value in the column.
If you set an ENUM column to an unsupported value, it will be set to the error value empty string, with numeric value 0.
If you set a SET column to an unsupported value, the value will be ignored.
If you execute a PROCEDURE on a query that returns an empty set, in some cases the PROCEDURE will not transform the columns.
Creation of a table of type MERGE doesn’t check if the underlying tables are of compatible types.
MySQL server can’t yet handle NaN, -Inf, and Inf values in double. Using these will cause problems when trying to export and import data. We should as an intermediate solution change NaN to NULL (if possible) and -Inf and Inf to the minimum respective maximum possible double value.
LIMIT on negative numbers are treated as big positive numbers.
If you use ALTER TABLE to first add a UNIQUE index to a table used in a MERGE table and then use ALTER TABLE to add a normal index on the MERGE table, the key order will be different for the tables if there was an old key that was not unique in the table. This is because ALTER TABLE puts UNIQUE keys before normal keys to be able to detect duplicate keys as early as possible.
The following are known bugs in earlier versions of MySQL:
You can get a hung thread if you do a DROP TABLE on a table that is one among many tables that is locked with LOCK TABLES.
In the following case you can get a core dump:
Delayed insert handler has pending inserts to a table.
LOCK table with WRITE.
FLUSH TABLES.
Before MySQL server Version 3.23.2 an UPDATE that updated a key with a WHERE on the same key may have failed because the key was used to search for records and the same row may have been found multiple times:
UPDATE tbl_name SET KEY=KEY+1 WHERE KEY > 100;
A workaround is to use:
mysql> UPDATE tbl_name SET KEY=KEY+1 WHERE KEY+0 > 100;
This will work because MySQL server will not use an index on expressions in the WHERE clause.
Before MySQL server Version 3.23, all numeric types where treated as fixed-point fields. That means you had to specify how many decimals a floating-point field shall have. All results were returned with the correct number of decimals.
For platform-specific bugs, see the sections about compiling and porting.
Get MySQL Reference Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
