MySQL Reference Manual
留學生dissertation網Table of Contents
Preface ..............................................................................................................xxi
1. General Information ........................................................................................... 1
1.1. About This Manual .................................................................................. 1
1.1.1. Conventions Used in This Manual .................................................... 2
1.2. Overview of the MySQL Database Management System ................................ 4
1.2.1. History of MySQL ........................................................................ 5
1.2.2. The Main Features of MySQL ......................................................... 5
1.2.3. MySQL Stability .......................................................................... 8
1.2.4. How Big MySQL Tables Can Be ..................................................... 8
1.2.5. Year 2000 Compliance .................................................................10
1.3. MySQL Development Roadmap ................................................................11
1.3.1. MySQL 4.0 in a Nutshell ...............................................................11
1.3.2. MySQL 4.1 in a Nutshell ...............................................................13
1.3.3. MySQL 5.0: The Next Development Release ....................................15
1.4. MySQL Information Sources ....................................................................15
1.4.1. MySQL Mailing Lists ...................................................................15
1.4.2. MySQL Community Support on IRC (Internet Relay Chat) ..................21
1.4.3. MySQL Community Support at the MySQL Forums ..........................22
1.5. MySQL Standards Compliance .................................................................22
1.5.1. What Standards MySQL Follows ....................................................23
1.5.2. Selecting SQL Modes ...................................................................23
1.5.3. Running MySQL in ANSI Mode ....................................................23
1.5.4. MySQL Extensions to Standard SQL ...............................................24
1.5.5. MySQL Differences from Standard SQL ..........................................26
1.5.6. How MySQL Deals with Constraints ...............................................32
1.5.7. Known Errors and Design Deficiencies in MySQL .............................35
2. Installing MySQL .............................................................................................41
2.1. General Installation Issues .......................................................................41
2.1.1. Operating Systems Supported by MySQL .........................................41#p#分頁標題#e#
2.1.2. Choosing Which MySQL Distribution to Install .................................43
2.1.3. How to Get MySQL .....................................................................54
2.1.4. Verifying Package Integrity Using MD5 Checksums or GnuPG ............54
2.1.5. Installation Layouts ......................................................................57
2.2. Standard MySQL Installation Using a Binary Distribution .............................58
2.3. Installing MySQL on Windows .................................................................58
2.3.1. Windows System Requirements ......................................................59
2.3.2. Choosing An Installation Package ...................................................60
2.3.3. Installing MySQL with the Automated Installer .................................60
2.3.4. Using the MySQL Installation Wizard .............................................60
2.3.5. Using the Configuration Wizard .....................................................63
2.3.6. Installing MySQL from a noinstall Zip Archive .................................68
2.3.7. Extracting the Install Archive .........................................................68
2.3.8. Creating an Option File .................................................................68
2.3.9. Selecting a MySQL Server type ......................................................69
2.3.10. Starting the Server for the First Time .............................................71
2.3.11. Starting MySQL from the Windows Command Line .........................72
2.3.12. Starting MySQL as a Windows Service ..........................................73
2.3.13. Testing The MySQL Installation ...................................................75
2.3.14. Troubleshooting a MySQL Installation Under Windows ....................75
2.3.15. Upgrading MySQL on Windows ...................................................76
2.3.16. MySQL on Windows Compared to MySQL on Unix ........................77
2.4. Installing MySQL on Linux ......................................................................80
2.5. Installing MySQL on Mac OS X ...............................................................82
2.6. Installing MySQL on NetWare .................................................................84
2.7. Installing MySQL on Other Unix-Like Systems ...........................................86
2.8. MySQL Installation Using a Source Distribution ..........................................89
2.8.1. Source Installation Overview .........................................................90
iv
2.8.2. Typical configure Options ............................................................93
2.8.3. Installing from the Development Source Tree ....................................95
2.8.4. Dealing with Problems Compiling MySQL .......................................98#p#分頁標題#e#
2.8.5. MIT-pthreads Notes ................................................................... 101
2.8.6. Installing MySQL from Source on Windows ................................... 102
2.8.7. Compiling MySQL Clients on Windows ........................................ 106
2.9. Post-Installation Setup and Testing .......................................................... 106
2.9.1. Windows Post-Installation Procedures ........................................... 107
2.9.2. Unix Post-Installation Procedures ................................................. 107
2.9.3. Securing the Initial MySQL Accounts ............................................ 118
2.10. Upgrading MySQL ............................................................................. 121
2.10.1. Upgrading from Version 4.1 to 5.0 .............................................. 122
2.10.2. Upgrading from Version 4.0 to 4.1 .............................................. 123
2.10.3. Upgrading from Version 3.23 to 4.0 ............................................. 128
2.10.4. Upgrading from Version 3.22 to 3.23 ........................................... 131
2.10.5. Upgrading from Version 3.21 to 3.22 ........................................... 133
2.10.6. Upgrading from Version 3.20 to 3.21 ........................................... 133
2.10.7. Upgrading the Grant Tables ....................................................... 134
2.10.8. Copying MySQL Databases to Another Machine ........................... 135
2.11. Downgrading MySQL ......................................................................... 136
2.11.1. Downgrading to 4.0 .................................................................. 137
2.12. Operating System-Specific Notes ........................................................... 138
2.12.1. Linux Notes ............................................................................ 138
2.12.2. Mac OS X Notes ...................................................................... 144
2.12.3. Solaris Notes ........................................................................... 145
2.12.4. BSD Notes .............................................................................. 149
2.12.5. Other Unix Notes ..................................................................... 152
2.12.6. OS/2 Notes ............................................................................. 164
2.12.7. BeOS Notes ............................................................................ 164
2.13. Perl Installation Notes ......................................................................... 165
2.13.1. Installing Perl on Unix .............................................................. 165
2.13.2. Installing ActiveState Perl on Windows ........................................ 166
2.13.3. Problems Using the Perl DBI/DBD Interface .................................. 166#p#分頁標題#e#
3. MySQL Tutorial ............................................................................................. 169
3.1. Connecting to and Disconnecting from the Server ....................................... 169
3.2. Entering Queries .................................................................................. 170
3.3. Creating and Using a Database ................................................................ 172
3.3.1. Creating and Selecting a Database ................................................. 174
3.3.2. Creating a Table ........................................................................ 174
3.3.3. Loading Data into a Table ........................................................... 175
3.3.4. Retrieving Information from a Table ............................................. 177
3.4. Getting Information About Databases and Tables ....................................... 189
3.5. Using mysql in Batch Mode ................................................................... 190
3.6. Examples of Common Queries ................................................................ 192
3.6.1. The Maximum Value for a Column ............................................... 192
3.6.2. The Row Holding the Maximum of a Certain Column ...................... 193
3.6.3. Maximum of Column per Group ................................................... 193
3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field ......... 194
3.6.5. Using User Variables .................................................................. 194
3.6.6. Using Foreign Keys ................................................................... 195
3.6.7. Searching on Two Keys .............................................................. 196
3.6.8. Calculating Visits Per Day ........................................................... 196
3.6.9. Using AUTO_INCREMENT .......................................................... 197
3.7. Queries from the Twin Project ................................................................ 198
3.7.1. Find All Non-distributed Twins .................................................... 198
3.7.2. Show a Table of Twin Pair Status ................................................. 200
3.8. Using MySQL with Apache ................................................................... 201
4. Using MySQL Programs .................................................................................. 202
4.1. Overview of MySQL Programs ............................................................... 202
4.2. Invoking MySQL Programs ................................................................... 203
4.3. Specifying Program Options ................................................................... 203
4.3.1. Using Options on the Command Line ............................................ 204
4.3.2. Using Option Files ..................................................................... 205#p#分頁標題#e#
MySQL Reference Manual
v
4.3.3. Using Environment Variables to Specify Options ............................. 208
4.3.4. Using Options to Set Program Variables ......................................... 209
5. 留學生dissertation網Database Administration .................................................................................. 211
5.1. The MySQL Server and Server Startup Scripts ........................................... 211
5.1.1. Overview of the Server-Side Scripts and Utilities ............................. 211
5.1.2. The mysqld-max Extended MySQL Server .................................... 212
5.1.3. The mysqld_safe Server Startup Script .......................................... 214
5.1.4. The mysql.server Server Startup Script ......................................... 217
5.1.5. The mysqld_multi Program for Managing Multiple MySQL Servers .. 217
5.2. Configuring the MySQL Server .............................................................. 221
5.2.1. mysqld Command-Line Options ................................................... 221
5.2.2. The Server SQL Mode ................................................................ 231
5.2.3. Server System Variables ............................................................. 236
5.2.4. Server Status Variables ............................................................... 262
5.3. The MySQL Server Shutdown Process ..................................................... 271
5.4. General Security Issues ......................................................................... 272
5.4.1. General Security Guidelines ......................................................... 273
5.4.2. Making MySQL Secure Against Attackers ..................................... 275
5.4.3. Startup Options for mysqld Concerning Security ............................. 276
5.4.4. Security Issues with LOAD DATA LOCAL ..................................... 278
5.5. The MySQL Access Privilege System ...................................................... 279
5.5.1. What the Privilege System Does ................................................... 279
5.5.2. How the Privilege System Works .................................................. 279
5.5.3. Privileges Provided by MySQL .................................................... 283
5.5.4. Connecting to the MySQL Server ................................................. 286
5.5.5. Access Control, Stage 1: Connection Verification ............................ 287
5.5.6. Access Control, Stage 2: Request Verification ................................. 291
5.5.7. When Privilege Changes Take Effect ............................................. 293
5.5.8. Causes of Access denied Errors ............................................. 293
5.5.9. Password Hashing in MySQL 4.1 ................................................. 298#p#分頁標題#e#
5.6. MySQL User Account Management ........................................................ 302
5.6.1. MySQL Usernames and Passwords ............................................... 303
5.6.2. Adding New User Accounts to MySQL ......................................... 304
5.6.3. Removing User Accounts from MySQL ......................................... 307
5.6.4. Limiting Account Resources ........................................................ 307
5.6.5. Assigning Account Passwords ...................................................... 308
5.6.6. Keeping Your Password Secure .................................................... 310
5.6.7. Using Secure Connections ........................................................... 311
5.7. Disaster Prevention and Recovery ........................................................... 317
5.7.1. Database Backups ...................................................................... 317
5.7.2. Example Backup and Recovery Strategy ........................................ 319
5.7.3. Table Maintenance and Crash Recovery ......................................... 323
5.7.4. Setting Up a Table Maintenance Schedule ...................................... 334
5.7.5. Getting Information About a Table ................................................ 335
5.8. MySQL Localization and International Usage ............................................ 340
5.8.1. The Character Set Used for Data and Sorting .................................. 340
5.8.2. Setting the Error Message Language .............................................. 342
5.8.3. Adding a New Character Set ........................................................ 342
5.8.4. The Character Definition Arrays ................................................... 344
5.8.5. String Collating Support .............................................................. 344
5.8.6. Multi-Byte Character Support ...................................................... 345
5.8.7. Problems With Character Sets ...................................................... 345
5.8.8. MySQL Server Time Zone Support ............................................... 345
5.9. The MySQL Log Files .......................................................................... 347
5.9.1. The Error Log ........................................................................... 347
5.9.2. The General Query Log .............................................................. 347
5.9.3. The Update Log ........................................................................ 348
5.9.4. The Binary Log ......................................................................... 349
5.9.5. The Slow Query Log .................................................................. 352
5.9.6. Log File Maintenance ................................................................. 352
5.10. Running Multiple MySQL Servers on the Same Machine ........................... 353#p#分頁標題#e#
5.10.1. Running Multiple Servers on Windows ........................................ 355
5.10.2. Running Multiple Servers on Unix .............................................. 358
MySQL Reference Manual
vi
5.10.3. Using Client Programs in a Multiple-Server Environment ................ 359
5.11. The MySQL Query Cache .................................................................... 360
5.11.1. How the Query Cache Operates .................................................. 360
5.11.2. Query Cache SELECT Options ................................................... 362
5.11.3. Query Cache Configuration ........................................................ 362
5.11.4. Query Cache Status and Maintenance .......................................... 363
6. Replication in MySQL ..................................................................................... 365
6.1. Introduction to Replication ..................................................................... 365
6.2. Replication Implementation Overview ...................................................... 365
6.3. Replication Implementation Details ......................................................... 366
6.3.1. Replication Master Thread States .................................................. 367
6.3.2. Replication Slave I/O Thread States .............................................. 368
6.3.3. Replication Slave SQL Thread States ............................................. 369
6.3.4. Replication Relay and Status Files ................................................ 369
6.4. How to Set Up Replication ..................................................................... 371
6.5. Replication Compatibility Between MySQL Versions ................................. 375
6.6. Upgrading a Replication Setup ................................................................ 375
6.6.1. Upgrading Replication to 4.0 or 4.1 ............................................... 376
6.6.2. Upgrading Replication to 5.0 ....................................................... 376
6.7. Replication Features and Known Problems ................................................ 377
6.8. Replication Startup Options .................................................................... 380
6.9. Replication FAQ .................................................................................. 388
6.10. Troubleshooting Replication ................................................................. 394
6.11. Reporting Replication Bugs .................................................................. 394
7. MySQL Optimization ...................................................................................... 396
7.1. Optimization Overview ......................................................................... 396
7.1.1. MySQL Design Limitations and Tradeoffs ..................................... 396#p#分頁標題#e#
7.1.2. Designing Applications for Portability ........................................... 397
7.1.3. What We Have Used MySQL For ................................................. 398
7.1.4. The MySQL Benchmark Suite ...................................................... 398
7.1.5. Using Your Own Benchmarks ...................................................... 399
7.2. Optimizing SELECT Statements and Other Queries .................................... 400
7.2.1. EXPLAIN Syntax (Get Information About a SELECT) ..................... 400
7.2.2. Estimating Query Performance ..................................................... 408
7.2.3. Speed of SELECT Queries ........................................................... 409
7.2.4. How MySQL Optimizes WHERE Clauses ........................................ 409
7.2.5. Range Optimization ................................................................... 411
7.2.6. Index Merge Optimization ........................................................... 414
7.2.7. How MySQL Optimizes IS NULL ............................................... 416
7.2.8. How MySQL Optimizes DISTINCT ............................................. 417
7.2.9. How MySQL Optimizes LEFT JOIN and RIGHT JOIN ................ 418
7.2.10. How MySQL Optimizes ORDER BY ........................................... 419
7.2.11. How MySQL Optimizes GROUP BY ........................................... 421
7.2.12. How MySQL Optimizes LIMIT ................................................. 423
7.2.13. How to Avoid Table Scans ......................................................... 423
7.2.14. Speed of INSERT Statements ..................................................... 424
7.2.15. Speed of UPDATE Statements ..................................................... 426
7.2.16. Speed of DELETE Statements ..................................................... 426
7.2.17. Other Optimization Tips ............................................................ 426
7.3. Locking Issues ..................................................................................... 428
7.3.1. Locking Methods ....................................................................... 428
7.3.2. Table Locking Issues .................................................................. 430
7.4. Optimizing Database Structure ................................................................ 432
7.4.1. Design Choices ......................................................................... 432
7.4.2. Make Your Data as Small as Possible ............................................ 432
7.4.3. Column Indexes ........................................................................ 433
7.4.4. Multiple-Column Indexes ............................................................ 434
7.4.5. How MySQL Uses Indexes .......................................................... 435#p#分頁標題#e#
7.4.6. The MyISAM Key Cache ............................................................. 437
7.4.7. How MySQL Counts Open Tables ................................................ 442
7.4.8. How MySQL Opens and Closes Tables .......................................... 442
7.4.9. Drawbacks to Creating Many Tables in the Same Database ................ 443
7.5. Optimizing the MySQL Server ............................................................... 443
MySQL Reference Manual
vii
7.5.1. System Factors and Startup Parameter Tuning ................................. 443
7.5.2. Tuning Server Parameters ............................................................ 444
7.5.3. Controlling Query Optimizer Performance ...................................... 446
7.5.4. How Compiling and Linking Affects the Speed of MySQL ................ 447
7.5.5. How MySQL Uses Memory ........................................................ 448
7.5.6. How MySQL Uses DNS ............................................................. 449
7.6. Disk Issues .......................................................................................... 450
7.6.1. Using Symbolic Links ................................................................ 451
8. MySQL Client and Utility Programs ................................................................... 455
8.1. Overview of the Client-Side Scripts and Utilities ........................................ 455
8.2. myisampack, the MySQL Compressed Read-only Table Generator ............... 457
8.3. mysql, the Command-Line Tool .............................................................. 462
8.3.1. mysql Commands ...................................................................... 468
8.3.2. Executing SQL Statements from a Text File .................................... 471
8.3.3. mysql Tips ............................................................................... 471
8.4. mysqladmin, Administering a MySQL Server ........................................... 473
8.5. The mysqlbinlog Binary Log Utility ........................................................ 478
8.6. mysqlcc, the MySQL Control Center ....................................................... 481
8.7. The mysqlcheck Table Maintenance and Repair Program ............................ 483
8.8. The mysqldump Database Backup Program .............................................. 486
8.9. The mysqlhotcopy Database Backup Program ........................................... 493
8.10. The mysqlimport Data Import Program ................................................. 495
8.11. mysqlshow, Showing Databases, Tables, and Columns .............................. 497
8.12. perror, Explaining Error Codes ............................................................ 499
8.13. The replace String-Replacement Utility .................................................. 499#p#分頁標題#e#
9. Language Structure ......................................................................................... 501
9.1. Literal Values ...................................................................................... 501
9.1.1. Strings ..................................................................................... 501
9.1.2. Numbers .................................................................................. 503
9.1.3. Hexadecimal Values ................................................................... 503
9.1.4. Boolean Values ......................................................................... 504
9.1.5. Bit-Field Values ........................................................................ 504
9.1.6. NULL Values ............................................................................ 504
9.2. Database, Table, Index, Column, and Alias Names ..................................... 504
9.2.1. Identifier Qualifiers .................................................................... 506
9.2.2. Identifier Case Sensitivity ............................................................ 506
9.3. User Variables ..................................................................................... 507
9.4. System Variables ................................................................................. 509
9.4.1. Structured System Variables ........................................................ 510
9.5. Comment Syntax .................................................................................. 512
9.6. Treatment of Reserved Words in MySQL ................................................. 512
10. Character Set Support .................................................................................... 516
10.1. Character Sets and Collations in General ................................................. 516
10.2. Character Sets and Collations in MySQL ................................................ 517
10.3. Determining the Default Character Set and Collation ................................. 518
10.3.1. Server Character Set and Collation .............................................. 518
10.3.2. Database Character Set and Collation ........................................... 519
10.3.3. Table Character Set and Collation ............................................... 519
10.3.4. Column Character Set and Collation ............................................ 520
10.3.5. Examples of Character Set and Collation Assignment ..................... 520
10.3.6. Connection Character Sets and Collations ..................................... 521
10.3.7. Character String Literal Character Set and Collation ....................... 523
10.3.8. Using COLLATE in SQL Statements ............................................ 524
10.3.9. COLLATE Clause Precedence ..................................................... 525#p#分頁標題#e#
10.3.10. BINARY Operator .................................................................. 525
10.3.11. Some Special Cases Where the Collation Determination Is Tricky ... 525
10.3.12. Collations Must Be for the Right Character Set ............................ 526
10.3.13. An Example of the Effect of Collation ........................................ 527
10.4. Operations Affected by Character Set Support .......................................... 528
10.4.1. Result Strings .......................................................................... 528
10.4.2. CONVERT() ........................................................................... 528
10.4.3. CAST() ................................................................................. 529
10.4.4. SHOW Statements ..................................................................... 529
MySQL Reference Manual
viii
10.5. Unicode Support ................................................................................ 530
10.6. UTF8 for Metadata ............................................................................. 531
10.7. Compatibility with Other DBMSs .......................................................... 532
10.8. New Character Set Configuration File Format .......................................... 532
10.9. National Character Set ......................................................................... 532
10.10. Upgrading Character Sets from MySQL 4.0 ........................................... 533
10.10.1. 4.0 Character Sets and Corresponding 4.1 Character Set/Collation Pairs
........................................................................................................ 534
10.10.2. Converting 4.0 Character Columns to 4.1 Format ......................... 534
10.11. Character Sets and Collations That MySQL Supports ............................... 535
10.11.1. Unicode Character Sets ............................................................ 536
10.11.2. West European Character Sets .................................................. 538
10.11.3. Central European Character Sets ............................................... 540
10.11.4. South European and Middle East Character Sets ........................... 540
10.11.5. Baltic Character Sets ............................................................... 541
10.11.6. Cyrillic Character Sets ............................................................. 541
10.11.7. Asian Character Sets ............................................................... 542
11. Column Types .............................................................................................. 544
11.1. Column Type Overview ....................................................................... 544
11.1.1. Overview of Numeric Types ...................................................... 544
11.1.2. Overview of Date and Time Types .............................................. 547#p#分頁標題#e#
11.1.3. Overview of String Types .......................................................... 548
11.2. Numeric Types ................................................................................... 551
11.3. Date and Time Types .......................................................................... 553
11.3.1. The DATETIME, DATE, and TIMESTAMP Types ........................... 554
11.3.2. The TIME Type ....................................................................... 560
11.3.3. The YEAR Type ....................................................................... 561
11.3.4. Y2K Issues and Date Types ....................................................... 561
11.4. String Types ...................................................................................... 562
11.4.1. The CHAR and VARCHAR Types ................................................. 562
11.4.2. The BINARY and VARBINARY Types ......................................... 563
11.4.3. The BLOB and TEXT Types ....................................................... 563
11.4.4. The ENUM Type ....................................................................... 564
11.4.5. The SET Type ......................................................................... 566
11.5. Column Type Storage Requirements ...................................................... 567
11.6. Choosing the Right Type for a Column ................................................... 568
11.7. Using Column Types from Other Database Engines .................................. 569
12. Functions and Operators ................................................................................. 570
12.1. Operators .......................................................................................... 570
12.1.1. Operator Precedence ................................................................. 570
12.1.2. Parentheses ............................................................................. 571
12.1.3. Comparison Functions and Operators ........................................... 571
12.1.4. Logical Operators ..................................................................... 577
12.2. Control Flow Functions ....................................................................... 578
12.3. String Functions ................................................................................. 580
12.3.1. String Comparison Functions ..................................................... 592
12.4. Numeric Functions .............................................................................. 595
12.4.1. Arithmetic Operators ................................................................ 595
12.4.2. Mathematical Functions ............................................................ 596
12.5. Date and Time Functions ..................................................................... 604#p#分頁標題#e#
12.6. Full-Text Search Functions ................................................................... 623
12.6.1. Boolean Full-Text Searches ....................................................... 625
12.6.2. Full-Text Searches with Query Expansion ..................................... 627
12.6.3. Full-Text Restrictions ............................................................... 628
12.6.4. Fine-Tuning MySQL Full-Text Search ......................................... 628
12.6.5. Full-Text Search TODO ............................................................ 630
12.7. Cast Functions and Operators ................................................................ 630
12.8. Other Functions .................................................................................. 633
12.8.1. Bit Functions ........................................................................... 633
12.8.2. Encryption Functions ................................................................ 635
12.8.3. Information Functions ............................................................... 638
12.8.4. Miscellaneous Functions ........................................................... 644
12.9. Functions and Modifiers for Use with GROUP BY Clauses ......................... 647
MySQL Reference Manual
ix
12.9.1. GROUP BY (Aggregate) Functions .............................................. 647
12.9.2. GROUP BY Modifiers ............................................................... 650
12.9.3. GROUP BY with Hidden Fields .................................................. 652
13. SQL Statement Syntax ................................................................................... 654
13.1. Data Manipulation Statements ............................................................... 654
13.1.1. DELETE Syntax ....................................................................... 654
13.1.2. DO Syntax ............................................................................... 656
13.1.3. HANDLER Syntax ..................................................................... 657
13.1.4. INSERT Syntax ....................................................................... 658
13.1.5. LOAD DATA INFILE Syntax ................................................... 664
13.1.6. REPLACE Syntax ..................................................................... 671
13.1.7. SELECT Syntax ....................................................................... 672
13.1.8. Subquery Syntax ...................................................................... 681
13.1.9. TRUNCATE Syntax ................................................................... 691
13.1.10. UPDATE Syntax ..................................................................... 691
13.2. Data Definition Statements ................................................................... 693#p#分頁標題#e#
13.2.1. ALTER DATABASE Syntax ....................................................... 693
13.2.2. ALTER TABLE Syntax ............................................................. 693
13.2.3. ALTER VIEW Syntax ............................................................... 698
13.2.4. CREATE DATABASE Syntax ..................................................... 698
13.2.5. CREATE INDEX Syntax ........................................................... 698
13.2.6. CREATE TABLE Syntax ........................................................... 700
13.2.7. CREATE VIEW Syntax ............................................................. 710
13.2.8. DROP DATABASE Syntax ......................................................... 712
13.2.9. DROP INDEX Syntax ............................................................... 712
13.2.10. DROP TABLE Syntax ............................................................. 713
13.2.11. DROP VIEW Syntax ............................................................... 713
13.2.12. RENAME TABLE Syntax ......................................................... 713
13.3. MySQL Utility Statements ................................................................... 714
13.3.1. DESCRIBE Syntax (Get Information About Columns) .................... 714
13.3.2. USE Syntax ............................................................................. 715
13.4. MySQL Transactional and Locking Statements ........................................ 715
13.4.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax ............ 715
13.4.2. Statements That Cannot Be Rolled Back ....................................... 716
13.4.3. Statements That Cause an Implicit Commit ................................... 717
13.4.4. SAVEPOINT and ROLLBACK TO SAVEPOINT Syntax ................ 717
13.4.5. LOCK TABLES and UNLOCK TABLES Syntax ............................ 717
13.4.6. SET TRANSACTION Syntax ..................................................... 720
13.5. Database Administration Statements ...................................................... 720
13.5.1. Account Management Statements ................................................ 720
13.5.2. Table Maintenance Statements .................................................... 729
13.5.3. SET Syntax ............................................................................. 734
13.5.4. SHOW Syntax ........................................................................... 738
13.5.5. Other Administrative Statements ................................................. 754
13.6. Replication Statements ........................................................................ 758
13.6.1. SQL Statements for Controlling Master Servers ............................. 758
13.6.2. SQL Statements for Controlling Slave Servers ............................... 760
13.7. SQL Syntax for Prepared Statements ...................................................... 768#p#分頁標題#e#
14. MySQL Storage Engines and Table Types ......................................................... 771
14.1. The MyISAM Storage Engine ................................................................ 773
14.1.1. MyISAM Startup Options ........................................................... 774
14.1.2. Space Needed for Keys ............................................................. 776
14.1.3. MyISAM Table Storage Formats ................................................. 776
14.1.4. MyISAM Table Problems ........................................................... 778
14.2. The MERGE Storage Engine .................................................................. 780
14.2.1. MERGE Table Problems ............................................................. 782
14.3. The MEMORY (HEAP) Storage Engine ..................................................... 783
14.4. The BDB (BerkeleyDB) Storage Engine ............................................... 785
14.4.1. Operating Systems Supported by BDB .......................................... 785
14.4.2. Installing BDB ......................................................................... 786
14.4.3. BDB Startup Options ................................................................. 786
14.4.4. Characteristics of BDB Tables ..................................................... 787
14.4.5. Things We Need to Fix for BDB .................................................. 789
MySQL Reference Manual
x
14.4.6. Restrictions on BDB Tables ........................................................ 789
14.4.7. Errors That May Occur When Using BDB Tables ........................... 789
14.5. The EXAMPLE Storage Engine .............................................................. 790
14.6. The FEDERATED Storage Engine .......................................................... 790
14.6.1. Installing the FEDERATED Storage Engine ................................... 790
14.6.2. Description of the FEDERATED Storage Engine ............................ 790
14.6.3. How to use FEDERATED Tables ................................................. 791
14.6.4. Limitations of the FEDERATED Storage Engine ............................. 792
14.7. The ARCHIVE Storage Engine .............................................................. 792
14.8. The CSV Storage Engine ...................................................................... 793
14.9. The ISAM Storage Engine .................................................................... 793
15. The InnoDB Storage Engine .......................................................................... 795
15.1. InnoDB Overview ............................................................................. 795
15.2. InnoDB Contact Information ............................................................... 795
15.3. InnoDB in MySQL 3.23 ..................................................................... 795#p#分頁標題#e#
15.4. InnoDB Configuration ........................................................................ 796
15.5. InnoDB Startup Options ..................................................................... 800
15.6. Creating the InnoDB Tablespace .......................................................... 805
15.6.1. Dealing with InnoDB Initialization Problems ............................... 806
15.7. Creating InnoDB Tables ..................................................................... 807
15.7.1. How to Use Transactions in InnoDB with Different APIs ............... 807
15.7.2. Converting MyISAM Tables to InnoDB ....................................... 808
15.7.3. How an AUTO_INCREMENT Column Works in InnoDB ................ 809
15.7.4. FOREIGN KEY Constraints ....................................................... 809
15.7.5. InnoDB and MySQL Replication ............................................... 813
15.7.6. Using Per-Table Tablespaces ...................................................... 813
15.8. Adding and Removing InnoDB Data and Log Files .................................. 815
15.9. Backing Up and Recovering an InnoDB Database ................................... 816
15.9.1. Forcing Recovery ..................................................................... 817
15.9.2. Checkpoints ............................................................................ 818
15.10. Moving an InnoDB Database to Another Machine ................................. 819
15.11. InnoDB Transaction Model and Locking .............................................. 819
15.11.1. InnoDB and AUTOCOMMIT ..................................................... 819
15.11.2. InnoDB and TRANSACTION ISOLATION LEVEL ................... 820
15.11.3. Consistent Non-Locking Read .................................................. 821
15.11.4. Locking Reads SELECT ... FOR UPDATE and SELECT ...
LOCK IN SHARE MODE ................................................................... 821
15.11.5. Next-Key Locking: Avoiding the Phantom Problem ...................... 822
15.11.6. An Example of How the Consistent Read Works in InnoDB .......... 823
15.11.7. Locks Set by Different SQL Statements in InnoDB ...................... 824
15.11.8. When Does MySQL Implicitly Commit or Roll Back a Transaction? 825
15.11.9. Deadlock Detection and Rollback .............................................. 825
15.11.10. How to Cope with Deadlocks .................................................. 826
15.12. InnoDB Performance Tuning Tips ...................................................... 827
15.12.1. SHOW INNODB STATUS and the InnoDB Monitors ................... 828
15.13. Implementation of Multi-Versioning ..................................................... 832
15.14. Table and Index Structures ................................................................. 833#p#分頁標題#e#
15.14.1. Physical Structure of an Index ................................................... 834
15.14.2. Insert Buffering ...................................................................... 834
15.14.3. Adaptive Hash Indexes ............................................................ 834
15.14.4. Physical Record Structure ........................................................ 834
15.15. File Space Management and Disk I/O ................................................... 835
15.15.1. Disk I/O ............................................................................... 835
15.15.2. Using Raw Devices for the Tablespace ....................................... 835
15.15.3. File Space Management ........................................................... 836
15.15.4. Defragmenting a Table ............................................................ 837
15.16. Error Handling ................................................................................. 837
15.16.1. InnoDB Error Codes .............................................................. 837
15.16.2. Operating System Error Codes .................................................. 838
15.17. Restrictions on InnoDB Tables ........................................................... 843
15.18. InnoDB Troubleshooting ................................................................... 845
15.18.1. Troubleshooting InnoDB Data Dictionary Operations .................. 845
16. MySQL Cluster ............................................................................................ 847
MySQL Reference Manual
xi
16.1. MySQL Cluster Glossary ..................................................................... 847
16.2. MySQL Cluster Overview .................................................................... 850
16.3. Basic MySQL Cluster Concepts ............................................................ 850
16.4. Simple Multi-Computer How-To ........................................................... 851
16.4.1. Hardware, Software, and Networking ........................................... 853
16.4.2. Installation .............................................................................. 853
16.4.3. Configuration .......................................................................... 855
16.4.4. Initial Startup .......................................................................... 856
16.4.5. Loading Sample Data and Performing Queries ............................... 857
16.4.6. Safe Shutdown and Restart ........................................................ 860
16.5. MySQL Cluster Configuration .............................................................. 861
16.5.1. Building from Source Code ........................................................ 861
16.5.2. Installing the Software .............................................................. 861#p#分頁標題#e#
16.5.3. Quick Test Setup of MySQL Cluster ............................................ 862
16.5.4. Configuration File .................................................................... 864
16.6. Process Management in MySQL Cluster ................................................. 887
16.6.1. MySQL Server Process Usage for MySQL Cluster ......................... 887
16.6.2. ndbd, the Storage Engine Node Process ....................................... 888
16.6.3. ndb_mgmd, the Management Server Process ................................ 889
16.6.4. ndb_mgm, the Management Client Process .................................. 890
16.6.5. Command Options for MySQL Cluster Processes ........................... 890
16.7. Management of MySQL Cluster ............................................................ 892
16.7.1. Commands in the Management Client .......................................... 892
16.7.2. Event Reports Generated in MySQL Cluster .................................. 893
16.7.3. Single User Mode .................................................................... 898
16.7.4. On-line Backup of MySQL Cluster .............................................. 899
16.8. Using High-Speed Interconnects with MySQL Cluster ............................... 901
16.8.1. Configuring MySQL Cluster to use SCI Sockets ............................ 901
16.8.2. Understanding the Impact of Cluster Interconnects ......................... 905
16.9. Cluster Limitations in MySQL 4.1 ......................................................... 906
16.10. MySQL Cluster FAQ ......................................................................... 908
17. Introduction to MaxDB .................................................................................. 914
17.1. History of MaxDB .............................................................................. 914
17.2. Licensing and Support ......................................................................... 914
17.3. MaxDB-Related Links ......................................................................... 914
17.4. Basic Concepts of MaxDB ................................................................... 914
17.5. Feature Differences Between MaxDB and MySQL ................................... 914
17.6. Interoperability Features Between MaxDB and MySQL ............................. 915
17.7. Reserved Words in MaxDB .................................................................. 915
18. Spatial Extensions in MySQL .......................................................................... 919
18.1. Introduction ....................................................................................... 919
18.2. The OpenGIS Geometry Model ............................................................. 920
18.2.1. The Geometry Class Hierarchy ................................................... 920#p#分頁標題#e#
18.2.2. Class Geometry ..................................................................... 921
18.2.3. Class Point ........................................................................... 922
18.2.4. Class Curve ........................................................................... 922
18.2.5. Class LineString ................................................................. 923
18.2.6. Class Surface ....................................................................... 923
18.2.7. Class Polygon ....................................................................... 923
18.2.8. Class GeometryCollection ................................................. 924
18.2.9. Class MultiPoint ................................................................. 924
18.2.10. Class MultiCurve ............................................................... 924
18.2.11. Class MultiLineString ..................................................... 925
18.2.12. Class MultiSurface ........................................................... 925
18.2.13. Class MultiPolygon ........................................................... 925
18.3. Supported Spatial Data Formats ............................................................ 926
18.3.1. Well-Known Text (WKT) Format ............................................... 926
18.3.2. Well-Known Binary (WKB) Format ............................................ 927
18.4. Creating a Spatially Enabled MySQL Database ........................................ 928
18.4.1. MySQL Spatial Data Types ........................................................ 928
18.4.2. Creating Spatial Values ............................................................. 928
18.4.3. Creating Spatial Columns .......................................................... 932
18.4.4. Populating Spatial Columns ....................................................... 932
MySQL Reference Manual
xii
18.4.5. Fetching Spatial Data ................................................................ 933
18.5. Analyzing Spatial Information .............................................................. 933
18.5.1. Geometry Format Conversion Functions ....................................... 934
18.5.2. Geometry Functions ............................................................... 935
18.5.3. Functions That Create New Geometries from Existing Ones ............. 941
18.5.4. Functions for Testing Spatial Relations Between Geometric Objects .. 943
18.5.5. Relations on Geometry Minimal Bounding Rectangles (MBRs) ........ 943
18.5.6. Functions That Test Spatial Relationships Between Geometries ........ 944
18.6. Optimizing Spatial Analysis ................................................................. 945
18.6.1. Creating Spatial Indexes ............................................................ 946
18.6.2. Using a Spatial Index ................................................................ 947#p#分頁標題#e#
18.7. MySQL Conformance and Compatibility ................................................ 948
18.7.1. GIS Features That Are Not Yet Implemented ................................ 948
19. Stored Procedures and Functions ...................................................................... 949
19.1. Stored Procedure Syntax ...................................................................... 949
19.1.1. Maintaining Stored Procedures ................................................... 950
19.1.2. SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS .... 953
19.1.3. CALL Statement ....................................................................... 953
19.1.4. BEGIN ... END Compound Statement ..................................... 953
19.1.5. DECLARE Statement ................................................................. 953
19.1.6. Variables in Stored Procedures ................................................... 954
19.1.7. Conditions and Handlers ............................................................ 954
19.1.8. Cursors .................................................................................. 955
19.1.9. Flow Control Constructs ............................................................ 957
20. Triggers ...................................................................................................... 960
20.1. CREATE TRIGGER Syntax ................................................................. 960
20.2. DROP TRIGGER Syntax ..................................................................... 960
20.3. Using Triggers ................................................................................... 961
21. The INFORMATION_SCHEMA Information Database .......................................... 964
21.1. INFORMATION_SCHEMA Tables ......................................................... 965
21.1.1. The INFORMATION_SCHEMA SCHEMATA Table ........................ 966
21.1.2. The INFORMATION_SCHEMA TABLES Table ............................ 966
21.1.3. The INFORMATION_SCHEMA COLUMNS Table .......................... 967
21.1.4. The INFORMATION_SCHEMA STATISTICS Table .................... 969
21.1.5. The INFORMATION_SCHEMA USER_PRIVILEGES Table .......... 970
21.1.6. The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table ...... 970
21.1.7. The INFORMATION_SCHEMA TABLE_PRIVILEGES Table ........ 970
21.1.8. The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table ...... 971
21.1.9. The INFORMATION_SCHEMA CHARACTER_SETS Table ............ 972
21.1.10. The INFORMATION_SCHEMA COLLATIONS Table ................... 972
21.1.11. The INFORMATION_SCHEMA COLLATION_
CHARACTER_SET_APPLICABILITY Table .............................. 973
21.1.12. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table ..... 973
21.1.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table ....... 974
21.1.14. The INFORMATION_SCHEMA ROUTINES Table ....................... 975#p#分頁標題#e#
21.1.15. The INFORMATION_SCHEMA VIEWS Table ............................. 976
21.1.16. Other INFORMATION_SCHEMA Tables ..................................... 977
21.2. Extensions to SHOW Statements ............................................................. 977
22. MySQL APIs ............................................................................................... 979
22.1. MySQL Program Development Utilities .................................................. 979
22.1.1. msql2mysql, Convert mSQL Programs for Use with MySQL ........... 979
22.1.2. mysql_config, Get compile options for compiling clients ................ 979
22.2. MySQL C API ................................................................................... 980
22.2.1. C API Data types ..................................................................... 981
22.2.2. C API Function Overview .......................................................... 984
22.2.3. C API Function Descriptions ...................................................... 987
22.2.4. C API Prepared Statements ...................................................... 1027
22.2.5. C API Prepared Statement Data types ........................................ 1027
22.2.6. C API Prepared Statement Function Overview ............................. 1030
22.2.7. C API Prepared Statement Function Descriptions ......................... 1032
22.2.8. C API Prepared statement problems ........................................... 1053
22.2.9. C API Handling of Multiple Query Execution .............................. 1053
22.2.10. C API Handling of Date and Time Values ................................. 1054
MySQL Reference Manual
xiii
22.2.11. C API Threaded Function Descriptions ..................................... 1055
22.2.12. C API Embedded Server Function Descriptions .......................... 1056
22.2.13. Common questions and problems when using the C API .............. 1057
22.2.14. Building Client Programs ....................................................... 1059
22.2.15. How to Make a Threaded Client .............................................. 1059
22.2.16. libmysqld, the Embedded MySQL Server Library ....................... 1061
22.3. MySQL PHP API ............................................................................. 1065
22.3.1. Common Problems with MySQL and PHP .................................. 1065
22.4. MySQL Perl API .............................................................................. 1065
22.5. MySQL C++ API ............................................................................. 1066
22.5.1. Borland C++ ......................................................................... 1066
22.6. MySQL Python API .......................................................................... 1066
22.7. MySQL Tcl API ............................................................................... 1066#p#分頁標題#e#
22.8. MySQL Eiffel Wrapper ..................................................................... 1066
23. MySQL Connectors ..................................................................................... 1067
23.1. MySQL ODBC Support ..................................................................... 1067
23.1.1. Introduction to MyODBC ........................................................ 1067
23.1.2. General Information About ODBC and MyODBC ........................ 1069
23.1.3. How to Install MyODBC ......................................................... 1072
23.1.4. Installing MyODBC from a Binary Distribution on Windows ......... 1072
23.1.5. Installing MyODBC from a Binary Distribution on Unix ............... 1073
23.1.6. Installing MyODBC from a Source Distribution on Windows ......... 1074
23.1.7. Installing MyODBC from a Source Distribution on Unix ............... 1075
23.1.8. Installing MyODBC from the BitKeeper Development Source Tree . 1081
23.1.9. MyODBC Configuration ......................................................... 1082
23.1.10. MyODBC Connection-Related Issues ....................................... 1099
23.1.11. MyODBC and Microsoft Access ............................................. 1099
23.1.12. MyODBC and Microsoft VBA and ASP ................................... 1104
23.1.13. MyODBC and Third-Party ODBC Tools ................................... 1106
23.1.14. MyODBC General Functionality ............................................. 1106
23.1.15. Basic MyODBC Application Steps .......................................... 1110
23.1.16. MyODBC API Reference ....................................................... 1111
23.1.17. MyODBC Data Types ........................................................... 1115
23.1.18. MyODBC Error Codes .......................................................... 1116
23.1.19. MyODBC With VB: ADO, DAO and RDO ............................... 1118
23.1.20. MyODBC with Microsoft .NET .............................................. 1121
23.1.21. Credits ............................................................................... 1124
23.2. MySQL Java Connectivity (JDBC) ...................................................... 1124
24. Error Handling in MySQL ............................................................................ 1125
25. Extending MySQL ...................................................................................... 1155
25.1. MySQL Internals .............................................................................. 1155
25.1.1. MySQL Threads .................................................................... 1155
25.1.2. MySQL Test Suite ................................................................. 1155
25.2. Adding New Functions to MySQL ....................................................... 1158
25.2.1. CREATE FUNCTION/DROP FUNCTION Syntax ....................... 1158#p#分頁標題#e#
25.2.2. Adding a New User-defined Function ........................................ 1159
25.2.3. Adding a New Native Function ................................................. 1166
25.3. Adding New Procedures to MySQL ..................................................... 1167
25.3.1. Procedure Analyse ................................................................. 1168
25.3.2. Writing a Procedure ................................................................ 1168
A. Problems and Common Errors ........................................................................ 1169
A.1. How to Determine What Is Causing a Problem ........................................ 1169
A.2. Common Errors When Using MySQL Programs ...................................... 1170
A.2.1. Access denied .................................................................. 1170
A.2.2. Can't connect to [local] MySQL server .................. 1170
A.2.3. Client does not support authentication protocol 1172
A.2.4. Password Fails When Entered Interactively .................................. 1173
A.2.5. Host 'host_name' is blocked ...................................... 1173
A.2.6. Too many connections .................................................... 1173
A.2.7. Out of memory .................................................................. 1174
A.2.8. MySQL server has gone away ........................................ 1174
A.2.9. Packet too large ............................................................ 1176
A.2.10. Communication Errors and Aborted Connections ........................ 1177
MySQL Reference Manual
xiv
A.2.11. The table is full ........................................................ 1178
A.2.12. Can't create/write to file ...................................... 1178
A.2.13. Commands out of sync .................................................. 1179
A.2.14. Ignoring user ................................................................ 1179
A.2.15. Table 'tbl_name' doesn't exist .............................. 1179
A.2.16. Can't initialize character set .............................. 1180
A.2.17. File Not Found ...................................................................... 1180
A.3. Installation-Related Issues ................................................................... 1181
A.3.1. Problems Linking to the MySQL Client Library ............................ 1181
A.3.2. How to Run MySQL as a Normal User ....................................... 1182
A.3.3. Problems with File Permissions ................................................. 1183
A.4. Administration-Related Issues .............................................................. 1183
A.4.1. How to Reset the Root Password ................................................ 1183
A.4.2. What to Do If MySQL Keeps Crashing ....................................... 1185#p#分頁標題#e#
A.4.3. How MySQL Handles a Full Disk .............................................. 1187
A.4.4. Where MySQL Stores Temporary Files ....................................... 1188
A.4.5. How to Protect or Change the MySQL Socket File /tmp/mysql.sock
...................................................................................................... 1188
A.4.6. Time Zone Problems ............................................................... 1189
A.5. Query-Related Issues ......................................................................... 1189
A.5.1. Case Sensitivity in Searches ...................................................... 1189
A.5.2. Problems Using DATE Columns ................................................ 1190
A.5.3. Problems with NULL Values ..................................................... 1191
A.5.4. Problems with Column Aliases .................................................. 1192
A.5.5. Rollback Failure for Non-Transactional Tables ............................. 1192
A.5.6. Deleting Rows from Related Tables ............................................ 1193
A.5.7. Solving Problems with No Matching Rows .................................. 1193
A.5.8. Problems with Floating-Point Comparisons .................................. 1194
A.6. Optimizer-Related Issues .................................................................... 1196
A.7. Table Definition-Related Issues ............................................................ 1196
A.7.1. Problems with ALTER TABLE ................................................. 1196
A.7.2. How to Change the Order of Columns in a Table ........................... 1197
A.7.3. TEMPORARY TABLE Problems ................................................ 1198
B. Credits ....................................................................................................... 1199
B.1. Developers at MySQL AB ................................................................... 1199
B.2. Contributors to MySQL ...................................................................... 1203
B.3. Documenters and translators ................................................................ 1208
B.4. Libraries used by and included with MySQL ........................................... 1209
B.5. Packages that support MySQL ............................................................. 1210
B.6. Tools that were used to create MySQL ................................................... 1210
B.7. Supporters of MySQL ........................................................................ 1211
C. MySQL and the Future (the TODO) ................................................................. 1212
C.1. New Features Planned for 5.0 ............................................................... 1212
C.2. New Features Planned for 5.1 ............................................................... 1213#p#分頁標題#e#
C.3. New Features Planned for the Near Future .............................................. 1214
C.4. New Features Planned for the Mid-Term Future ...................................... 1216
C.5. New Features We Don't Plan to Implement ............................................. 1217
D. MySQL Change History ................................................................................ 1218
D.1. Changes in release 5.0.x (Development) ................................................. 1218
D.1.1. Changes in release 5.0.4 (not released yet) ................................... 1219
D.1.2. Changes in release 5.0.3 (release imminent) ................................. 1219
D.1.3. Changes in release 5.0.2 (01 Dec 2004) ....................................... 1225
D.1.4. Changes in release 5.0.1 (27 Jul 2004) ........................................ 1227
D.1.5. Changes in release 5.0.0 (22 Dec 2003: Alpha) ............................. 1231
D.2. Changes in release 4.1.x (Production) .................................................... 1232
D.2.1. Changes in release 4.1.11 (not released yet) ................................. 1233
D.2.2. Changes in release 4.1.10 (12 Feb 2005) ...................................... 1234
D.2.3. Changes in release 4.1.9 (11 Jan 2005) ........................................ 1238
D.2.4. Changes in release 4.1.8 (14 Dec 2004) ....................................... 1240
D.2.5. Changes in release 4.1.7 (23 Oct 2004: Production) ....................... 1244
D.2.6. Changes in release 4.1.6 (10 Oct 2004) ........................................ 1245
D.2.7. Changes in release 4.1.5 (16 Sep 2004) ....................................... 1247
D.2.8. Changes in release 4.1.4 (26 Aug 2004: Gamma) .......................... 1248
MySQL Reference Manual
xv
D.2.9. Changes in release 4.1.3 (28 Jun 2004: Beta) ................................ 1250
D.2.10. Changes in release 4.1.2 (28 May 2004) ..................................... 1253
D.2.11. Changes in release 4.1.1 (01 Dec 2003) ..................................... 1262
D.2.12. Changes in release 4.1.0 (03 Apr 2003: Alpha) ............................ 1267
D.3. Changes in release 4.0.x (Production) .................................................... 1270
D.3.1. Changes in release 4.0.24 (not released yet) ................................. 1271
D.3.2. Changes in release 4.0.23 (18 Dec 2004) ..................................... 1272
D.3.3. Changes in release 4.0.22 (27 Oct 2004) ...................................... 1274
D.3.4. Changes in release 4.0.21 (06 Sep 2004) ...................................... 1276
D.3.5. Changes in release 4.0.20 (17 May 2004) ..................................... 1278
D.3.6. Changes in release 4.0.19 (04 May 2004) ..................................... 1278
D.3.7. Changes in release 4.0.18 (12 Feb 2004) ...................................... 1282#p#分頁標題#e#
D.3.8. Changes in release 4.0.17 (14 Dec 2003) ..................................... 1285
D.3.9. Changes in release 4.0.16 (17 Oct 2003) ...................................... 1288
D.3.10. Changes in release 4.0.15 (03 Sep 2003) .................................... 1290
D.3.11. Changes in release 4.0.14 (18 Jul 2003) ..................................... 1294
D.3.12. Changes in release 4.0.13 (16 May 2003) ................................... 1297
D.3.13. Changes in release 4.0.12 (15 Mar 2003: Production) ................... 1301
D.3.14. Changes in release 4.0.11 (20 Feb 2003) .................................... 1303
D.3.15. Changes in release 4.0.10 (29 Jan 2003) ..................................... 1304
D.3.16. Changes in release 4.0.9 (09 Jan 2003) ...................................... 1305
D.3.17. Changes in release 4.0.8 (07 Jan 2003) ...................................... 1305
D.3.18. Changes in release 4.0.7 (20 Dec 2002) ..................................... 1306
D.3.19. Changes in release 4.0.6 (14 Dec 2002: Gamma) ......................... 1306
D.3.20. Changes in release 4.0.5 (13 Nov 2002) ..................................... 1308
D.3.21. Changes in release 4.0.4 (29 Sep 2002) ...................................... 1310
D.3.22. Changes in release 4.0.3 (26 Aug 2002: Beta) ............................. 1312
D.3.23. Changes in release 4.0.2 (01 Jul 2002) ....................................... 1314
D.3.24. Changes in release 4.0.1 (23 Dec 2001) ..................................... 1317
D.3.25. Changes in release 4.0.0 (Oct 2001: Alpha) ................................ 1318
D.4. Changes in release 3.23.x (Recent; still supported) ................................... 1320
D.4.1. Changes in release 3.23.59 (not released yet) ................................ 1321
D.4.2. Changes in release 3.23.58 (11 Sep 2003) .................................... 1322
D.4.3. Changes in release 3.23.57 (06 Jun 2003) .................................... 1322
D.4.4. Changes in release 3.23.56 (13 Mar 2003) ................................... 1323
D.4.5. Changes in release 3.23.55 (23 Jan 2003) ..................................... 1324
D.4.6. Changes in release 3.23.54 (05 Dec 2002) .................................... 1325
D.4.7. Changes in release 3.23.53 (09 Oct 2002) .................................... 1326
D.4.8. Changes in release 3.23.52 (14 Aug 2002) ................................... 1326
D.4.9. Changes in release 3.23.51 (31 May 2002) ................................... 1327
D.4.10. Changes in release 3.23.50 (21 Apr 2002) .................................. 1328
D.4.11. Changes in release 3.23.49 (14 Feb 2002) .................................. 1329
D.4.12. Changes in release 3.23.48 (07 Feb 2002) .................................. 1329
D.4.13. Changes in release 3.23.47 (27 Dec 2001) .................................. 1330#p#分頁標題#e#
D.4.14. Changes in release 3.23.46 (29 Nov 2001) .................................. 1331
D.4.15. Changes in release 3.23.45 (22 Nov 2001) .................................. 1331
D.4.16. Changes in release 3.23.44 (31 Oct 2001) ................................... 1331
D.4.17. Changes in release 3.23.43 (04 Oct 2001) ................................... 1333
D.4.18. Changes in release 3.23.42 (08 Sep 2001) .................................. 1333
D.4.19. Changes in release 3.23.41 (11 Aug 2001) .................................. 1334
D.4.20. Changes in release 3.23.40 (18 Jul 2001) ................................... 1334
D.4.21. Changes in release 3.23.39 (12 Jun 2001) ................................... 1335
D.4.22. Changes in release 3.23.38 (09 May 2001) ................................. 1336
D.4.23. Changes in release 3.23.37 (17 Apr 2001) .................................. 1336
D.4.24. Changes in release 3.23.36 (27 Mar 2001) .................................. 1337
D.4.25. Changes in release 3.23.35 (15 Mar 2001) .................................. 1338
D.4.26. Changes in release 3.23.34a (11 Mar 2001) ................................ 1338
D.4.27. Changes in release 3.23.34 (10 Mar 2001) .................................. 1338
D.4.28. Changes in release 3.23.33 (09 Feb 2001) .................................. 1339
D.4.29. Changes in release 3.23.32 (22 Jan 2001) ................................... 1341
D.4.30. Changes in release 3.23.31 (17 Jan 2001: Production) ................... 1341
D.4.31. Changes in release 3.23.30 (04 Jan 2001) ................................... 1342
D.4.32. Changes in release 3.23.29 (16 Dec 2000) .................................. 1343
MySQL Reference Manual
xvi
D.4.33. Changes in release 3.23.28 (22 Nov 2000: Gamma) ..................... 1344
D.4.34. Changes in release 3.23.27 (24 Oct 2000) ................................... 1346
D.4.35. Changes in release 3.23.26 (18 Oct 2000) ................................... 1346
D.4.36. Changes in release 3.23.25 (29 Sep 2000) .................................. 1347
D.4.37. Changes in release 3.23.24 (08 Sep 2000) .................................. 1349
D.4.38. Changes in release 3.23.23 (01 Sep 2000) .................................. 1349
D.4.39. Changes in release 3.23.22 (31 Jul 2000) ................................... 1350
D.4.40. Changes in release 3.23.21 (04 Jul 2000) ................................... 1351
D.4.41. Changes in release 3.23.20 (28 Jun 2000: Beta) ........................... 1352
D.4.42. Changes in release 3.23.19 ...................................................... 1352
D.4.43. Changes in release 3.23.18 (11 Jun 2000) ................................... 1352
D.4.44. Changes in release 3.23.17 (07 Jun 2000) ................................... 1353
D.4.45. Changes in release 3.23.16 (16 May 2000) ................................. 1353#p#分頁標題#e#
D.4.46. Changes in release 3.23.15 (08 May 2000) ................................. 1354
D.4.47. Changes in release 3.23.14 (09 Apr 2000) .................................. 1355
D.4.48. Changes in release 3.23.13 (14 Mar 2000) .................................. 1356
D.4.49. Changes in release 3.23.12 (07 Mar 2000) .................................. 1356
D.4.50. Changes in release 3.23.11 (16 Feb 2000) .................................. 1357
D.4.51. Changes in release 3.23.10 (30 Jan 2000) ................................... 1357
D.4.52. Changes in release 3.23.9 (29 Jan 2000) ..................................... 1357
D.4.53. Changes in release 3.23.8 (02 Jan 2000) ..................................... 1358
D.4.54. Changes in release 3.23.7 (10 Dec 1999) .................................... 1359
D.4.55. Changes in release 3.23.6 (15 Nov 1999) ................................... 1360
D.4.56. Changes in release 3.23.5 (20 Oct 1999) .................................... 1360
D.4.57. Changes in release 3.23.4 (28 Sep 1999) .................................... 1361
D.4.58. Changes in release 3.23.3 (13 Sep 1999) .................................... 1362
D.4.59. Changes in release 3.23.2 (09 Aug 1999) ................................... 1362
D.4.60. Changes in release 3.23.1 (08 Jul 1999) ..................................... 1363
D.4.61. Changes in release 3.23.0 (05 Jul 1999: Alpha) ........................... 1363
D.5. Changes in release 3.22.x (Old; discontinued) ......................................... 1365
D.5.1. Changes in release 3.22.35 ........................................................ 1366
D.5.2. Changes in release 3.22.34 ........................................................ 1366
D.5.3. Changes in release 3.22.33 ........................................................ 1366
D.5.4. Changes in release 3.22.32 (14 Feb 2000) .................................... 1366
D.5.5. Changes in release 3.22.31 ........................................................ 1366
D.5.6. Changes in release 3.22.30 (11 Jan 2000) ..................................... 1366
D.5.7. Changes in release 3.22.29 (02 Jan 2000) ..................................... 1367
D.5.8. Changes in release 3.22.28 (20 Oct 1999) .................................... 1367
D.5.9. Changes in release 3.22.27 (05 Oct 1999) .................................... 1367
D.5.10. Changes in release 3.22.26 (16 Sep 1999) .................................. 1367
D.5.11. Changes in release 3.22.25 (07 Jun 1999) ................................... 1368
D.5.12. Changes in release 3.22.24 (05 Jul 1999) ................................... 1368
D.5.13. Changes in release 3.22.23 (08 Jun 1999) ................................... 1368
D.5.14. Changes in release 3.22.22 (30 Apr 1999) .................................. 1369
D.5.15. Changes in release 3.22.21 (04 Apr 1999) .................................. 1369#p#分頁標題#e#
D.5.16. Changes in release 3.22.20 (18 Mar 1999) .................................. 1369
D.5.17. Changes in release 3.22.19 (01 Mar 1999) .................................. 1369
D.5.18. Changes in release 3.22.18 (26 Feb 1999) .................................. 1370
D.5.19. Changes in release 3.22.17 (22 Feb 1999: Production) .................. 1370
D.5.20. Changes in release 3.22.16 (05 Feb 1999) .................................. 1370
D.5.21. Changes in release 3.22.15 (27 Jan 1999) ................................... 1370
D.5.22. Changes in release 3.22.14 (01 Jan 1999: Gamma) ....................... 1371
D.5.23. Changes in release 3.22.13 (16 Dec 1998) .................................. 1371
D.5.24. Changes in release 3.22.12 (09 Dec 1998) .................................. 1371
D.5.25. Changes in release 3.22.11 (24 Nov 1998) .................................. 1372
D.5.26. Changes in release 3.22.10 (04 Nov 1998) .................................. 1373
D.5.27. Changes in release 3.22.9 (19 Oct 1998) .................................... 1373
D.5.28. Changes in release 3.22.8 (06 Oct 1998) .................................... 1374
D.5.29. Changes in release 3.22.7 (21 Sep 1998: Beta) ............................ 1374
D.5.30.留學生dissertation網 Changes in release 3.22.6 (31 Aug 1998) ................................... 1375
D.5.31. Changes in release 3.22.5 (20 Aug 1998: Alpha) ......................... 1375
D.5.32. Changes in release 3.22.4 (06 Jul 1998: Beta) ............................. 1377
D.5.33. Changes in release 3.22.3 (30 Jun 1998) .................................... 1377
MySQL Reference Manual
xvii
D.5.34. Changes in release 3.22.2 ........................................................ 1377
D.5.35. Changes in release 3.22.1 (Jun 1998) ......................................... 1378
D.5.36. Changes in release 3.22.0 (18 May 1998: Alpha) ......................... 1379
D.6. Changes in release 3.21.x .................................................................... 1380
D.6.1. Changes in release 3.21.33 (08 Jul 1998) ..................................... 1380
D.6.2. Changes in release 3.21.32 (30 Jun 1998) .................................... 1380
D.6.3. Changes in release 3.21.31 (10 Jun 1998) .................................... 1381
D.6.4. Changes in release 3.21.30 ........................................................ 1381
D.6.5. Changes in release 3.21.29 ........................................................ 1381
D.6.6. Changes in release 3.21.28 ........................................................ 1382
D.6.7. Changes in release 3.21.27 ........................................................ 1382
D.6.8. Changes in release 3.21.26 ........................................................ 1382
D.6.9. Changes in release 3.21.25 ........................................................ 1383#p#分頁標題#e#
D.6.10. Changes in release 3.21.24 ...................................................... 1383
D.6.11. Changes in release 3.21.23 ...................................................... 1383
D.6.12. Changes in release 3.21.22 ...................................................... 1384
D.6.13. Changes in release 3.21.21a ..................................................... 1385
D.6.14. Changes in release 3.21.21 ...................................................... 1385
D.6.15. Changes in release 3.21.20 ...................................................... 1385
D.6.16. Changes in release 3.21.19 ...................................................... 1385
D.6.17. Changes in release 3.21.18 ...................................................... 1385
D.6.18. Changes in release 3.21.17 ...................................................... 1386
D.6.19. Changes in release 3.21.16 ...................................................... 1386
D.6.20. Changes in release 3.21.15 ...................................................... 1387
D.6.21. Changes in release 3.21.14b .................................................... 1387
D.6.22. Changes in release 3.21.14a ..................................................... 1387
D.6.23. Changes in release 3.21.13 ...................................................... 1388
D.6.24. Changes in release 3.21.12 ...................................................... 1388
D.6.25. Changes in release 3.21.11 ...................................................... 1389
D.6.26. Changes in release 3.21.10 ...................................................... 1390
D.6.27. Changes in release 3.21.9 ........................................................ 1390
D.6.28. Changes in release 3.21.8 ........................................................ 1390
D.6.29. Changes in release 3.21.7 ........................................................ 1391
D.6.30. Changes in release 3.21.6 ........................................................ 1391
D.6.31. Changes in release 3.21.5 ........................................................ 1391
D.6.32. Changes in release 3.21.4 ........................................................ 1392
D.6.33. Changes in release 3.21.3 ........................................................ 1392
D.6.34. Changes in release 3.21.2 ........................................................ 1393
D.6.35. Changes in release 3.21.0 ........................................................ 1394
D.7. Changes in release 3.20.x .................................................................... 1395
D.7.1. Changes in release 3.20.18 ........................................................ 1395
D.7.2. Changes in release 3.20.17 ........................................................ 1395
D.7.3. Changes in release 3.20.16 ........................................................ 1396#p#分頁標題#e#
D.7.4. Changes in release 3.20.15 ........................................................ 1397
D.7.5. Changes in release 3.20.14 ........................................................ 1397
D.7.6. Changes in release 3.20.13 ........................................................ 1397
D.7.7. Changes in release 3.20.11 ........................................................ 1398
D.7.8. Changes in release 3.20.10 ........................................................ 1398
D.7.9. Changes in release 3.20.9 ......................................................... 1399
D.7.10. Changes in release 3.20.8 ........................................................ 1399
D.7.11. Changes in release 3.20.7 ........................................................ 1399
D.7.12. Changes in release 3.20.6 ........................................................ 1399
D.7.13. Changes in release 3.20.3 ........................................................ 1401
D.7.14. Changes in release 3.20.0 ........................................................ 1401
D.8. Changes in release 3.19.x .................................................................... 1402
D.8.1. Changes in release 3.19.5 ......................................................... 1402
D.8.2. Changes in release 3.19.4 ......................................................... 1402
D.8.3. Changes in release 3.19.3 ......................................................... 1403
D.9. InnoDB Change History .................................................................... 1403
D.9.1. MySQL/InnoDB-4.0.21, September 10, 2004 ............................... 1403
D.9.2. MySQL/InnoDB-4.1.4, August 31, 2004 ..................................... 1404
D.9.3. MySQL/InnoDB-4.1.3, June 28, 2004 ......................................... 1405
D.9.4. MySQL/InnoDB-4.1.2, May 30, 2004 ......................................... 1406
MySQL Reference Manual
xviii
D.9.5. MySQL/InnoDB-4.0.20, May 18, 2004 ....................................... 1407
D.9.6. MySQL/InnoDB-4.0.19, May 4, 2004 ......................................... 1407
D.9.7. MySQL/InnoDB-4.0.18, February 13, 2004 ................................. 1408
D.9.8. MySQL/InnoDB-5.0.0, December 24, 2003 ................................. 1409
D.9.9. MySQL/InnoDB-4.0.17, December 17, 2003 ................................ 1409
D.9.10. MySQL/InnoDB-4.1.1, December 4, 2003 ................................. 1410
D.9.11. MySQL/InnoDB-4.0.16, October 22, 2003 ................................. 1410
D.9.12. MySQL/InnoDB-3.23.58, September 15, 2003 ............................ 1410
D.9.13. MySQL/InnoDB-4.0.15, September 10, 2003 ............................. 1410
D.9.14. MySQL/InnoDB-4.0.14, July 22, 2003 ...................................... 1411
D.9.15. MySQL/InnoDB-3.23.57, June 20, 2003 .................................... 1412#p#分頁標題#e#
D.9.16. MySQL/InnoDB-4.0.13, May 20, 2003 ...................................... 1413
D.9.17. MySQL/InnoDB-4.1.0, April 3, 2003 ........................................ 1413
D.9.18. MySQL/InnoDB-3.23.56, March 17, 2003 ................................. 1414
D.9.19. MySQL/InnoDB-4.0.12, March 18, 2003 ................................... 1414
D.9.20. MySQL/InnoDB-4.0.11, February 25, 2003 ................................ 1414
D.9.21. MySQL/InnoDB-4.0.10, February 4, 2003 ................................. 1414
D.9.22. MySQL/InnoDB-3.23.55, January 24, 2003 ................................ 1415
D.9.23. MySQL/InnoDB-4.0.9, January 14, 2003 ................................... 1415
D.9.24. MySQL/InnoDB-4.0.8, January 7, 2003 ..................................... 1416
D.9.25. MySQL/InnoDB-4.0.7, December 26, 2002 ................................ 1416
D.9.26. MySQL/InnoDB-4.0.6, December 19, 2002 ................................ 1416
D.9.27. MySQL/InnoDB-3.23.54, December 12, 2002 ............................ 1416
D.9.28. MySQL/InnoDB-4.0.5, November 18, 2002 ............................... 1417
D.9.29. MySQL/InnoDB-3.23.53, October 9, 2002 ................................. 1418
D.9.30. MySQL/InnoDB-4.0.4, October 2, 2002 .................................... 1418
D.9.31. MySQL/InnoDB-4.0.3, August 28, 2002 .................................... 1419
D.9.32. MySQL/InnoDB-3.23.52, August 16, 2002 ................................ 1419
D.9.33. MySQL/InnoDB-4.0.2, July 10, 2002 ........................................ 1421
D.9.34. MySQL/InnoDB-3.23.51, June 12, 2002 .................................... 1421
D.9.35. MySQL/InnoDB-3.23.50, April 23, 2002 ................................... 1421
D.9.36. MySQL/InnoDB-3.23.49, February 17, 2002 .............................. 1422
D.9.37. MySQL/InnoDB-3.23.48, February 9, 2002 ................................ 1422
D.9.38. MySQL/InnoDB-3.23.47, December 28, 2001 ............................ 1423
D.9.39. MySQL/InnoDB-4.0.1, December 23, 2001 ................................ 1423
D.9.40. MySQL/InnoDB-3.23.46, November 30, 2001 ............................ 1424
D.9.41. MySQL/InnoDB-3.23.45, November 23, 2001 ............................ 1424
D.9.42. MySQL/InnoDB-3.23.44, November 2, 2001 .............................. 1424
D.9.43. MySQL/InnoDB-3.23.43, October 4, 2001 ................................. 1425
D.9.44. MySQL/InnoDB-3.23.42, September 9, 2001 ............................. 1425
D.9.45. MySQL/InnoDB-3.23.41, August 13, 2001 ................................ 1425
D.9.46. MySQL/InnoDB-3.23.40, July 16, 2001 ..................................... 1426
D.9.47. MySQL/InnoDB-3.23.39, June 13, 2001 .................................... 1426
D.9.48. MySQL/InnoDB-3.23.38, May 12, 2001 .................................... 1426
D.10. MySQL Cluster Change History ..................................................... 1426#p#分頁標題#e#
D.10.1. MySQL Cluster-4.1.10 (12 Feb 2005) ....................................... 1426
D.10.2. MySQL Cluster-4.1.9 (13 Jan 2005) .......................................... 1427
D.10.3. MySQL Cluster-4.1.8 (14 Dec 2004) ......................................... 1427
D.10.4. MySQL Cluster-4.1.7 (23 Oct 2004) ......................................... 1430
D.10.5. MySQL Cluster-4.1.6 (10 Oct 2004) ......................................... 1430
D.10.6. MySQL Cluster-4.1.5 (16 Sep 2004) ......................................... 1431
D.10.7. MySQL Cluster-4.1.4 (31 Aug 2004) ........................................ 1433
D.10.8. MySQL Cluster-5.0.1 (27 Jul 2004) .......................................... 1433
D.10.9. MySQL Cluster-4.1.3 (28 Jun 2004) ......................................... 1433
E. Porting to Other Systems ............................................................................... 1434
E.1. Debugging a MySQL Server ................................................................ 1435
E.1.1. Compiling MySQL for Debugging .............................................. 1435
E.1.2. Creating Trace Files ................................................................. 1436
E.1.3. Debugging mysqld under gdb ................................................... 1436
E.1.4. Using a Stack Trace ................................................................. 1437
E.1.5. Using Log Files to Find Cause of Errors in mysqld ........................ 1438
E.1.6. Making a Test Case If You Experience Table Corruption ................ 1439
E.2. Debugging a MySQL Client ................................................................. 1440
MySQL Reference Manual
xix
E.3. The DBUG Package ........................................................................... 1440
E.4. Comments about RTS Threads ............................................................. 1441
E.5. Differences Between Thread Packages ................................................... 1443
F. Environment Variables .................................................................................. 1444
G. MySQL Regular Expressions ......................................................................... 1445
H. 留學生dissertation網GNU General Public License ......................................................................... 1449
I. MySQL FLOSS License Exception ................................................................... 1454
Index ............................................................................................................. 1456