Mysql-5 1-en-complete(Ingles)

Prévia do material em texto

MySQL 5.1 Reference Manual
MySQL 5.1 Reference Manual
Abstract
This is the MySQL Reference Manual. It documents MySQL 5.1 through 5.1.12-beta.
Document generated on: 2006-10-18 (revision: 3672)
Copyright 1997-2006 MySQL AB
This documentation is NOT distributed under a GPL license. Use of this documentation is subject to the following terms: You may
create a printed copy of this documentation solely for your own personal use. Conversion to other formats is allowed as long as the
actual content is not altered or edited in any way. You shall not publish or distribute this documentation in any form or on any me-
dia, except if you distribute the documentation in a manner similar to how MySQL disseminates it (that is, electronically for down-
load on a website with the software) or on a CD-ROM or similar medium, provided however that the documentation is dissemin-
ated together with the software on the same medium. Any other use, such as any dissemination of printed copies or use of this doc-
umentation, in whole or in part, in another publication, requires the prior written consent from an authorized representative of
MySQL AB. MySQL AB reserves any and all rights to this documentation not expressly granted above.
Please email <docs@mysql.com> for more information.
Table of Contents
Preface .................................................................................................................. xxv
1. General Information ................................................................................................. 1
1.1. About This Manual ........................................................................................ 1
1.2. Conventions Used in This Manual .................................................................... 2
1.3. Overview of MySQL AB ................................................................................ 4
1.4. Overview of the MySQL Database Management System ...................................... 5
1.4.1. History of MySQL .............................................................................. 6
1.4.2. The Main Features of MySQL ............................................................... 6
1.4.3. MySQL Stability ................................................................................ 9
1.4.4. How Large MySQL Tables Can Be ...................................................... 10
1.4.5. Year 2000 Compliance ...................................................................... 11
1.5. Overview of the MaxDB Database Management System ..................................... 12
1.5.1. What is MaxDB? .............................................................................. 12
1.5.2. History of MaxDB ............................................................................ 13
1.5.3. Features of MaxDB ........................................................................... 13
1.5.4. Licensing and Support ....................................................................... 14
1.5.5. Feature Differences Between MaxDB and MySQL ................................. 14
1.5.6. Interoperability Features Between MaxDB and MySQL ........................... 15
1.5.7. MaxDB-Related Links ....................................................................... 15
1.6. MySQL Development Roadmap ..................................................................... 15
1.6.1. What's New in MySQL 5.1 ................................................................. 16
1.6.2. What's Planned for MySQL 5.2 ........................................................... 18
1.7. MySQL Information Sources ......................................................................... 18
1.7.1. MySQL Mailing Lists ........................................................................ 18
1.7.2. MySQL Community Support at the MySQL Forums ............................... 21
1.7.3. MySQL Community Support on Internet Relay Chat (IRC) ....................... 21
1.8. How to Report Bugs or Problems ................................................................... 21
1.9. MySQL Standards Compliance ...................................................................... 26
1.9.1. What Standards MySQL Follows ......................................................... 26
1.9.2. Selecting SQL Modes ........................................................................ 26
1.9.3. Running MySQL in ANSI Mode ......................................................... 27
1.9.4. MySQL Extensions to Standard SQL .................................................... 27
1.9.5. MySQL Differences from Standard SQL ............................................... 30
1.9.6. How MySQL Deals with Constraints .................................................... 36
2. Installing and Upgrading MySQL ............................................................................. 40
2.1. General Installation Issues ............................................................................ 40
2.1.1. Operating Systems On Which MySQL Is Known To Run ......................... 41
2.1.2. Choosing Which MySQL Distribution to Install ...................................... 43
2.1.3. How to Get MySQL .......................................................................... 53
2.1.4. Verifying Package Integrity Using MD5 Checksums or GnuPG ................. 53
2.1.5. Installation Layouts ........................................................................... 56
2.2. Standard MySQL Installation Using a Binary Distribution .................................. 57
2.3. Installing MySQL on Windows ...................................................................... 58
2.3.1. Choosing An Installation Package ........................................................ 59
2.3.2. Installing MySQL with the Automated Installer ...................................... 59
2.3.3. Using the MySQL Installation Wizard .................................................. 59
2.3.4. Using the Configuration Wizard .......................................................... 62
2.3.5. Installing MySQL from a Noinstall Zip Archive ..................................... 67
2.3.6. Extracting the Install Archive .............................................................. 67
2.3.7. Creating an Option File ...................................................................... 68
2.3.8. Selecting a MySQL Server type ........................................................... 69
2.3.9. Starting the Server for the First Time .................................................... 69
2.3.10. Starting MySQL from the Windows Command Line .............................. 71
iv
2.3.11. Starting MySQL as a Windows Service ............................................... 71
2.3.12. Testing The MySQL Installation ........................................................ 74
2.3.13. Troubleshooting a MySQL Installation Under Windows ......................... 74
2.3.14. Upgrading MySQL on Windows ........................................................ 76
2.3.15. MySQL on Windows Compared to MySQL on Unix ............................. 77
2.4. Installing MySQL on Linux ........................................................................... 79
2.5. Installing MySQL on Mac OS X .................................................................... 81
2.6. Installing MySQL on Solaris ......................................................................... 84
2.7. Installing MySQL on NetWare ...................................................................... 84
2.8. Installing MySQL on Other Unix-Like Systems ................................................ 86
2.9. MySQL Installation Using a Source Distribution ............................................... 89
2.9.1. Source Installation Overview .............................................................. 90
2.9.2. Typical configure Options ............................................................. 93
2.9.3. Installing from the Development Source Tree .........................................96
2.9.4. Dealing with Problems Compiling MySQL ............................................ 99
2.9.5. MIT-pthreads Notes ........................................................................ 102
2.9.6. Installing MySQL from Source on Windows ........................................ 103
2.9.7. Compiling MySQL Clients on Windows ............................................. 106
2.10. Post-Installation Setup and Testing ............................................................. 106
2.10.1. Windows Post-Installation Procedures ............................................... 106
2.10.2. Unix Post-Installation Procedures ..................................................... 107
2.10.3. Securing the Initial MySQL Accounts ............................................... 117
2.11. Upgrading MySQL .................................................................................. 120
2.11.1. Upgrading from MySQL 5.0 to 5.1 ................................................... 122
2.11.2. Copying MySQL Databases to Another Machine ................................ 125
2.12. Downgrading MySQL .............................................................................. 126
2.12.1. Downgrading to MySQL 5.0 ........................................................... 127
2.13. Operating System-Specific Notes ................................................................ 127
2.13.1. Linux Notes ................................................................................. 127
2.13.2. Mac OS X Notes ........................................................................... 134
2.13.3. Solaris Notes ................................................................................ 134
2.13.4. BSD Notes ................................................................................... 138
2.13.5. Other Unix Notes .......................................................................... 141
2.13.6. OS/2 Notes .................................................................................. 157
2.14. Perl Installation Notes .............................................................................. 157
2.14.1. Installing Perl on Unix ................................................................... 158
2.14.2. Installing ActiveState Perl on Windows ............................................. 159
2.14.3. Problems Using the Perl DBI/DBD Interface ....................................... 159
3. Tutorial .............................................................................................................. 162
3.1. Connecting to and Disconnecting from the Server ............................................ 162
3.2. Entering Queries ....................................................................................... 163
3.3. Creating and Using a Database ..................................................................... 166
3.3.1. Creating and Selecting a Database ...................................................... 167
3.3.2. Creating a Table ............................................................................. 168
3.3.3. Loading Data into a Table ................................................................ 169
3.3.4. Retrieving Information from a Table .................................................. 170
3.4. Getting Information About Databases and Tables ............................................ 182
3.5. Using mysql in Batch Mode ...................................................................... 183
3.6. Examples of Common Queries ..................................................................... 185
3.6.1. The Maximum Value for a Column .................................................... 185
3.6.2. The Row Holding the Maximum of a Certain Column ........................... 185
3.6.3. Maximum of Column per Group ........................................................ 186
3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field .............. 186
3.6.5. Using User-Defined Variables ........................................................... 186
3.6.6. Using Foreign Keys ........................................................................ 187
3.6.7. Searching on Two Keys ................................................................... 188
3.6.8. Calculating Visits Per Day ................................................................ 188
3.6.9. Using AUTO_INCREMENT ............................................................... 189
MySQL 5.1 Reference Manual
v
3.7. Queries from the Twin Project ..................................................................... 191
3.7.1. Find All Non-distributed Twins ......................................................... 191
3.7.2. Show a Table of Twin Pair Status ...................................................... 193
3.8. Using MySQL with Apache ........................................................................ 193
4. Using MySQL Programs ....................................................................................... 195
4.1. Overview of MySQL Programs .................................................................... 195
4.2. Invoking MySQL Programs ........................................................................ 196
4.3. Specifying Program Options ........................................................................ 197
4.3.1. Using Options on the Command Line ................................................. 198
4.3.2. Using Option Files .......................................................................... 200
4.3.3. Using Environment Variables to Specify Options .................................. 204
4.3.4. Using Options to Set Program Variables .............................................. 205
5. Database Administration ....................................................................................... 206
5.1. Overview of Server-Side Programs ............................................................... 206
5.2. mysqld — The MySQL Server .................................................................. 207
5.2.1. Option and Variable Reference .......................................................... 207
5.2.2. Command Options .......................................................................... 222
5.2.3. System Variables ............................................................................ 234
5.2.4. Using System Variables ................................................................... 262
5.2.5. Status Variables .............................................................................. 270
5.2.6. SQL Modes ................................................................................... 281
5.2.7. The Shutdown Process ..................................................................... 286
5.2.8. Server-Side Help ............................................................................ 288
5.3. The mysqld-max Extended MySQL Server ................................................. 288
5.4. MySQL Server Startup Programs ................................................................. 288
5.4.1. mysqld_safe — MySQL Server Startup Script ................................. 288
5.4.2. mysql.server — MySQL Server Startup Script ............................... 292
5.4.3. mysqld_multi — Manage Multiple MySQL Servers ......................... 292
5.5. mysqlmanager — The MySQL Instance Manager ....................................... 296
5.5.1. MySQL Instance Manager Command Options ...................................... 296
5.5.2. MySQL Instance Manager Configuration Files ..................................... 300
5.5.3. Starting the MySQL Server with MySQL Instance Manager ................... 301
5.5.4. Instance Manager User and Password Management ............................... 302
5.5.5. MySQL Server Instance Status Monitoring .......................................... 305
5.5.6. Connecting to MySQL Instance Manager ............................................ 306
5.5.7. MySQL Instance Manager Commands ................................................306
5.6. Installation-Related Programs ...................................................................... 310
5.6.1. mysql_fix_privilege_tables — Upgrade MySQL System Tables 310
5.6.2. mysql_upgrade — Check Tables for MySQL Upgrade ..................... 311
5.7. General Security Issues .............................................................................. 312
5.7.1. General Security Guidelines .............................................................. 312
5.7.2. Making MySQL Secure Against Attackers .......................................... 314
5.7.3. Security-Related mysqld Options ..................................................... 316
5.7.4. Security Issues with LOAD DATA LOCAL .......................................... 317
5.7.5. How to Run MySQL as a Normal User ............................................... 318
5.8. The MySQL Access Privilege System ........................................................... 319
5.8.1. What the Privilege System Does ........................................................ 319
5.8.2. How the Privilege System Works ....................................................... 319
5.8.3. Privileges Provided by MySQL ......................................................... 324
5.8.4. Connecting to the MySQL Server ...................................................... 327
5.8.5. Access Control, Stage 1: Connection Verification ................................. 328
5.8.6. Access Control, Stage 2: Request Verification ...................................... 332
5.8.7. When Privilege Changes Take Effect .................................................. 334
5.8.8. Causes of Access denied Errors .................................................. 335
5.8.9. Password Hashing as of MySQL 4.1 ................................................... 339
5.9. MySQL User Account Management ............................................................. 344
5.9.1. MySQL Usernames and Passwords .................................................... 344
5.9.2. Adding New User Accounts to MySQL .............................................. 346
MySQL 5.1 Reference Manual
vi
5.9.3. Removing User Accounts from MySQL .............................................. 349
5.9.4. Limiting Account Resources ............................................................. 349
5.9.5. Assigning Account Passwords ........................................................... 350
5.9.6. Keeping Your Password Secure ......................................................... 351
5.9.7. Using Secure Connections ................................................................ 352
5.10. Backup and Recovery ............................................................................... 360
5.10.1. Database Backups ......................................................................... 360
5.10.2. Example Backup and Recovery Strategy ............................................ 362
5.10.3. Point-in-Time Recovery ................................................................. 365
5.10.4. Table Maintenance and Crash Recovery ............................................ 366
5.11. MySQL Localization and International Usage ............................................... 377
5.11.1. The Character Set Used for Data and Sorting ...................................... 377
5.11.2. Setting the Error Message Language ................................................. 379
5.11.3. Adding a New Character Set ........................................................... 379
5.11.4. The Character Definition Arrays ...................................................... 381
5.11.5. String Collating Support ................................................................. 381
5.11.6. Multi-Byte Character Support .......................................................... 381
5.11.7. Problems With Character Sets ......................................................... 382
5.11.8. MySQL Server Time Zone Support .................................................. 382
5.12. MySQL Server Logs ................................................................................ 384
5.12.1. Selecting General Query and Slow Query Log Output Destinations ........ 384
5.12.2. The Error Log .............................................................................. 386
5.12.3. The General Query Log .................................................................. 386
5.12.4. The Binary Log ............................................................................ 387
5.12.5. The Slow Query Log ..................................................................... 391
5.12.6. Server Log Maintenance ................................................................. 392
5.13. Running Multiple MySQL Servers on the Same Machine ................................ 393
5.13.1. Running Multiple Servers on Windows ............................................. 395
5.13.2. Running Multiple Servers on Unix ................................................... 398
5.13.3. Using Client Programs in a Multiple-Server Environment ..................... 399
5.14. The MySQL Query Cache ......................................................................... 400
5.14.1. How the Query Cache Operates ....................................................... 400
5.14.2. Query Cache SELECT Options ........................................................ 402
5.14.3. Query Cache Configuration ............................................................. 402
5.14.4. Query Cache Status and Maintenance ............................................... 404
6. Replication ......................................................................................................... 406
6.1. Introduction to Replication .......................................................................... 406
6.2. Replication Implementation Overview ........................................................... 407
6.3. Row-Based Replication .............................................................................. 407
6.4. Replication Implementation Details .............................................................. 409
6.4.1. Replication Master Thread States ....................................................... 410
6.4.2. Replication Slave I/O Thread States ................................................... 410
6.4.3. Replication Slave SQL Thread States .................................................. 411
6.4.4. Replication Relay and Status Files ..................................................... 412
6.5. How to Set Up Replication .......................................................................... 414
6.6. Replication Compatibility Between MySQL Versions ...................................... 418
6.7. Upgrading a Replication Setup ..................................................................... 418
6.7.1. Upgrading Replication to 5.0 ............................................................ 418
6.8. Replication Features and Known Problems ..................................................... 419
6.9. Replication Startup Options ......................................................................... 424
6.10. How Servers Evaluate Replication Rules ...................................................... 431
6.11. Replication FAQ ..................................................................................... 434
6.12. Comparison of Statement-Based Versus Row-Based Replication ...................... 440
6.13. Troubleshooting Replication ...................................................................... 442
6.14. How to Report Replication Bugs or Problems ............................................... 443
6.15. Auto-Increment in Multiple-Master Replication ............................................. 444
7. Optimization ....................................................................................................... 445
7.1. Optimization Overview .............................................................................. 445MySQL 5.1 Reference Manual
vii
7.1.1. MySQL Design Limitations and Tradeoffs .......................................... 445
7.1.2. Designing Applications for Portability ................................................ 446
7.1.3. What We Have Used MySQL For ...................................................... 447
7.1.4. The MySQL Benchmark Suite ........................................................... 447
7.1.5. Using Your Own Benchmarks ........................................................... 448
7.2. Optimizing SELECT and Other Statements .................................................... 449
7.2.1. Optimizing Queries with EXPLAIN ................................................... 449
7.2.2. Estimating Query Performance .......................................................... 459
7.2.3. Speed of SELECT Queries ................................................................ 459
7.2.4. WHERE Clause Optimization ............................................................. 459
7.2.5. Range Optimization ........................................................................ 461
7.2.6. Index Merge Optimization ................................................................ 465
7.2.7. IS NULL Optimization ................................................................... 467
7.2.8. DISTINCT Optimization ................................................................. 468
7.2.9. LEFT JOIN and RIGHT JOIN Optimization .................................... 469
7.2.10. Nested Join Optimization ................................................................ 470
7.2.11. Outer Join Simplification ................................................................ 475
7.2.12. ORDER BY Optimization ............................................................... 477
7.2.13. GROUP BY Optimization ............................................................... 479
7.2.14. LIMIT Optimization ..................................................................... 481
7.2.15. How to Avoid Table Scans .............................................................. 482
7.2.16. Speed of INSERT Statements .......................................................... 482
7.2.17. Speed of UPDATE Statements .......................................................... 484
7.2.18. Speed of DELETE Statements .......................................................... 485
7.2.19. Other Optimization Tips ................................................................. 485
7.3. Locking Issues .......................................................................................... 488
7.3.1. Locking Methods ............................................................................ 488
7.3.2. Table Locking Issues ....................................................................... 490
7.3.3. Concurrent Inserts ........................................................................... 491
7.4. Optimizing Database Structure ..................................................................... 492
7.4.1. Design Choices .............................................................................. 492
7.4.2. Make Your Data as Small as Possible ................................................. 492
7.4.3. Column Indexes ............................................................................. 493
7.4.4. Multiple-Column Indexes ................................................................. 494
7.4.5. How MySQL Uses Indexes ............................................................... 495
7.4.6. The MyISAM Key Cache .................................................................. 497
7.4.7. MyISAM Index Statistics Collection ................................................... 502
7.4.8. How MySQL Opens and Closes Tables ............................................... 503
7.4.9. Drawbacks to Creating Many Tables in the Same Database ..................... 505
7.5. Optimizing the MySQL Server .................................................................... 505
7.5.1. System Factors and Startup Parameter Tuning ...................................... 505
7.5.2. Tuning Server Parameters ................................................................. 506
7.5.3. Controlling Query Optimizer Performance ........................................... 510
7.5.4. How Compiling and Linking Affects the Speed of MySQL ..................... 511
7.5.5. How MySQL Uses Memory ............................................................. 512
7.5.6. How MySQL Uses DNS .................................................................. 513
7.6. Disk Issues ............................................................................................... 514
7.6.1. Using Symbolic Links ..................................................................... 515
8. Client and Utility Programs ................................................................................... 518
8.1. Overview of Client and Utility Programs ....................................................... 518
8.2. myisam_ftdump — Display Full-Text Index information .............................. 520
8.3. myisamchk — MyISAM Table-Maintenance Utility ...................................... 521
8.3.1. myisamchk General Options ........................................................... 522
8.3.2. myisamchk Check Options ............................................................. 524
8.3.3. myisamchk Repair Options ............................................................ 525
8.3.4. Other myisamchk Options .............................................................. 527
8.3.5. myisamchk Memory Usage ............................................................ 527
8.4. myisamlog — Display MyISAM Log File Contents ...................................... 528
MySQL 5.1 Reference Manual
viii
8.5. myisampack — Generate Compressed, Read-Only MyISAM Tables ................ 529
8.6. mysql — The MySQL Command-Line Tool ................................................. 535
8.6.1. mysql Options .............................................................................. 536
8.6.2. mysql Commands ......................................................................... 541
8.6.3. mysql Server-Side Help ................................................................. 544
8.6.4. Executing SQL Statements from a Text File ......................................... 545
8.6.5. mysql Tips ................................................................................... 546
8.7. mysqlaccess — Client for Checking Access Privileges ................................ 547
8.8. mysqladmin — Client for Administering a MySQL Server ............................ 549
8.9. mysqlbinlog — Utility for Processing Binary Log Files ............................... 554
8.10. mysqlcheck — A Table Maintenance and Repair Program ........................... 561
8.11. mysqldump — A Database Backup Program .............................................. 564
8.12. mysqlhotcopy — A Database Backup Program ........................................ 574
8.13. mysqlimport — A Data Import Program ................................................. 576
8.14. mysqlshow — Display Database, Table, and Column Information .................. 579
8.15. mysqlslap — Load Emulation Client ....................................................... 581
8.16. mysql_zap — Kill Processes That Match a Pattern ..................................... 584
8.17. perror — Explain Error Codes ................................................................ 584
8.18. replace — A String-Replacement Utility .................................................. 585
9. Language Structure .............................................................................................. 587
9.1. Literal Values ........................................................................................... 587
9.1.1. Strings .......................................................................................... 587
9.1.2. Numbers .......................................................................................589
9.1.3. Hexadecimal Values ........................................................................ 589
9.1.4. Boolean Values .............................................................................. 590
9.1.5. Bit-Field Values ............................................................................. 590
9.1.6. NULL Values ................................................................................. 590
9.2. Database, Table, Index, Column, and Alias Names .......................................... 590
9.2.1. Identifier Qualifiers ......................................................................... 592
9.2.2. Identifier Case Sensitivity ................................................................. 593
9.2.3. Mapping of Identifiers to Filenames ................................................... 594
9.3. User-Defined Variables .............................................................................. 596
9.4. Comment Syntax ....................................................................................... 597
9.5. Treatment of Reserved Words in MySQL ...................................................... 598
10. Character Set Support ......................................................................................... 602
10.1. Character Sets and Collations in General ...................................................... 602
10.2. Character Sets and Collations in MySQL ..................................................... 603
10.3. Specifying Character Sets and Collations ..................................................... 604
10.3.1. Server Character Set and Collation ................................................... 604
10.3.2. Database Character Set and Collation ................................................ 605
10.3.3. Table Character Set and Collation .................................................... 606
10.3.4. Column Character Set and Collation ................................................. 606
10.3.5. Character String Literal Character Set and Collation ............................ 607
10.3.6. National Character Set ................................................................... 608
10.3.7. Examples of Character Set and Collation Assignment .......................... 608
10.3.8. Compatibility with Other DBMSs .................................................... 609
10.4. Connection Character Sets and Collations .................................................... 609
10.5. Collation Issues ....................................................................................... 611
10.5.1. Using COLLATE in SQL Statements ................................................. 611
10.5.2. COLLATE Clause Precedence .......................................................... 612
10.5.3. BINARY Operator ......................................................................... 612
10.5.4. Some Special Cases Where the Collation Determination Is Tricky .......... 613
10.5.5. Collations Must Be for the Right Character Set ................................... 614
10.5.6. An Example of the Effect of Collation ............................................... 614
10.6. Operations Affected by Character Set Support ............................................... 615
10.6.1. Result Strings ............................................................................... 615
10.6.2. CONVERT() and CAST() ............................................................. 616
10.6.3. SHOW Statements and INFORMATION_SCHEMA ................................ 616
MySQL 5.1 Reference Manual
ix
10.7. Unicode Support ..................................................................................... 618
10.8. UTF-8 for Metadata ................................................................................. 618
10.9. Character Sets and Collations That MySQL Supports ..................................... 620
10.9.1. Unicode Character Sets .................................................................. 620
10.9.2. West European Character Sets ......................................................... 623
10.9.3. Central European Character Sets ...................................................... 625
10.9.4. South European and Middle East Character Sets ................................. 625
10.9.5. Baltic Character Sets ...................................................................... 626
10.9.6. Cyrillic Character Sets ................................................................... 626
10.9.7. Asian Character Sets ...................................................................... 627
10.10. FAQ: MySQL Chinese, Japanese, and Korean Character Sets ......................... 631
10.10.1. SELECT shows non-Latin characters as "?"s. Why? ........................... 631
10.10.2. Troubles with GB character sets (Chinese) ....................................... 632
10.10.3. Troubles with big5 character set (Chinese) ....................................... 632
10.10.4. Troubles with character-set conversions (Japanese) ............................ 633
10.10.5. The Great Yen Sign problem (Japanese) .......................................... 634
10.10.6. Troubles with euckr character set (Korean) ....................................... 634
10.10.7. The “Data truncated” message ....................................................... 634
10.10.8. Troubles with Access, Perl, PHP, etc. .............................................. 635
10.10.9. How can I get old MySQL 4.0 behaviour back? ................................. 636
10.10.10. Why do some LIKE and FULLTEXT searches fail? ......................... 637
10.10.11. What CJK character sets are available? .......................................... 637
10.10.12. Is character X available in all character sets? ................................... 637
10.10.13. Strings don't sort correctly in Unicode (I) ....................................... 638
10.10.14. Strings don't sort correctly in Unicode (II) ...................................... 639
10.10.15. My supplementary characters get rejected ....................................... 640
10.10.16. Shouldn't it be CJKV (V for Vietnamese)? ...................................... 640
10.10.17. Will MySQL fix any CJK problems in version 5.1? .......................... 640
10.10.18. When will MySQL translate the manual again? ............................... 641
10.10.19. Whom can I talk to? ................................................................... 641
11. Data Types ....................................................................................................... 642
11.1. Data Type Overview ................................................................................ 642
11.1.1. Overview of Numeric Types ........................................................... 642
11.1.2. Overview of Date and Time Types ................................................... 645
11.1.3. Overview of String Types ............................................................... 646
11.1.4. Data Type Default Values ............................................................... 649
11.2. Numeric Types ........................................................................................ 650
11.3. Date and Time Types ............................................................................... 653
11.3.1. The DATETIME, DATE, and TIMESTAMP Types ................................ 654
11.3.2. The TIME Type ............................................................................ 659
11.3.3. The YEAR Type ............................................................................ 659
11.3.4. Y2K Issues and Date Types ............................................................ 660
11.4. String Types ........................................................................................... 660
11.4.1. The CHAR and VARCHAR Types ...................................................... 660
11.4.2. The BINARY and VARBINARY Types ..............................................662
11.4.3. The BLOB and TEXT Types ............................................................ 663
11.4.4. The ENUM Type ............................................................................ 664
11.4.5. The SET Type .............................................................................. 666
11.5. Data Type Storage Requirements ................................................................ 668
11.6. Choosing the Right Type for a Column ........................................................ 671
11.7. Using Data Types from Other Database Engines ............................................ 671
12. Functions and Operators ...................................................................................... 673
12.1. Operators ............................................................................................... 673
12.1.1. Operator Precedence ...................................................................... 673
12.1.2. Type Conversion in Expression Evaluation ........................................ 674
12.1.3. Comparison Functions and Operators ................................................ 675
12.1.4. Logical Operators .......................................................................... 679
12.2. Control Flow Functions ............................................................................ 681
MySQL 5.1 Reference Manual
x
12.3. String Functions ...................................................................................... 682
12.3.1. String Comparison Functions .......................................................... 692
12.4. Numeric Functions ................................................................................... 694
12.4.1. Arithmetic Operators ..................................................................... 694
12.4.2. Mathematical Functions ................................................................. 696
12.5. Date and Time Functions .......................................................................... 702
12.6. What Calendar Is Used By MySQL? ........................................................... 718
12.7. Full-Text Search Functions ........................................................................ 718
12.7.1. Boolean Full-Text Searches ............................................................ 722
12.7.2. Full-Text Searches with Query Expansion .......................................... 724
12.7.3. Full-Text Stopwords ...................................................................... 725
12.7.4. Full-Text Restrictions .................................................................... 727
12.7.5. Fine-Tuning MySQL Full-Text Search .............................................. 728
12.8. Cast Functions and Operators ..................................................................... 730
12.9. XML Functions ....................................................................................... 732
12.10. Other Functions ..................................................................................... 736
12.10.1. Bit Functions .............................................................................. 736
12.10.2. Encryption and Compression Functions ........................................... 737
12.10.3. Information Functions .................................................................. 742
12.10.4. Miscellaneous Functions ............................................................... 748
12.11. Functions and Modifiers for Use with GROUP BY Clauses ............................ 751
12.11.1. GROUP BY (Aggregate) Functions ................................................. 751
12.11.2. GROUP BY Modifiers .................................................................. 754
12.11.3. GROUP BY and HAVING with Hidden Fields ................................... 757
13. SQL Statement Syntax ........................................................................................ 758
13.1. Data Definition Statements ........................................................................ 758
13.1.1. ALTER DATABASE Syntax ............................................................ 758
13.1.2. ALTER TABLE Syntax .................................................................. 758
13.1.3. ALTER LOGFILE GROUP Syntax .................................................. 766
13.1.4. ALTER TABLESPACE Syntax ........................................................ 767
13.1.5. CREATE DATABASE Syntax .......................................................... 768
13.1.6. CREATE INDEX Syntax ................................................................ 768
13.1.7. CREATE TABLE Syntax ................................................................ 770
13.1.8. CREATE LOGFILE GROUP Syntax ................................................ 785
13.1.9. CREATE TABLESPACE Syntax ...................................................... 786
13.1.10. DROP DATABASE Syntax ............................................................ 786
13.1.11. DROP INDEX Syntax .................................................................. 787
13.1.12. DROP TABLE Syntax .................................................................. 787
13.1.13. DROP LOGFILE GROUP Syntax .................................................. 788
13.1.14. DROP TABLESPACE Syntax ........................................................ 788
13.1.15. RENAME DATABASE Syntax ........................................................ 789
13.1.16. RENAME TABLE Syntax .............................................................. 789
13.2. Data Manipulation Statements .................................................................... 790
13.2.1. DELETE Syntax ............................................................................ 790
13.2.2. DO Syntax .................................................................................... 792
13.2.3. HANDLER Syntax .......................................................................... 793
13.2.4. INSERT Syntax ............................................................................ 794
13.2.5. LOAD DATA INFILE Syntax ........................................................ 801
13.2.6. REPLACE Syntax .......................................................................... 809
13.2.7. SELECT Syntax ............................................................................ 810
13.2.8. Subquery Syntax ........................................................................... 825
13.2.9. TRUNCATE Syntax ........................................................................ 834
13.2.10. UPDATE Syntax .......................................................................... 835
13.3. MySQL Utility Statements ........................................................................ 837
13.3.1. DESCRIBE Syntax ........................................................................ 837
13.3.2. HELP Syntax ................................................................................ 838
13.3.3. USE Syntax .................................................................................. 840
13.4. MySQL Transactional and Locking Statements ............................................. 840
MySQL 5.1 Reference Manual
xi
13.4.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax ................. 840
13.4.2. Statements That Cannot Be Rolled Back ............................................ 842
13.4.3. Statements That Cause an Implicit Commit ........................................ 842
13.4.4. SAVEPOINT and ROLLBACK TO SAVEPOINT Syntax ..................... 843
13.4.5. LOCK TABLES and UNLOCK TABLES Syntax ................................. 843
13.4.6. SET TRANSACTION Syntax .......................................................... 846
13.4.7. XA Transactions ........................................................................... 846
13.5. Database Administration Statements ........................................................... 850
13.5.1. Account Management Statements..................................................... 850
13.5.2. Table Maintenance Statements ......................................................... 859
13.5.3. SET Syntax .................................................................................. 865
13.5.4. SHOW Syntax ................................................................................ 871
13.5.5. Other Administrative Statements ...................................................... 896
13.6. Replication Statements ............................................................................. 900
13.6.1. SQL Statements for Controlling Master Servers .................................. 900
13.6.2. SQL Statements for Controlling Slave Servers .................................... 903
13.7. SQL Syntax for Prepared Statements ........................................................... 911
14. Storage Engines and Table Types .......................................................................... 915
14.1. Overview of MySQL Storage Engine Architecture ......................................... 915
14.1.1. The Common Database Server Layer ................................................ 916
14.1.2. Pluggable Storage Engine Architecture .............................................. 917
14.2. Supported Storage Engines ........................................................................ 917
14.2.1. Choosing a Storage Engine ............................................................. 918
14.2.2. Comparing Transaction and Non-Transaction Engines ......................... 919
14.2.3. Other Storage Engines ................................................................... 920
14.3. Setting the Storage Engine ......................................................................... 920
14.4. The MyISAM Storage Engine ..................................................................... 921
14.4.1. MyISAM Startup Options ................................................................ 923
14.4.2. Space Needed for Keys .................................................................. 924
14.4.3. MyISAM Table Storage Formats ...................................................... 925
14.4.4. MyISAM Table Problems ................................................................ 927
14.5. The InnoDB Storage Engine ..................................................................... 929
14.5.1. InnoDB Overview ........................................................................ 929
14.5.2. InnoDB Contact Information .......................................................... 929
14.5.3. InnoDB Configuration .................................................................. 930
14.5.4. InnoDB Startup Options and System Variables .................................. 936
14.5.5. Creating the InnoDB Tablespace ..................................................... 942
14.5.6. Creating and Using InnoDB Tables ................................................. 943
14.5.7. Adding and Removing InnoDB Data and Log Files ............................ 951
14.5.8. Backing Up and Recovering an InnoDB Database .............................. 952
14.5.9. Moving an InnoDB Database to Another Machine .............................. 955
14.5.10. InnoDB Transaction Model and Locking ........................................ 955
14.5.11. InnoDB Performance Tuning Tips ................................................. 964
14.5.12. Implementation of Multi-Versioning ............................................... 970
14.5.13. InnoDB Table and Index Structures ............................................... 971
14.5.14. InnoDB File Space Management and Disk I/O ................................. 973
14.5.15. InnoDB Error Handling ............................................................... 975
14.5.16. Restrictions on InnoDB Tables ..................................................... 981
14.5.17. InnoDB Troubleshooting ............................................................. 983
14.6. The MERGE Storage Engine ....................................................................... 985
14.6.1. MERGE Table Problems .................................................................. 987
14.7. The MEMORY (HEAP) Storage Engine .......................................................... 988
14.8. The EXAMPLE Storage Engine ................................................................... 990
14.9. The FEDERATED Storage Engine ............................................................... 990
14.9.1. Description of the FEDERATED Storage Engine ................................. 991
14.9.2. How to use FEDERATED Tables ...................................................... 991
14.9.3. Limitations of the FEDERATED Storage Engine .................................. 992
14.10. The ARCHIVE Storage Engine ................................................................. 993
MySQL 5.1 Reference Manual
xii
14.11. The CSV Storage Engine ......................................................................... 994
14.11.1. Repairing and Checking CSV Tables ............................................... 995
14.11.2. CSV Limitations ......................................................................... 996
14.12. The BLACKHOLE Storage Engine ............................................................. 996
15. MySQL Cluster ................................................................................................. 998
15.1. MySQL Cluster Overview ......................................................................... 999
15.2. Basic MySQL Cluster Concepts ................................................................ 1000
15.2.1. MySQL Cluster Nodes, Node Groups, Replicas, and Partitions ............. 1001
15.3. Simple Multi-Computer How-To ............................................................... 1003
15.3.1. Hardware, Software, and Networking ............................................... 1005
15.3.2. Multi-Computer Installation ........................................................... 1006
15.3.3. Multi-Computer Configuration ....................................................... 1007
15.3.4. Initial Startup .............................................................................. 1009
15.3.5. Loading Sample Data and Performing Queries ................................... 1010
15.3.6. Safe Shutdown and Restart ............................................................ 1013
15.4. MySQL Cluster Configuration .................................................................. 1014
15.4.1. Building MySQL Cluster from Source Code ...................................... 1014
15.4.2. Installing the Software .................................................................. 1015
15.4.3. Quick Test Setup of MySQL Cluster ................................................ 1015
15.4.4. Configuration File ........................................................................ 1017
15.4.5. Overview of Cluster Configuration Parameters .................................. 1045
15.4.6. Configuring Parameters for Local Checkpoints .................................. 1055
15.5. Upgrading and Downgrading MySQL Cluster .............................................. 1057
15.5.1. Performing a Rolling Restart of the Cluster ....................................... 1057
15.5.2. Cluster Upgrade and Downgrade Compatibility ................................. 1058
15.6. Process Management in MySQL Cluster ..................................................... 1060
15.6.1. MySQL Server Process Usage for MySQL Cluster ............................. 1061
15.6.2. ndbd, the Storage Engine Node Process ........................................... 1062
15.6.3. ndb_mgmd, the Management Server Process .................................... 1063
15.6.4. ndb_mgm, the Management Client Process ....................................... 1064
15.6.5. Command Options for MySQL Cluster Processes ............................... 1064
15.7. Management of MySQL Cluster ................................................................1068
15.7.1. MySQL Cluster Startup Phases ....................................................... 1068
15.7.2. Commands in the Management Client .............................................. 1070
15.7.3. Event Reports Generated in MySQL Cluster ...................................... 1072
15.7.4. Single User Mode ........................................................................ 1078
15.8. On-line Backup of MySQL Cluster ............................................................ 1079
15.8.1. Cluster Backup Concepts ............................................................... 1079
15.8.2. Using The Management Client to Create a Backup ............................. 1080
15.8.3. How to Restore a Cluster Backup .................................................... 1081
15.8.4. Configuration for Cluster Backup .................................................... 1084
15.8.5. Backup Troubleshooting ................................................................ 1084
15.9. Cluster Utility Programs .......................................................................... 1085
15.9.1. ndb_config ............................................................................. 1086
15.9.2. ndb_delete_all ..................................................................... 1088
15.9.3. ndb_desc ................................................................................. 1089
15.9.4. ndb_drop_index ..................................................................... 1090
15.9.5. ndb_drop_table ..................................................................... 1090
15.9.6. ndb_error_reporter ............................................................. 1091
15.9.7. ndb_print_backup_file ....................................................... 1091
15.9.8. ndb_print_schema_file ....................................................... 1091
15.9.9. ndb_print_sys_file ............................................................. 1092
15.9.10. ndb_select_all ................................................................... 1092
15.9.11. ndb_select_count ............................................................... 1094
15.9.12. ndb_show_tables ................................................................. 1095
15.9.13. ndb_size.pl ......................................................................... 1095
15.9.14. ndb_waiter ........................................................................... 1098
15.10. MySQL Cluster Replication .................................................................... 1099
MySQL 5.1 Reference Manual
xiii
15.10.1. Abbreviations and Symbols .......................................................... 1100
15.10.2. Assumptions and General Requirements ......................................... 1101
15.10.3. Known Issues ............................................................................ 1101
15.10.4. Replication Schema and Tables ..................................................... 1102
15.10.5. Preparing the Cluster for Replication .............................................. 1104
15.10.6. Starting Replication (Single Replication Channel) ............................. 1106
15.10.7. Using Two Replication Channels ................................................... 1107
15.10.8. Implementing Failover with MySQL Cluster ................................... 1107
15.10.9. MySQL Cluster Backups With Replication ...................................... 1108
15.11. MySQL Cluster Disk Data Storage ........................................................... 1114
15.12. Using High-Speed Interconnects with MySQL Cluster ................................. 1118
15.12.1. Configuring MySQL Cluster to use SCI Sockets ............................... 1118
15.12.2. Understanding the Impact of Cluster Interconnects ............................ 1122
15.13. Known Limitations of MySQL Cluster ...................................................... 1124
15.14. MySQL Cluster Development Roadmap .................................................... 1130
15.14.1. MySQL Cluster Changes in MySQL 5.1 ......................................... 1131
15.15. MySQL Cluster FAQ ............................................................................. 1131
15.16. MySQL Cluster Glossary ....................................................................... 1139
16. Partitioning ...................................................................................................... 1144
16.1. Overview of Partitioning in MySQL ........................................................... 1145
16.2. Partition Types ....................................................................................... 1147
16.2.1. RANGE Partitioning ...................................................................... 1149
16.2.2. LIST Partitioning ........................................................................ 1151
16.2.3. HASH Partitioning ........................................................................ 1153
16.2.4. KEY Partitioning .......................................................................... 1156
16.2.5. Subpartitioning ............................................................................ 1157
16.2.6. How MySQL Partitioning Handles NULL Values ............................... 1160
16.3. Partition Management ............................................................................. 1164
16.3.1. Management of RANGE and LIST Partitions ..................................... 1165
16.3.2. Management of HASH and KEY Partitions ......................................... 1170
16.3.3. Maintenance of Partitions .............................................................. 1171
16.3.4. Obtaining Information About Partitions ............................................ 1172
16.4. Partition Pruning .................................................................................... 1174
16.5. Restrictions and Limitations on Partitioning ................................................. 1177
17. Spatial Extensions ............................................................................................. 1182
17.1. Introduction to MySQL Spatial Support ...................................................... 1182
17.2. The OpenGIS Geometry Model ................................................................. 1183
17.2.1. The Geometry Class Hierarchy ....................................................... 1183
17.2.2. Class Geometry ......................................................................... 1184
17.2.3. Class Point ............................................................................... 1185
17.2.4. Class Curve ............................................................................... 1186
17.2.5. Class LineString ..................................................................... 1186
17.2.6. Class Surface ........................................................................... 1186
17.2.7. Class Polygon ........................................................................... 1187
17.2.8. Class GeometryCollection ..................................................... 1187
17.2.9. Class MultiPoint ..................................................................... 1188
17.2.10. Class MultiCurve ................................................................... 1188
17.2.11. Class MultiLineString ......................................................... 1188
17.2.12. Class MultiSurface ............................................................... 1189
17.2.13. Class MultiPolygon ............................................................... 1189
17.3. Supported Spatial Data Formats ................................................................ 1189
17.3.1. Well-Known Text (WKT) Format ................................................... 1190
17.3.2. Well-Known Binary (WKB) Format ................................................ 1190
17.4. Creating a Spatially Enabled MySQL Database ............................................1191
17.4.1. MySQL Spatial Data Types ............................................................ 1191
17.4.2. Creating Spatial Values ................................................................. 1192
17.4.3. Creating Spatial Columns .............................................................. 1195
17.4.4. Populating Spatial Columns ........................................................... 1195
MySQL 5.1 Reference Manual
xiv
17.4.5. Fetching Spatial Data .................................................................... 1196
17.5. Analyzing Spatial Information .................................................................. 1197
17.5.1. Geometry Format Conversion Functions ........................................... 1197
17.5.2. Geometry Functions ................................................................... 1198
17.5.3. Functions That Create New Geometries from Existing Ones ................. 1203
17.5.4. Functions for Testing Spatial Relations Between Geometric Objects ...... 1204
17.5.5. Relations on Geometry Minimal Bounding Rectangles (MBRs) ............ 1204
17.5.6. Functions That Test Spatial Relationships Between Geometries ............ 1205
17.6. Optimizing Spatial Analysis ..................................................................... 1207
17.6.1. Creating Spatial Indexes ................................................................ 1207
17.6.2. Using a Spatial Index .................................................................... 1208
17.7. MySQL Conformance and Compatibility .................................................... 1210
18. Stored Procedures and Functions .......................................................................... 1211
18.1. Stored Routines and the Grant Tables ......................................................... 1211
18.2. Stored Routine Syntax ............................................................................. 1212
18.2.1. CREATE PROCEDURE and CREATE FUNCTION Syntax .................. 1212
18.2.2. ALTER PROCEDURE and ALTER FUNCTION Syntax ...................... 1216
18.2.3. DROP PROCEDURE and DROP FUNCTION Syntax .......................... 1216
18.2.4. CALL Statement Syntax ................................................................ 1216
18.2.5. BEGIN ... END Compound Statement Syntax ............................... 1217
18.2.6. DECLARE Statement Syntax .......................................................... 1218
18.2.7. Variables in Stored Routines .......................................................... 1218
18.2.8. Conditions and Handlers ................................................................ 1219
18.2.9. Cursors ...................................................................................... 1221
18.2.10. Flow Control Constructs .............................................................. 1222
18.3. Stored Procedures, Functions, Triggers, and LAST_INSERT_ID() ................ 1224
18.4. Stored Procedures, Functions, Triggers, and Replication: Frequently Asked Questions
.................................................................................................................... 1225
18.5. Binary Logging of Stored Routines and Triggers .......................................... 1226
19. Triggers .......................................................................................................... 1232
19.1. CREATE TRIGGER Syntax ..................................................................... 1232
19.2. DROP TRIGGER Syntax ......................................................................... 1235
19.3. Using Triggers ....................................................................................... 1235
20. Event Scheduler ................................................................................................ 1239
20.1. Event Scheduler Overview ....................................................................... 1239
20.2. Event Scheduler Syntax ........................................................................... 1242
20.2.1. CREATE EVENT Syntax ............................................................... 1243
20.2.2. ALTER EVENT Syntax ................................................................. 1246
20.2.3. DROP EVENT Syntax ................................................................... 1248
20.3. Event Metadata ...................................................................................... 1248
20.4. Event Scheduler Status ............................................................................ 1248
20.5. The Event Scheduler and MySQL Privileges ................................................ 1249
20.6. Event Scheduler Limitations and Restrictions ............................................... 1251
21. Views ............................................................................................................. 1253
21.1. ALTER VIEW Syntax ............................................................................. 1253
21.2. CREATE VIEW Syntax ........................................................................... 1253
21.3. DROP VIEW Syntax ............................................................................... 1260
22. The INFORMATION_SCHEMA Database ............................................................... 1261
22.1. The INFORMATION_SCHEMA SCHEMATA Table ....................................... 1263
22.2. The INFORMATION_SCHEMA TABLES Table ........................................... 1263
22.3. The INFORMATION_SCHEMA COLUMNS Table ......................................... 1264
22.4. The INFORMATION_SCHEMA STATISTICS Table ................................... 1265
22.5. The INFORMATION_SCHEMA USER_PRIVILEGES Table ......................... 1266
22.6. The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table ..................... 1267
22.7. The INFORMATION_SCHEMA TABLE_PRIVILEGES Table ....................... 1267
22.8. The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table ..................... 1268
22.9. The INFORMATION_SCHEMA CHARACTER_SETS Table ........................... 1268
22.10. The INFORMATION_SCHEMA COLLATIONS Table ................................. 1269
MySQL 5.1 Reference Manual
xv
22.11. The INFORMATION_SCHEMA COLLA-
TION_CHARACTER_SET_APPLICABILITY Table .......................................... 1269
22.12. The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table ................... 1269
22.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table ..................... 1270
22.14. The INFORMATION_SCHEMA ROUTINES Table ..................................... 1271
22.15. The INFORMATION_SCHEMA VIEWS Table ........................................... 1272
22.16. The INFORMATION_SCHEMA TRIGGERS Table ..................................... 1273
22.17. The INFORMATION_SCHEMA PLUGINS Table ....................................... 1274
22.18. The INFORMATION_SCHEMA ENGINES Table ....................................... 1275
22.19. The INFORMATION_SCHEMA PARTITIONS Table ................................. 1275
22.20. The INFORMATION_SCHEMA EVENTS Table ......................................... 1278
22.21. The INFORMATION_SCHEMA FILES Table ........................................... 1281
22.22. The INFORMATION_SCHEMA PROCESSLIST Table ............................... 1285
22.23. The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table ....... 1285
22.24. The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables
.................................................................................................................... 1286
22.25. The INFORMATION_SCHEMA GLOBAL_VARIABLES and SES-
SION_VARIABLES Tables ............................................................................. 1286
22.26. Other INFORMATION_SCHEMA Tables ................................................... 1287
22.27. Extensions to SHOW Statements ............................................................... 1287
23. Precision Math .................................................................................................1289
23.1. Types of Numeric Values ......................................................................... 1289
23.2. DECIMAL Data Type Changes .................................................................. 1290
23.3. Expression Handling ............................................................................... 1291
23.4. Rounding Behavior ................................................................................. 1293
23.5. Precision Math Examples ......................................................................... 1293
24. APIs and Libraries ............................................................................................ 1298
24.1. libmysqld, the Embedded MySQL Server Library ......................................... 1298
24.1.1. Overview of the Embedded MySQL Server Library ............................ 1298
24.1.2. Compiling Programs with libmysqld ........................................... 1298
24.1.3. Restrictions When Using the Embedded MySQL Server ...................... 1299
24.1.4. Options with the Embedded Server .................................................. 1299
24.1.5. Embedded Server Examples ........................................................... 1300
24.1.6. Licensing the Embedded Server ...................................................... 1303
24.2. MySQL C API ....................................................................................... 1303
24.2.1. C API Data types ......................................................................... 1303
24.2.2. C API Function Overview .............................................................. 1307
24.2.3. C API Function Descriptions .......................................................... 1311
24.2.4. C API Prepared Statements ............................................................ 1359
24.2.5. C API Prepared Statement Data types .............................................. 1359
24.2.6. C API Prepared Statement Function Overview ................................... 1362
24.2.7. C API Prepared Statement Function Descriptions ............................... 1365
24.2.8. C API Prepared statement problems ................................................. 1387
24.2.9. C API Handling of Multiple Statement Execution ............................... 1387
24.2.10. C API Handling of Date and Time Values ....................................... 1388
24.2.11. C API Threaded Function Descriptions ........................................... 1389
24.2.12. C API Embedded Server Function Descriptions ................................ 1390
24.2.13. Common Questions and Problems When Using the C API .................. 1392
24.2.14. Building Client Programs ............................................................. 1393
24.2.15. How to Make a Threaded Client .................................................... 1394
24.3. MySQL PHP API ................................................................................... 1395
24.3.1. Common Problems with MySQL and PHP ........................................ 1396
24.3.2. Enabling Both mysql and mysqli in PHP ...................................... 1396
24.4. MySQL Perl API .................................................................................... 1397
24.5. MySQL C++ API ................................................................................... 1397
24.6. MySQL Python API ................................................................................ 1398
24.7. MySQL Tcl API ..................................................................................... 1398
24.8. MySQL Eiffel Wrapper ........................................................................... 1398
MySQL 5.1 Reference Manual
xvi
24.9. MySQL Program Development Utilities ...................................................... 1398
24.9.1. msql2mysql — Convert mSQL Programs for Use with MySQL ........ 1398
24.9.2. mysql_config — Get Compile Options for Compiling Clients ......... 1398
25. Connectors ...................................................................................................... 1401
25.1. MySQL Connector/ODBC ....................................................................... 1401
25.1.1. Introduction to Connector/ODBC .................................................... 1402
25.1.2. Installing Connector/ODBC ........................................................... 1405
25.1.3. MyODBC Configuration ............................................................... 1425
25.1.4. MyODBC Examples ..................................................................... 1441
25.1.5. MyODBC Reference .................................................................... 1457
25.1.6. MyODBC Notes and Tips .............................................................. 1463
25.1.7. MyODBC Support ....................................................................... 1473
25.2. Connector/NET ...................................................................................... 1474
25.2.1. Connector/NET Versions ............................................................... 1475
25.2.2. How to install Connector/NET ........................................................ 1475
25.2.3. Connector/NET Examples ............................................................. 1482
25.2.4. Connector/NET Reference ............................................................. 1531
25.2.5. Connector/NET Notes and Tips ...................................................... 1645
25.2.6. Connector/NET Support ................................................................ 1662
25.3. MySQL Connector/J ............................................................................... 1663
25.3.1. Connector/J Versions .................................................................... 1664
25.3.2. Installing Connector/J ................................................................... 1665
25.3.3. Connector/J Examples ................................................................... 1669
25.3.4. Connector/J (JDBC) Reference ....................................................... 1669
25.3.5. Connector/J Notes and Tips ............................................................ 1687
25.3.6. Connector/J Support ..................................................................... 1705
25.4. MySQL Connector/MXJ .......................................................................... 1707
25.4.1. Introduction to Connector/MXJ ...................................................... 1707
25.4.2. Installing Connector/MXJ .............................................................. 1708
25.4.3. Connector/MXJ Configuration ........................................................ 1712
25.4.4. Connector/MXJ Reference ............................................................. 1715
25.4.5. Connector/MXJ Notes and Tips ...................................................... 1717
25.4.6. Connector/MXJ Support ................................................................ 1721
25.5. Connector/PHP ...................................................................................... 1722
26. Extending MySQL ............................................................................................ 1723
26.1. MySQL Internals .................................................................................... 1723
26.1.1. MySQL Threads .......................................................................... 1723
26.1.2. MySQL Test Suite ....................................................................... 1723
26.2. The MySQL Plugin Interface .................................................................... 1724
26.2.1. Characteristics of the Plugin Interface .............................................. 1724
26.2.2. Full-Text Parser Plugins ................................................................ 1726
26.2.3. INSTALL PLUGIN Syntax ........................................................... 1727
26.2.4. UNINSTALL PLUGIN Syntax .......................................................