book

MySQL Reference Manual

by Michael Widenius, David Axmark, Kaj Arno

June 2002

Intermediate to advanced

816 pages

20h 46m

English

O'Reilly Media, Inc.

Read now

Unlock full access

About This ManualConventions Used in This Manual
History of MySQLThe Main Features of MySQLHow Stable Is MySQL?How Big Can MySQL Tables Be?Year 2000 Compliance
The Business Model and Services of MySQL ABSupportTraining and certificationConsultingCommercial licensesPartneringAdvertisingContact Information
Support Offered by MySQL ABCopyrights and Licenses Used by MySQLMySQL LicensesUsing the MySQL software under a commercial licenseUsing the MySQL software for free under GPLMySQL AB Logos and TrademarksThe original MySQL logoMySQL logos that may be used without written permissionWhen do you need a written permission to use MySQL logos?MySQL AB partnership logosUsing the word MySQL in printed text or presentationsUsing the word MySQL in company and product names
Stepwise RolloutReady for Immediate Development UseEmbedded MySQLOther Features Available from MySQL 4.0.0Future MySQL 4.0 FeaturesMySQL 4.1, The Following Development Release
MySQL PortalsMySQL Mailing ListsThe MySQL Mailing ListsAsking questions or reporting bugsHow to report bugs or problemsGuidelines for answering questions on the mailing list
What Standards Does MySQL Follow?Running MySQL in ANSI ModeMySQL Extensions to ANSI SQL92MySQL Differences Compared to ANSI SQL92Sub-SELECTsSELECT INTO TABLETransactions and atomic operationsStored procedures and triggersForeign keysViews-- as the start of a commentKnown Errors and Design Deficiencies in MySQL
Things That Should Be in 4.0Things That Should Be in 4.1Things That Must Be Done in the Real Near FutureThings That Have to Be Done SometimeThings We Have No Plans to Do

How MySQL Compares to mSQLHow to convert mSQL tools for MySQLHow mSQL and MySQL client/server communications protocols differHow mSQL 2.0 SQL syntax differs from MySQLColumn typesIndex creationTo insert a unique identifier into a tableTo obtain a unique identifier for a rowTo get the time a column was last modifiedNULL value comparisonsString comparisonsCase-insensitive searchingHandling of trailing spacesWHERE clausesAccess controlHow MySQL Compares to PostgreSQLMySQL and PostgreSQL development strategiesFeaturewise comparison of MySQL and PostgreSQLBenchmarking MySQL and PostgreSQL
Quick Standard Installation of MySQLInstalling MySQL on LinuxInstalling MySQL on WindowsInstalling the binariesPreparing the windows MySQL environmentStarting the server for the first time
How to Get MySQLOperating Systems Supported by MySQLWhich MySQL Version to UseInstallation LayoutsHow and When Updates Are ReleasedMySQL Binaries Compiled by MySQL ABInstalling a MySQL Binary Distribution
Quick Installation OverviewApplying PatchesTypical configure OptionsInstalling from the Development Source TreeProblems Compiling?MIT-pthreads NotesWindows Source Distribution
Problems Running mysql_install_dbProblems Starting the MySQL serverStarting and Stopping MySQL Automatically
Upgrading from Version 3.23 to Version 4.0Upgrading from Version 3.22 to Version 3.23Upgrading from Version 3.21 to Version 3.22Upgrading from Version 3.20 to Version 3.21Upgrading to Another Architecture
Linux Notes (All Linux Versions)Linux notes for binary distributionsLinux x86 notesLinux SPARC notesLinux-Alpha notesLinux PowerPC notesLinux MIPS notesLinux IA64 notesWindows NotesStarting MySQL on Windows 95, 98, or MeStarting MySQL on Windows NT, 2000, or XPRunning MySQL on WindowsConnecting to a remote MySQL from Windows with SSHSplitting data across different disks on windowsCompiling MySQL clients on WindowsMySQL-Windows compared to Unix MySQLSolaris NotesSolaris 2.7/2.8 notesSolaris x86 notesBSD NotesFreeBSD notesNetBSD notesOpenBSD 2.5 notesOpenBSD 2.8 notesBSD/OS Version 2.x notesBSD/OS Version 3.x notesBSD/OS Version 4.x notesMac OS X NotesMac OS X public betaMac OS X serverOther Unix NotesHP-UX notes for binary distributionsHP-UX Version 10.20 notesHP-UX Version 11.x notesIBM-AIX notesSunOS 4 notesAlpha-DEC-Unix notes (Tru64)Alpha-DEC-OSF/1 notesSGI Irix notesCaldera (SCO) notesCaldera (SCO) Unixware Version 7.0 notesOS/2 NotesBeOS NotesNovell Netware Notes
Installing Perl on UnixInstalling ActiveState Perl on WindowsInstalling the MySQL Perl Distribution on WindowsProblems Using the Perl DBI/DBD Interface
Connecting to and Disconnecting from the Server
Creating and Selecting a DatabaseCreating a TableLoading Data into a TableRetrieving Information from a TableSelecting all dataSelecting particular rowsSelecting particular columnsSorting rowsDate calculationsWorking with NULL valuesPattern matchingCounting rowsUsing more than one table
The Maximum Value for a ColumnThe Row Holding the Maximum of a Certain ColumnMaximum of Column per GroupThe Rows Holding the Group-Wise Maximum of a Certain FieldUsing User VariablesUsing Foreign KeysSearching on Two KeysCalculating Visits per DayUsing AUTO_INCREMENT
Find All Non-Distributed TwinsShow a Table on Twin Pair Status
Configuring MySQLmysqld Command-Line Optionsmy.cnf Option FilesInstalling Many Servers on the Same MachineRunning Multiple MySQL Servers on the Same Machine
General Security GuidelinesHow to Make MySQL Secure Against CrackersStartup Options for mysqld Concerning SecuritySecurity Issues with LOAD DATA LOCALWhat the Privilege System DoesHow the Privilege System WorksPrivileges Provided by MySQLConnecting to the MySQL serverAccess Control, Stage 1: Connection VerificationAccess Control, Stage 2: Request VerificationCauses of Access denied Errors
GRANT and REVOKE SyntaxMySQL Usernames and PasswordsWhen Privilege Changes Take EffectSetting Up the Initial MySQL PrivilegesAdding New Users to MySQLSetting Up PasswordsKeeping Your Password SecureUsing Secure ConnectionsBasicsRequirementsGRANT options
Database BackupsBACKUP TABLE SyntaxRESTORE TABLE SyntaxCHECK TABLE SyntaxREPAIR TABLE SyntaxUsing myisamchk for Table Maintenance and Crash Recoverymyisamchk invocation syntaxGeneral options for myisamchkCheck options for myisamchkRepair options for myisamchkOther options for myisamchkmyisamchk memory usageUsing myisamchk for crash recoveryHow to check tables for errorsHow to repair tablesTable optimisationSetting Up a Table Maintenance RegimenGetting Information About a Table
OPTIMIZE TABLE SyntaxANALYZE TABLE SyntaxFLUSH SyntaxRESET SyntaxKILL SyntaxSHOW SyntaxRetrieving information about databases, tables, columns, and indexesSHOW TABLE STATUSSHOW STATUSSHOW VARIABLESSHOW LOGSSHOW PROCESSLISTSHOW GRANTS FORSHOW CREATE TABLE
The Character Set Used for Data and SortingGerman character setNon-English Error MessagesAdding a New Character SetThe Character Definition ArraysString Collating SupportMulti-Byte Character SupportProblems with Character Sets
Overview of the Server-Side Scripts and Utilitiessafe_mysqld, the Wrapper Around mysqldmysqld_multi, Program for Managing Multiple MySQL serversmyisampack, the MySQL Compressed Read-Only Table Generatormysqld-max, an Extended mysqld Server
Overview of the Client-Side Scripts and UtilitiesThe Command-Line Toolmysqladmin, Administrating a MySQL ServerUsing mysqlcheck for Table Maintenance and Crash Recoverymysqldump, Dumping Table Structure and Datamysqlhotcopy, Copying MySQL Databases and Tablesmysqlimport, Importing Data from Text FilesShowing Databases, Tables, and Columnsperror, Explaining Error CodesHow to Run SQL Commands from a Text File
The Error LogThe General Query LogThe Update LogThe Binary Update LogThe Slow Query LogLog File Maintenance
IntroductionReplication Implementation OverviewHow to Set Up ReplicationReplication Features and Known ProblemsReplication Options in my.cnfSQL Commands Related to ReplicationReplication FAQTroubleshooting Replication
Optimisation OverviewMySQL Design Limitations/TradeoffsPortabilityWhat Have We Used MySQL for?The MySQL Benchmark SuiteUsing Your Own Benchmarks
EXPLAIN Syntax (Get Information About a SELECT)Estimating Query PerformanceSpeed of SELECT QueriesHow MySQL Optimises WHERE ClausesHow MySQL Optimises DISTINCTHow MySQL Optimises LEFT JOIN and RIGHT JOINHow MySQL Optimises ORDER BYHow MySQL Optimises LIMITSpeed of INSERT QueriesSpeed of UPDATE QueriesSpeed of DELETE QueriesOther Optimisation Tips
How MySQL Locks TablesTable Locking Issues
Design ChoicesGet Your Data as Small as PossibleHow MySQL Uses IndexesColumn IndexesMultiple-Column IndexesWhy So Many Open tables?How MySQL Opens and Closes TablesDrawbacks to Creating Large Numbers of Tables in the Same Database
System/Compile Time and Startup Parameter TuningTuning Server ParametersHow Compiling and Linking Affects the Speed of MySQLHow MySQL Uses MemoryHow MySQL Uses DNSSET Syntax
Using Symbolic LinksUsing symbolic links for databasesUsing symbolic links for tables
Language StructureLiterals: How to Write Strings and NumbersStringsNumbersHexadecimal valuesNULL valuesDatabase, Table, Index, Column, and Alias NamesCase Sensitivity in NamesUser VariablesComment SyntaxIs MySQL Picky About Reserved Words?
Numeric TypesDate and Time TypesY2K issues and datetypesThe DATETIME, DATE, and TIMESTAMP typesThe TIME typeThe YEAR typeString TypesThe CHAR and VARCHAR typesThe BLOB and TEXT typesThe ENUM typeThe SET typeChoosing the Right Type for a ColumnUsing Column Types from Other Database EnginesColumn Type Storage RequirementsStorage requirements for numeric typesStorage requirements for date and time typesStorage requirements for string types
Non-Type-Specific Operators and FunctionsParenthesesComparison operatorsLogical operatorsControl flow functionsString FunctionsString comparison functionsCase sensitivityNumeric FunctionsArithmetic operationsMathematical functionsDate and Time FunctionsCast FunctionsOther FunctionsBit functionsMiscellaneous functionsFunctions for Use with GROUP BY Clauses
SELECT SyntaxJOIN syntaxUNION syntaxHANDLER SyntaxINSERT SyntaxINSERT ... SELECT syntaxINSERT DELAYED SyntaxUPDATE SyntaxDELETE SyntaxTRUNCATE SyntaxREPLACE SyntaxLOAD DATA INFILE SyntaxDO Syntax
CREATE DATABASE SyntaxDROP DATABASE SyntaxCREATE TABLE SyntaxSilent column specification changesALTER TABLE SyntaxRENAME TABLE SyntaxDROP TABLE SyntaxCREATE INDEX SyntaxDROP INDEX Syntax
USE SyntaxDESCRIBE Syntax (Get Information About Columns)
BEGIN/COMMIT/ROLLBACK SyntaxLOCK TABLES/UNLOCK TABLES SyntaxSET TRANSACTION Syntax
Full-Text RestrictionsFine-Tuning MySQL Full-Text SearchFull-Text Search TODO
How the Query Cache OperatesQuery Cache ConfigurationQuery Cache Options in SELECTQuery Cache Status and Maintenance
MyISAM TablesSpace Needed for KeysMyISAM Table FormatsStatic (fixed-length) table characteristicsDynamic table characteristicsCompressed table characteristicsMyISAM Table ProblemsCorrupted MyISAM tablesClient is using or hasn’t closed the table properly
MERGE Table Problems
InnoDB Tables OverviewInnoDB Startup OptionsCreating InnoDB TablespaceIf something goes wrong in database creationCreating InnoDB TablesConverting MyISAM tables to InnoDBForeign key constraintsAdding and Removing InnoDB Data and Log FilesBacking up and Recovering an InnoDB DatabaseCheckpointsMoving an InnoDB Database to Another MachineInnoDB Transaction ModelConsistent readLocking readsNext-key locking: Avoiding the phantom problemLocks set by different SQL statements in InnoDBDeadlock detection and rollbackAn example of how the consistent read works in InnoDBPerformance Tuning TipsThe InnoDB monitorImplementation of Multi-VersioningTable and Index StructuresPhysical structure of an indexInsert bufferingAdaptive hash indexesPhysical Record StructureHow an auto-increment column works in InnoDBFile Space Management and Disk I/ODisk I/OFile space managementDefragmenting a tableError HandlingRestrictions on InnoDB TablesInnoDB Contact Information
Overview of BDB TablesInstalling BDBBDB Startup OptionsCharacteristics of BDB TablesThings We Need to Fix for BDB in the Near FutureOperating Systems Supported by BDBRestrictions on BDB TablesErrors that May Occur When Using BDB Tables
MySQL PHP APICommon Problems with MySQL and PHP
DBI with DBD::mysqlThe DBI InterfacePortable DBI methodsMySQL-specific methodsPortable DBI methodsMySQL-specific methodsMore DBI/DBD Information
How to Install MyODBCHow to Fill in the Various Fields in the ODBC Administrator ProgramConnect Parameters for MyODBCHow to Report Problems with MyODBCPrograms Known to Work with MyODBCHow to Get the Value of an AUTO_INCREMENT Column in ODBCReporting Problems with MyODBC
C API DatatypesC API Function OverviewC API Function Descriptionsmysql_affected_rows( )DescriptionReturn valuesErrorsExamplemysql_change_user( )DescriptionReturn valuesErrorsExamplemysql_character_set_name( )DescriptionReturn valuesErrorsmysql_close( )DescriptionReturn valuesErrorsmysql_connect( )DescriptionReturn valuesErrorsmysql_create_db( )DescriptionReturn valuesErrorsExamplemysql_data_seek( )DescriptionReturn valuesErrorsmysql_debug( )DescriptionReturn valuesErrorsExamplemysql_drop_db( )DescriptionReturn valuesErrorsExamplemysql_dump_debug_info( )DescriptionReturn valuesErrorsmysql_eof( )DescriptionReturn valuesErrorsExamplemysql_errno( )DescriptionReturn valuesErrorsmysql_error( )DescriptionReturn valuesErrorsmysql_escape_string( )mysql_fetch_field( )DescriptionReturn valuesErrorsExamplemysql_fetch_fields( )DescriptionReturn valuesErrorsExamplemysql_fetch_field_direct( )DescriptionReturn valuesErrorsExamplemysql_fetch_lengths( )DescriptionReturn valuesErrorsExamplemysql_fetch_row( )DescriptionReturn valuesErrorsExamplemysql_field_count( )DescriptionReturn valuesErrorsExamplemysql_field_seek( )DescriptionReturn valuesErrorsmysql_field_tell( )DescriptionReturn valuesErrorsmysql_free_result( )DescriptionReturn valuesErrorsmysql_get_client_info( )DescriptionReturn valuesErrorsmysql_get_host_info( )DescriptionReturn valuesErrorsmysql_get_proto_info( )DescriptionReturn valuesErrorsmysql_get_server_info( )DescriptionReturn valuesErrorsmysql_info( )DescriptionReturn valuesErrorsmysql_init( )DescriptionReturn valuesErrorsmysql_insert_id( )DescriptionReturn valuesErrorsmysql_kill( )DescriptionReturn valuesErrorsmysql_list_dbs( )DescriptionReturn valuesErrorsmysql_list_fields( )DescriptionReturn valuesErrorsmysql_list_processes( )DescriptionReturn valuesErrorsmysql_list_tables( )DescriptionReturn valuesErrorsmysql_num_fields( )DescriptionReturn valuesErrorsExamplemysql_num_rows( )DescriptionReturn valuesErrorsmysql_options( )DescriptionReturn valuesExamplemysql_ping( )DescriptionReturn valuesErrorsmysql_query( )DescriptionReturn valuesErrorsmysql_real_connect( )DescriptionReturn valuesErrorsExamplemysql_real_escape_string( )DescriptionExampleReturn valuesErrorsmysql_real_query( )DescriptionReturn valuesErrorsmysql_reload( )DescriptionReturn valuesErrorsmysql_row_seek( )DescriptionReturn valuesErrorsmysql_row_tell( )DescriptionReturn valuesErrorsmysql_select_db( )DescriptionReturn valuesErrorsmysql_shutdown( )DescriptionReturn valuesErrorsmysql_stat( )DescriptionReturn valuesErrorsmysql_store_result( )DescriptionReturn valuesErrorsmysql_thread_id( )DescriptionReturn valuesErrorsmysql_use_result( )DescriptionReturn valuesErrorsC Threaded Function Descriptionsmy_init( )DescriptionReturn valuesmysql_thread_init( )DescriptionReturn valuesmysql_thread_end( )DescriptionReturn valuesmysql_thread_safe( )DescriptionReturn valuesC Embedded Server Function Descriptionsmysql_server_init( )DescriptionExampleReturn valuesmysql_server_end( )DescriptionReturn valuesCommon Questions and Problems When Using the C APIWhy Is It that After mysql_query( ) Returns Success, mysql_store_result( ) Sometimes Returns NULL?What results can I get from a query?How can I get the unique ID for the last inserted row?Problems linking with the C APIBuilding Client ProgramsHow to Make a Threaded Clientlibmysqld, the Embedded MySQL Server LibraryOverview of the embedded MySQL server libraryCompiling programs with libmysqldRestrictions when using the embedded MySQL serverUsing option files with the embedded serverThings left to do in embedded server (TODO)A simple embedded server exampleLicensing the embedded server
Borland C++
MySQL InternalsMySQL ThreadsMySQL Test SuiteRunning the MySQL test suiteExtending the MySQL test suiteReporting bugs in the MySQL test suite
CREATE FUNCTION/DROP FUNCTION SyntaxAdding a New User-Definable FunctionUDF calling sequences for simple functionsUDF calling sequences for aggregate functionsArgument processingReturn values and error handlingCompiling and installing user-definable functionsAdding a New Native Function
Procedure AnalyseWriting a Procedure
How to Determine What Is Causing Problems
Access denied ErrorMySQL server has gone away ErrorCan't connect to [local] MySQL server ErrorHost '...' is blocked ErrorToo many connections ErrorSome non-transactional changed tables couldn't be rolled back ErrorOut of memory ErrorPacket too large ErrorCommunication Error/ Aborted ConnectionThe table is full ErrorCan't create/write to file ErrorCommands out of sync Error in ClientIgnoring user ErrorTable 'xxx' doesn't exist ErrorCan't initialize character set xxx errorFile Not Found
Problems When Linking with the MySQL Client LibraryHow to Run MySQL as a Normal UserProblems with File Permissions
What to Do if MySQL Keeps CrashingHow to Reset a Forgotten PasswordHow MySQL Handles a Full DiskWhere MySQL Stores Temporary FilesHow to Protect or Change the MySQL Socket File /tmp/mysql.sockTime Zone Problems
Case Sensitivity in SearchesProblems Using DATE ColumnsProblems with NULL ValuesProblems with aliasDeleting Rows from Related TablesSolving Problems with No Matching RowsProblems with Floating-Point Comparison
Problems with ALTER TABLEHow to Change the Order of Columns in a TableTEMPORARY TABLES Problems
APIs
Developers at MySQL AB
Debugging a MySQL ServerCompiling MySQL for DebuggingCreating Trace FilesDebugging mysqld under gdbUsing a Stack TraceUsing Log Files to Find Cause of Errors in mysqldMaking a Test Case When You Experience Table Corruption

Content preview from MySQL Reference Manual

InnoDB Tables

InnoDB Tables Overview

InnoDB provides MySQL with a transaction-safe (ACID-compliant) table handler with commit, rollback, and crash recovery capabilities. InnoDB does locking on row level and also provides an Oracle-style consistent non-locking read in SELECTs. These features increase multi-user concurrency and performance. There is no need for lock escalation in InnoDB because row level locks in InnoDB fit in very little space. InnoDB tables support FOREIGN KEY constraints as the first table type in MySQL.

InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine.

Technically, InnoDB is a complete database backend placed under MySQL. InnoDB has its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a tablespace, which may consist of several files. This is different from, for example, MyISAM tables, where each table is stored as a separate file. InnoDB tables can be of any size also on those operating systems where file-size is limited to 2G.

You can find the latest information about InnoDB at http://www.innodb.com/. The most up-to-date version of the InnoDB manual is always placed there, and you can also order commercial licenses and support for InnoDB.

InnoDB is currently (October 2001) used in production at several large database sites requiring high performance. The famous Internet news site Slashdot.org ...