Dyntex.net Support Centre: MySQL Reference Manual p.42

<< previous page -- table of contents -- next page >>

Chapter_1

Chapter_2

Chapter_3

Chapter_4

Chapter_5

Return to Table of Contents

Table of Contents
1. General Information
2. MySQL Installation
3. Tutorial Introduction
4. Database Administration
5. MySQL Optimisation
6. MySQL Language Reference
7. MySQL Table Types
8. MySQL APIs
9. Extending MySQL

42 MySQL Technical Reference for Version 4.0.3 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 dierently 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 dier). 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 transac- tions, and thus can't rollback data, some things behave a little dierently 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 behaviour 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.

MySQL Reference Manual

Our Partners

SpiritHost - web hosting for spiritual and education sites
SpiritHit.com - Religious and Spiritual Portal

Return to Table of Contents	Back to top

Web Hosting: Manuals & FAQ's

1. Unix-Based Web Hosting
2. Unix Dedicated Servers
3. Windows Dedicated Servers
4. CuteFTP User’s Guide
5. CuteHTML User’s Guide
6. WS_FTP Pro User's Guide
7. Miva Order User's Guide
8. Miva Merchant User's Guide