Oracle 5.0 User manual

MySQL 5.0 Reference Manual

MySQL 5.0 Reference Manual
Abstract
This is the MySQL™ Reference Manual. It documents MySQL 5.0 through 5.0.96.
End of Product Lifecycle
Active development for MySQL Database Server version 5.0 has ended. Oracle offers various support
offerings which may be of interest. For details and more information, see the MySQL section of the
Lifetime Support Policy for Oracle Technology Products (http://www.oracle.com/us/support/lifetime-
support/index.html). Please consider upgrading to a recent version.
MySQL 5.0 features. This manual describes features that are not included in every edition of MySQL 5.0 and
such features may not be included in the edition of MySQL 5.0 licensed to you. If you have any questions about
the features included in your edition of MySQL 5.0, refer to your MySQL 5.0 license agreement or contact your
Oracle representative.
For release notes detailing the changes in each release, see the MySQL 5.0 Release Notes.
For legal information, see the Legal Notices.
Document generated on: 2013-04-16 (revision: 34918)
General Administrators MySQL
Enterprise Developers & Functionality Connectors & APIs HA/Scalability
Tutorial Installation &
Upgrades MySQL Enterprise Edition MySQL Workbench Connectors and APIs » HA/Scalability Guide
Server
Administration » MySQL Installer MySQL Enterprise
Monitor
Globalization Connector/J MySQL and
DRBD
SQL Syntax » Security MySQL
Enterprise
Backup
Optimization Connector/ODBC memcached
Storage Engines » Startup /
Shutdown MySQL Enterprise
Security
Functions and
Operators Connector/Net MySQL and Virtualization
Server Option /
Variable
Reference
» Backup and
Recovery
Overview
MySQL
Enterprise Audit Views and Stored Programs Connector/ Python MySQL Proxy
» Release Notes » MySQL Utilities MySQL Thread
Pool Precision Math PHP Replication
» MySQL Version
Reference » Linux/Unix Platform Guide Spatial Extensions C API
FAQs » Windows
Platform Guide Restrictions and Limitations Connector/C
» Mac OS X
Platform Guide Connector/C++
» Solaris Platform
Guide » MySQL for Excel
» Building from
Source

iii
Table of Contents
Preface and Legal Notices .......................................................................................................... xix
1. General Information .................................................................................................................. 1
1.1. About This Manual ......................................................................................................... 2
1.2. Typographical and Syntax Conventions ........................................................................... 3
1.3. Overview of the MySQL Database Management System .................................................. 4
1.3.1. What is MySQL? ................................................................................................. 4
1.3.2. The Main Features of MySQL .............................................................................. 6
1.3.3. History of MySQL ................................................................................................ 8
1.4. What Is New in MySQL 5.0 ............................................................................................ 9
1.5. MySQL Development History ........................................................................................ 11
1.6. MySQL Information Sources ......................................................................................... 11
1.6.1. MySQL Mailing Lists .......................................................................................... 11
1.6.2. MySQL Community Support at the MySQL Forums ............................................. 14
1.6.3. MySQL Community Support on Internet Relay Chat (IRC) ................................... 14
1.6.4. MySQL Enterprise ............................................................................................. 14
1.7. How to Report Bugs or Problems .................................................................................. 14
1.8. MySQL Standards Compliance ..................................................................................... 19
1.8.1. What Standards MySQL Follows ........................................................................ 19
1.8.2. Selecting SQL Modes ........................................................................................ 19
1.8.3. Running MySQL in ANSI Mode .......................................................................... 20
1.8.4. MySQL Extensions to Standard SQL .................................................................. 20
1.8.5. MySQL Differences from Standard SQL ............................................................. 23
1.8.6. How MySQL Deals with Constraints ................................................................... 28
1.9. Credits ......................................................................................................................... 32
1.9.1. Contributors to MySQL ...................................................................................... 32
1.9.2. Documenters and translators ............................................................................. 36
1.9.3. Packages that support MySQL ........................................................................... 37
1.9.4. Tools that were used to create MySQL ............................................................... 38
1.9.5. Supporters of MySQL ........................................................................................ 38
2. Installing and Upgrading MySQL ............................................................................................. 41
2.1. MySQL Installation Overview ........................................................................................ 42
2.2. Determining your current MySQL version ...................................................................... 42
2.3. Notes for MySQL Enterprise Server .............................................................................. 43
2.3.1. Enterprise Server Distribution Types ................................................................... 44
2.3.2. Upgrading MySQL Enterprise Server .................................................................. 44
2.4. Notes for MySQL Community Server ............................................................................. 44
2.4.1. Overview of MySQL Community Server Installation ............................................. 44
2.4.2. Operating Systems Supported by MySQL Community Server ............................... 45
2.4.3. Choosing Which MySQL Distribution to Install ..................................................... 46
2.5. How to Get MySQL ...................................................................................................... 50
2.6. Verifying Package Integrity Using MD5 Checksums or GnuPG ......................................... 50
2.6.1. Verifying the MD5 Checksum ............................................................................. 50
2.6.2. Signature Checking Using GnuPG ....................................................................... 51
2.6.3. Signature Checking Using Gpg4win for Windows ............................................... 53
2.6.4. Signature Checking Using RPM ........................................................................... 57
2.7. Installation Layouts ....................................................................................................... 58
2.8. Compiler-Specific Build Characteristics .......................................................................... 59
2.9. Standard MySQL Installation from a Binary Distribution .................................................. 60
2.10. Installing MySQL on Microsoft Windows ...................................................................... 60
2.10.1. Choosing An Installation Package .................................................................... 61
2.10.2. Installing MySQL on Microsoft Windows Using an MSI Package ......................... 62
2.10.3. MySQL Server Instance Configuration Wizard ................................................... 67
2.10.4. Installing MySQL on Microsoft Windows Using a noinstall Zip Archive ........... 79
2.10.5. Troubleshooting a MySQL Installation Under Windows ...................................... 86
2.10.6. Upgrading MySQL on Windows ........................................................................ 87

MySQL 5.0 Reference Manual
iv
2.10.7. Windows Postinstallation Procedures ................................................................ 88
2.10.8. Installing MySQL from Source on Windows ....................................................... 90
2.11. Installing MySQL on Mac OS X .................................................................................. 95
2.12. Installing MySQL from RPM Packages on Linux .......................................................... 97
2.13. Installing MySQL on Solaris ...................................................................................... 100
2.14. Installing MySQL on i5/OS ........................................................................................ 101
2.15. Installing MySQL on NetWare ................................................................................... 105
2.16. Installing MySQL from Generic Binaries on Other Unix-Like Systems ........................... 107
2.17. Installing MySQL from Source ................................................................................... 109
2.17.1. Installing MySQL from a Standard Source Distribution ..................................... 110
2.17.2. Installing MySQL from a Development Source Tree ......................................... 113
2.17.3. MySQL Source-Configuration Options ............................................................. 115
2.17.4. Dealing with Problems Compiling MySQL ....................................................... 123
2.17.5. Compiling and Linking an Optimized mysqld Server ....................................... 126
2.18. Postinstallation Setup and Testing ............................................................................. 127
2.18.1. Unix Postinstallation Procedures ..................................................................... 127
2.18.2. Securing the Initial MySQL Accounts .............................................................. 138
2.19. Upgrading or Downgrading MySQL ........................................................................... 142
2.19.1. Upgrading MySQL ......................................................................................... 142
2.19.2. Downgrading MySQL ..................................................................................... 153
2.19.3. Checking Whether Tables or Indexes Must Be Rebuilt ..................................... 154
2.19.4. Rebuilding or Repairing Tables or Indexes ...................................................... 156
2.19.5. Copying MySQL Databases to Another Machine ............................................. 158
2.20. Operating System-Specific Notes .............................................................................. 159
2.20.1. Linux Notes ................................................................................................... 159
2.20.2. Mac OS X Notes ........................................................................................... 165
2.20.3. Solaris Notes ................................................................................................. 165
2.20.4. BSD Notes .................................................................................................... 169
2.20.5. Other Unix Notes ........................................................................................... 172
2.20.6. OS/2 Notes ................................................................................................... 187
2.21. Environment Variables .............................................................................................. 187
2.22. Perl Installation Notes ............................................................................................... 188
2.22.1. Installing Perl on Unix .................................................................................... 189
2.22.2. Installing ActiveState Perl on Windows ........................................................... 190
2.22.3. Problems Using the Perl DBI/DBD Interface ..................................................... 190
3. Tutorial ................................................................................................................................. 193
3.1. Connecting to and Disconnecting from the Server ........................................................ 193
3.2. Entering Queries ........................................................................................................ 194
3.3. Creating and Using a Database .................................................................................. 197
3.3.1. Creating and Selecting a Database .................................................................. 198
3.3.2. Creating a Table .............................................................................................. 199
3.3.3. Loading Data into a Table ................................................................................ 200
3.3.4. Retrieving Information from a Table .................................................................. 201
3.4. Getting Information About Databases and Tables ......................................................... 213
3.5. Using mysql in Batch Mode ....................................................................................... 214
3.6. Examples of Common Queries .................................................................................... 216
3.6.1. The Maximum Value for a Column ................................................................... 216
3.6.2. The Row Holding the Maximum of a Certain Column ......................................... 216
3.6.3. Maximum of Column per Group ....................................................................... 217
3.6.4. The Rows Holding the Group-wise Maximum of a Certain Column ...................... 217
3.6.5. Using User-Defined Variables ........................................................................... 218
3.6.6. Using Foreign Keys ......................................................................................... 218
3.6.7. Searching on Two Keys ................................................................................... 220
3.6.8. Calculating Visits Per Day ................................................................................ 220
3.6.9. Using AUTO_INCREMENT ................................................................................. 220
3.7. Using MySQL with Apache ......................................................................................... 222
4. MySQL Programs .................................................................................................................. 223
4.1. Overview of MySQL Programs .................................................................................... 224

MySQL 5.0 Reference Manual
v
4.2. Using MySQL Programs ............................................................................................. 229
4.2.1. Invoking MySQL Programs ............................................................................... 229
4.2.2. Connecting to the MySQL Server ..................................................................... 229
4.2.3. Specifying Program Options ............................................................................. 232
4.2.4. Setting Environment Variables .......................................................................... 243
4.3. MySQL Server and Server-Startup Programs ............................................................... 244
4.3.1. mysqld — The MySQL Server ........................................................................ 244
4.3.2. mysqld_safe — MySQL Server Startup Script ................................................ 245
4.3.3. mysql.server — MySQL Server Startup Script .............................................. 249
4.3.4. mysqld_multi — Manage Multiple MySQL Servers ........................................ 250
4.4. MySQL Installation-Related Programs ......................................................................... 254
4.4.1. comp_err — Compile MySQL Error Message File ............................................ 254
4.4.2. make_win_bin_dist — Package MySQL Distribution as Zip Archive ............... 255
4.4.3. make_win_src_distribution — Create Source Distribution for Windows ...... 256
4.4.4. mysqlbug — Generate Bug Report ................................................................. 257
4.4.5. mysql_fix_privilege_tables — Upgrade MySQL System Tables .............. 257
4.4.6. mysql_install_db — Initialize MySQL Data Directory ................................... 258
4.4.7. mysql_secure_installation — Improve MySQL Installation Security .......... 259
4.4.8. mysql_tzinfo_to_sql — Load the Time Zone Tables .................................. 260
4.4.9. mysql_upgrade — Check Tables for MySQL Upgrade .................................... 260
4.5. MySQL Client Programs ............................................................................................. 262
4.5.1. mysql — The MySQL Command-Line Tool ...................................................... 262
4.5.2. mysqladmin — Client for Administering a MySQL Server ................................. 282
4.5.3. mysqlcheck — A Table Maintenance Program ................................................ 288
4.5.4. mysqldump — A Database Backup Program .................................................... 293
4.5.5. mysqlimport — A Data Import Program ......................................................... 308
4.5.6. mysqlshow — Display Database, Table, and Column Information ...................... 312
4.6. MySQL Administrative and Utility Programs ................................................................. 315
4.6.1. innochecksum — Offline InnoDB File Checksum Utility .................................... 315
4.6.2. myisam_ftdump — Display Full-Text Index information .................................... 316
4.6.3. myisamchk — MyISAM Table-Maintenance Utility ............................................ 317
4.6.4. myisamlog — Display MyISAM Log File Contents ............................................ 333
4.6.5. myisampack — Generate Compressed, Read-Only MyISAM Tables .................. 334
4.6.6. mysqlaccess — Client for Checking Access Privileges .................................... 339
4.6.7. mysqlbinlog — Utility for Processing Binary Log Files .................................... 342
4.6.8. mysqldumpslow — Summarize Slow Query Log Files ...................................... 350
4.6.9. mysqlhotcopy — A Database Backup Program .............................................. 352
4.6.10. mysqlmanager — The MySQL Instance Manager .......................................... 354
4.6.11. mysql_convert_table_format — Convert Tables to Use a Given Storage
Engine ...................................................................................................................... 364
4.6.12. mysql_explain_log — Use EXPLAIN on Statements in Query Log .............. 365
4.6.13. mysql_find_rows — Extract SQL Statements from Files .............................. 366
4.6.14. mysql_fix_extensions — Normalize Table File Name Extensions .............. 366
4.6.15. mysql_setpermission — Interactively Set Permissions in Grant Tables ........ 367
4.6.16. mysql_tableinfo — Generate Database Metadata ...................................... 367
4.6.17. mysql_waitpid — Kill Process and Wait for Its Termination .......................... 369
4.6.18. mysql_zap — Kill Processes That Match a Pattern ........................................ 370
4.7. MySQL Program Development Utilities ........................................................................ 370
4.7.1. msql2mysql — Convert mSQL Programs for Use with MySQL ......................... 371
4.7.2. mysql_config — Display Options for Compiling Clients .................................. 371
4.7.3. my_print_defaults — Display Options from Option Files .............................. 372
4.7.4. resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols ....... 373
4.8. Miscellaneous Programs ............................................................................................. 373
4.8.1. perror — Explain Error Codes ....................................................................... 373
4.8.2. replace — A String-Replacement Utility ......................................................... 374
4.8.3. resolveip — Resolve Host name to IP Address or Vice Versa ........................ 375
5. MySQL Server Administration ................................................................................................ 377
5.1. The MySQL Server .................................................................................................... 377

MySQL 5.0 Reference Manual
vi
5.1.1. Server Option and Variable Reference .............................................................. 378
5.1.2. Server Configuration Defaults ........................................................................... 399
5.1.3. Server Command Options ................................................................................ 399
5.1.4. Server System Variables .................................................................................. 425
5.1.5. Using System Variables ................................................................................... 507
5.1.6. Server Status Variables ................................................................................... 516
5.1.7. Server SQL Modes .......................................................................................... 534
5.1.8. Server-Side Help ............................................................................................. 541
5.1.9. Server Response to Signals ............................................................................. 541
5.1.10. The Shutdown Process .................................................................................. 542
5.2. MySQL Server Logs ................................................................................................... 543
5.2.1. The Error Log .................................................................................................. 543
5.2.2. The General Query Log ................................................................................... 544
5.2.3. The Binary Log ................................................................................................ 545
5.2.4. The Slow Query Log ........................................................................................ 548
5.2.5. Server Log Maintenance .................................................................................. 549
5.3. Running Multiple MySQL Instances on One Machine ................................................... 550
5.3.1. Setting Up Multiple Data Directories ................................................................. 552
5.3.2. Running Multiple MySQL Instances on Windows ............................................... 553
5.3.3. Running Multiple MySQL Instances on Unix ...................................................... 555
5.3.4. Using Client Programs in a Multiple-Server Environment .................................... 556
6. Security ................................................................................................................................ 559
6.1. General Security Issues .............................................................................................. 559
6.1.1. Security Guidelines .......................................................................................... 560
6.1.2. Keeping Passwords Secure ............................................................................. 561
6.1.3. Making MySQL Secure Against Attackers ......................................................... 569
6.1.4. Security-Related mysqld Options and Variables ............................................... 570
6.1.5. How to Run MySQL as a Normal User ............................................................. 571
6.1.6. Security Issues with LOAD DATA LOCAL ......................................................... 572
6.1.7. Client Programming Security Guidelines ........................................................... 573
6.2. The MySQL Access Privilege System .......................................................................... 574
6.2.1. Privileges Provided by MySQL ......................................................................... 575
6.2.2. Privilege System Grant Tables ......................................................................... 579
6.2.3. Specifying Account Names ............................................................................... 583
6.2.4. Access Control, Stage 1: Connection Verification .............................................. 585
6.2.5. Access Control, Stage 2: Request Verification ................................................... 588
6.2.6. When Privilege Changes Take Effect ................................................................ 590
6.2.7. Causes of Access-Denied Errors ...................................................................... 590
6.3. MySQL User Account Management ............................................................................. 595
6.3.1. User Names and Passwords ............................................................................ 595
6.3.2. Adding User Accounts ..................................................................................... 597
6.3.3. Removing User Accounts ................................................................................. 600
6.3.4. Setting Account Resource Limits ...................................................................... 600
6.3.5. Assigning Account Passwords .......................................................................... 602
6.3.6. Using SSL for Secure Connections ................................................................... 603
6.3.7. Connecting to MySQL Remotely from Windows with SSH .................................. 614
6.3.8. SQL-Based MySQL Account Activity Auditing .................................................... 614
7. Backup and Recovery ........................................................................................................... 617
7.1. Backup and Recovery Types ...................................................................................... 618
7.2. Database Backup Methods ......................................................................................... 620
7.3. Example Backup and Recovery Strategy ..................................................................... 622
7.3.1. Establishing a Backup Policy ............................................................................ 623
7.3.2. Using Backups for Recovery ............................................................................ 625
7.3.3. Backup Strategy Summary ............................................................................... 625
7.4. Using mysqldump for Backups ................................................................................... 625
7.4.1. Dumping Data in SQL Format with mysqldump ................................................ 626
7.4.2. Reloading SQL-Format Backups ....................................................................... 627
7.4.3. Dumping Data in Delimited-Text Format with mysqldump .................................. 627

MySQL 5.0 Reference Manual
vii
7.4.4. Reloading Delimited-Text Format Backups ........................................................ 628
7.4.5. mysqldump Tips ............................................................................................. 629
7.5. Point-in-Time (Incremental) Recovery Using the Binary Log .......................................... 631
7.5.1. Point-in-Time Recovery Using Event Times ....................................................... 632
7.5.2. Point-in-Time Recovery Using Event Positions .................................................. 632
7.6. MyISAM Table Maintenance and Crash Recovery ........................................................ 633
7.6.1. Using myisamchk for Crash Recovery ............................................................. 634
7.6.2. How to Check MyISAM Tables for Errors .......................................................... 634
7.6.3. How to Repair MyISAM Tables ......................................................................... 635
7.6.4. MyISAM Table Optimization .............................................................................. 637
7.6.5. Setting Up a MyISAM Table Maintenance Schedule ........................................... 638
8. Optimization .......................................................................................................................... 639
8.1. Optimization Overview ................................................................................................ 640
8.1.1. MySQL Design Limitations and Tradeoffs ......................................................... 640
8.1.2. Designing Applications for Portability ................................................................ 640
8.1.3. The MySQL Benchmark Suite .......................................................................... 641
8.1.4. Using Your Own Benchmarks .......................................................................... 642
8.2. Obtaining Query Execution Plan Information ................................................................ 643
8.2.1. Optimizing Queries with EXPLAIN .................................................................... 643
8.2.2. EXPLAIN Output Format .................................................................................. 643
8.2.3. EXPLAIN EXTENDED Output Format ................................................................ 652
8.2.4. Estimating Query Performance ......................................................................... 654
8.3. Optimizing SQL Statements ........................................................................................ 654
8.3.1. Optimizing SELECT Statements ........................................................................ 654
8.3.2. Optimizing Non-SELECT Statements ................................................................. 685
8.3.3. Other Optimization Tips ................................................................................... 689
8.4. Controlling the Query Optimizer .................................................................................. 691
8.5. Optimization and Indexes ........................................................................................... 692
8.5.1. Column Indexes .............................................................................................. 692
8.5.2. Multiple-Column Indexes .................................................................................. 692
8.5.3. How MySQL Uses Indexes .............................................................................. 694
8.5.4. MyISAM Index Statistics Collection ................................................................... 696
8.6. Buffering and Caching ................................................................................................ 698
8.6.1. The MyISAM Key Cache .................................................................................. 698
8.6.2. The InnoDB Buffer Pool .................................................................................. 702
8.6.3. The MySQL Query Cache ................................................................................ 703
8.7. Locking Issues ........................................................................................................... 709
8.7.1. Internal Locking Methods ................................................................................. 709
8.7.2. Table Locking Issues ....................................................................................... 711
8.7.3. Concurrent Inserts ........................................................................................... 712
8.7.4. External Locking .............................................................................................. 713
8.8. Optimizing Database Structure .................................................................................... 714
8.8.1. Make Your Data as Small as Possible .............................................................. 714
8.8.2. Using PROCEDURE ANALYSE ........................................................................... 715
8.8.3. How MySQL Opens and Closes Tables ............................................................ 716
8.8.4. Disadvantages of Creating Many Tables in the Same Database ......................... 717
8.8.5. How MySQL Uses Internal Temporary Tables ................................................... 717
8.9. Optimizing the MySQL Server ..................................................................................... 718
8.9.1. System Factors and Startup Parameter Tuning ................................................. 718
8.9.2. Tuning Server Parameters ............................................................................... 718
8.9.3. How MySQL Uses Threads for Client Connections ............................................ 723
8.9.4. How MySQL Uses Memory .............................................................................. 724
8.9.5. Disk Issues ..................................................................................................... 725
8.9.6. Using Symbolic Links ....................................................................................... 726
8.9.7. Enabling Large Page Support .......................................................................... 729
8.9.8. DNS Lookup Optimization and the Host Cache ................................................. 730
8.10. Examining Thread Information ................................................................................... 731
8.10.1. Thread Command Values ............................................................................... 732

MySQL 5.0 Reference Manual
viii
8.10.2. General Thread States ................................................................................... 734
8.10.3. Delayed-Insert Thread States ......................................................................... 739
8.10.4. Query Cache Thread States ........................................................................... 740
8.10.5. Replication Master Thread States ................................................................... 740
8.10.6. Replication Slave I/O Thread States ............................................................... 741
8.10.7. Replication Slave SQL Thread States ............................................................. 742
8.10.8. Replication Slave Connection Thread States ................................................... 742
8.10.9. MySQL Cluster Thread States ........................................................................ 743
9. Language Structure ............................................................................................................... 745
9.1. Literal Values ............................................................................................................. 745
9.1.1. String Literals .................................................................................................. 745
9.1.2. Number Literals ............................................................................................... 747
9.1.3. Date and Time Literals ..................................................................................... 748
9.1.4. Hexadecimal Literals ........................................................................................ 750
9.1.5. Boolean Literals ............................................................................................... 750
9.1.6. Bit-Field Literals ............................................................................................... 750
9.1.7. NULL Values ................................................................................................... 751
9.2. Schema Object Names ............................................................................................... 751
9.2.1. Identifier Qualifiers ........................................................................................... 753
9.2.2. Identifier Case Sensitivity ................................................................................. 754
9.2.3. Function Name Parsing and Resolution ............................................................ 755
9.3. Reserved Words ........................................................................................................ 758
9.4. User-Defined Variables ............................................................................................... 761
9.5. Expression Syntax ...................................................................................................... 764
9.6. Comment Syntax ........................................................................................................ 766
10. Globalization ....................................................................................................................... 767
10.1. Character Set Support .............................................................................................. 767
10.1.1. Character Sets and Collations in General ........................................................ 768
10.1.2. Character Sets and Collations in MySQL ........................................................ 769
10.1.3. Specifying Character Sets and Collations ........................................................ 770
10.1.4. Connection Character Sets and Collations ...................................................... 776
10.1.5. Configuring the Character Set and Collation for Applications ............................ 778
10.1.6. Character Set for Error Messages .................................................................. 780
10.1.7. Collation Issues ............................................................................................. 780
10.1.8. String Repertoire ............................................................................................ 787
10.1.9. Operations Affected by Character Set Support ................................................ 788
10.1.10. Unicode Support .......................................................................................... 791
10.1.11. UTF-8 for Metadata ...................................................................................... 792
10.1.12. Column Character Set Conversion ................................................................ 793
10.1.13. Character Sets and Collations That MySQL Supports .................................... 794
10.2. Setting the Error Message Language ........................................................................ 804
10.3. Adding a Character Set ............................................................................................ 805
10.3.1. Character Definition Arrays ............................................................................. 807
10.3.2. String Collating Support for Complex Character Sets ....................................... 808
10.3.3. Multi-Byte Character Support for Complex Character Sets ................................ 808
10.4. Adding a Collation to a Character Set ....................................................................... 808
10.4.1. Collation Implementation Types ...................................................................... 809
10.4.2. Choosing a Collation ID ................................................................................. 811
10.4.3. Adding a Simple Collation to an 8-Bit Character Set ........................................ 812
10.4.4. Adding a UCA Collation to a Unicode Character Set ........................................ 813
10.5. Character Set Configuration ...................................................................................... 816
10.6. MySQL Server Time Zone Support ............................................................................ 816
10.6.1. Staying Current with Time Zone Changes ....................................................... 819
10.6.2. Time Zone Leap Second Support ................................................................... 820
10.7. MySQL Server Locale Support .................................................................................. 821
11. Data Types ......................................................................................................................... 825
11.1. Data Type Overview ................................................................................................. 825
11.1.1. Numeric Type Overview ................................................................................. 825

MySQL 5.0 Reference Manual
ix
11.1.2. Date and Time Type Overview ....................................................................... 829
11.1.3. String Type Overview ..................................................................................... 830
11.1.4. Numeric Types .............................................................................................. 834
11.1.5. Date and Time Types .................................................................................... 838
11.1.6. String Types .................................................................................................. 848
11.1.7. Data Type Default Values .............................................................................. 856
11.2. Data Type Storage Requirements ............................................................................. 858
11.3. Choosing the Right Type for a Column ...................................................................... 861
11.4. Using Data Types from Other Database Engines ....................................................... 861
12. Functions and Operators ..................................................................................................... 863
12.1. Function and Operator Reference ............................................................................. 864
12.2. Type Conversion in Expression Evaluation ................................................................ 871
12.3. Operators ................................................................................................................. 872
12.3.1. Operator Precedence ..................................................................................... 873
12.3.2. Comparison Functions and Operators ............................................................. 874
12.3.3. Logical Operators .......................................................................................... 880
12.3.4. Assignment Operators .................................................................................... 881
12.4. Control Flow Functions ............................................................................................. 882
12.5. String Functions ....................................................................................................... 884
12.5.1. String Comparison Functions .......................................................................... 895
12.5.2. Regular Expressions ...................................................................................... 899
12.6. Numeric Functions and Operators ............................................................................. 904
12.6.1. Arithmetic Operators ...................................................................................... 905
12.6.2. Mathematical Functions .................................................................................. 907
12.7. Date and Time Functions .......................................................................................... 915
12.8. What Calendar Is Used By MySQL? .......................................................................... 934
12.9. Full-Text Search Functions ....................................................................................... 934
12.9.1. Natural Language Full-Text Searches ............................................................. 935
12.9.2. Boolean Full-Text Searches ........................................................................... 938
12.9.3. Full-Text Searches with Query Expansion ....................................................... 940
12.9.4. Full-Text Stopwords ....................................................................................... 941
12.9.5. Full-Text Restrictions ..................................................................................... 944
12.9.6. Fine-Tuning MySQL Full-Text Search ............................................................. 944
12.9.7. Adding a Collation for Full-Text Indexing ......................................................... 946
12.10. Cast Functions and Operators ................................................................................. 948
12.11. Bit Functions .......................................................................................................... 950
12.12. Encryption and Compression Functions ................................................................... 952
12.13. Information Functions .............................................................................................. 957
12.14. Miscellaneous Functions ......................................................................................... 964
12.15. Functions and Modifiers for Use with GROUP BY Clauses ......................................... 968
12.15.1. GROUP BY (Aggregate) Functions ................................................................ 968
12.15.2. GROUP BY Modifiers .................................................................................... 972
12.15.3. MySQL Extensions to GROUP BY ................................................................. 975
12.16. Spatial Extensions .................................................................................................. 976
12.16.1. Introduction to MySQL Spatial Support .......................................................... 976
12.16.2. The OpenGIS Geometry Model .................................................................... 977
12.16.3. Supported Spatial Data Formats ................................................................... 983
12.16.4. Creating a Spatially Enabled MySQL Database ............................................. 984
12.16.5. Spatial Analysis Functions ............................................................................ 989
12.16.6. Optimizing Spatial Analysis ........................................................................... 998
12.16.7. MySQL Conformance and Compatibility ...................................................... 1001
12.17. Precision Math ...................................................................................................... 1001
12.17.1. Types of Numeric Values ........................................................................... 1002
12.17.2. DECIMAL Data Type Changes .................................................................... 1002
12.17.3. Expression Handling ................................................................................... 1004
12.17.4. Rounding Behavior ..................................................................................... 1005
12.17.5. Precision Math Examples ........................................................................... 1006
13. SQL Statement Syntax ...................................................................................................... 1011

MySQL 5.0 Reference Manual
x
13.1. Data Definition Statements ...................................................................................... 1012
13.1.1. ALTER DATABASE Syntax ........................................................................... 1012
13.1.2. ALTER FUNCTION Syntax ........................................................................... 1012
13.1.3. ALTER PROCEDURE Syntax ......................................................................... 1013
13.1.4. ALTER TABLE Syntax ................................................................................. 1013
13.1.5. ALTER VIEW Syntax ................................................................................... 1020
13.1.6. CREATE DATABASE Syntax ......................................................................... 1020
13.1.7. CREATE FUNCTION Syntax ......................................................................... 1021
13.1.8. CREATE INDEX Syntax ............................................................................... 1021
13.1.9. CREATE PROCEDURE and CREATE FUNCTION Syntax .................................. 1023
13.1.10. CREATE TABLE Syntax ............................................................................. 1029
13.1.11. CREATE TRIGGER Syntax ......................................................................... 1047
13.1.12. CREATE VIEW Syntax ............................................................................... 1050
13.1.13. DROP DATABASE Syntax ........................................................................... 1054
13.1.14. DROP FUNCTION Syntax ........................................................................... 1055
13.1.15. DROP INDEX Syntax ................................................................................. 1055
13.1.16. DROP PROCEDURE and DROP FUNCTION Syntax ........................................ 1056
13.1.17. DROP TABLE Syntax ................................................................................. 1056
13.1.18. DROP TRIGGER Syntax ............................................................................. 1056
13.1.19. DROP VIEW Syntax ................................................................................... 1057
13.1.20. RENAME TABLE Syntax ............................................................................. 1057
13.1.21. TRUNCATE TABLE Syntax ......................................................................... 1058
13.2. Data Manipulation Statements ................................................................................. 1059
13.2.1. CALL Syntax ................................................................................................ 1059
13.2.2. DELETE Syntax ............................................................................................ 1060
13.2.3. DO Syntax .................................................................................................... 1064
13.2.4. HANDLER Syntax .......................................................................................... 1064
13.2.5. INSERT Syntax ............................................................................................ 1065
13.2.6. LOAD DATA INFILE Syntax ....................................................................... 1072
13.2.7. REPLACE Syntax .......................................................................................... 1081
13.2.8. SELECT Syntax ............................................................................................ 1082
13.2.9. Subquery Syntax .......................................................................................... 1098
13.2.10. UPDATE Syntax .......................................................................................... 1108
13.3. MySQL Transactional and Locking Statements ......................................................... 1110
13.3.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax ............................... 1110
13.3.2. Statements That Cannot Be Rolled Back ....................................................... 1113
13.3.3. Statements That Cause an Implicit Commit ................................................... 1113
13.3.4. SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT, and
Syntax .................................................................................................................... 1114
13.3.5. LOCK TABLES and UNLOCK TABLES Syntax ................................................ 1114
13.3.6. SET TRANSACTION Syntax ......................................................................... 1119
13.3.7. XA Transactions .......................................................................................... 1121
13.4. Replication Statements ........................................................................................... 1125
13.4.1. SQL Statements for Controlling Master Servers ............................................. 1125
13.4.2. SQL Statements for Controlling Slave Servers ............................................... 1127
13.5. SQL Syntax for Prepared Statements ...................................................................... 1133
13.5.1. PREPARE Syntax .......................................................................................... 1136
13.5.2. EXECUTE Syntax .......................................................................................... 1136
13.5.3. DEALLOCATE PREPARE Syntax ................................................................... 1136
13.6. MySQL Compound-Statement Syntax ...................................................................... 1137
13.6.1. BEGIN ... END Compound-Statement Syntax ............................................ 1137
13.6.2. Statement Label Syntax ............................................................................... 1137
13.6.3. DECLARE Syntax .......................................................................................... 1138
13.6.4. Variables in Stored Programs ....................................................................... 1138
13.6.5. Flow Control Statements .............................................................................. 1140
13.6.6. Cursors ....................................................................................................... 1144
13.6.7. Condition Handling ....................................................................................... 1145
13.7. Database Administration Statements ........................................................................ 1149