MySQL Reference Manual for version 3.23.15-alpha. - Table of Contents
MySQL Reference Manual for version 3.23.15-alpha.
1 General Information about MySQL
1.1 What is MySQL?
1.2 About this manual
1.2.1 Conventions used in this manual
1.3 History of MySQL
1.4 Books about MySQL
1.5 The main features of MySQL
1.6 How stable is MySQL?
1.7 Year 2000 compliance
1.8 General SQL information and tutorials
1.9 Useful MySQL-related links
2 MySQL mailing lists and how to ask questions or report errors (bugs)
2.1 The MySQL mailing lists
2.2 Asking questions or reporting bugs
2.3 How to report bugs or problems
2.4 Guidelines for answering questions on the mailing list
3 MySQL licensing and support
3.1 MySQL licensing policy
3.2 MySQL licensing policy on Win32
3.3 Copyrights used by MySQL
3.3.1 Possible future copyright changes
3.4 Distributing MySQL commercially
3.5 Example licensing situations
3.5.1 Selling products that use MySQL
3.5.2 Selling MySQL-related services
3.5.3 ISP MySQL services
3.5.4 Running a web server using MySQL
3.6 MySQL licensing and support costs
3.6.1 Payment information
3.6.2 Contact information
3.7 Types of commercial support
3.7.1 Basic email support
3.7.2 Extended email support
3.7.3 Login support
3.7.4 Extended login support
4 Installing MySQL
4.1 How to get MySQL
4.2 Operating systems supported by MySQL
4.3 Which MySQL version to use
4.4 How and when updates are released
4.5 Installation layouts
4.6 Installing a MySQL binary distribution
4.6.1 Linux RPM notes
4.6.2 Building client programs
4.6.3 System-specific issues
4.6.3.1 Linux notes
4.6.3.2 HP-UX notes
4.7 Installing a MySQL source distribution
4.7.1 Quick installation overview
4.7.2 Applying patches
4.7.3 Typical configure options
4.8 Problems compiling?
4.9 MIT-pthreads notes
4.10 Perl installation comments
4.10.1 Installing Perl on Unix
4.10.2 Installing ActiveState Perl on Win32
4.10.3 Installing the MySQL Perl distribution on Win32
4.10.4 Problems using the Perl DBI/DBD interface
4.11 System-specific issues
4.11.1 Solaris notes
4.11.2 Solaris 2.7 notes
4.11.3 Solaris x86 notes
4.11.4 SunOS 4 notes
4.11.5 Linux notes (all Linux versions)
4.11.5.1 Linux-x86 notes
4.11.5.2 RedHat 5.0 notes
4.11.5.3 RedHat 5.1 notes
4.11.5.4 Linux-SPARC notes
4.11.5.5 Linux-Alpha notes
4.11.5.6 MkLinux notes
4.11.5.7 Qube2 Linux notes
4.11.6 Alpha-DEC-Unix notes
4.11.7 Alpha-DEC-OSF1 notes
4.11.8 SGI-Irix notes
4.11.9 FreeBSD notes
4.11.10 NetBSD notes
4.11.11 OpenBSD 2.5 notes
4.11.12 BSD/OS notes
4.11.12.1 BSD/OS 2.x notes
4.11.12.2 BSD/OS 3.x notes
4.11.12.3 BSD/OS 4.x notes
4.11.13 SCO notes
4.11.14 SCO Unixware 7.0 notes
4.11.15 IBM-AIX notes
4.11.16 HP-UX 10.20 notes
4.11.17 HP-UX 11.x notes
4.11.18 MacOS X notes
4.12 Win32 notes
4.12.1 Installing MySQL on Win32
4.12.2 Starting MySQL on Win95 / Win98
4.12.3 Starting MySQL on NT
4.12.4 Running MySQL on Win32
4.12.5 Connecting to a remote MySQL from Win32 with SSH
4.12.6 MySQL-Win32 compared to Unix MySQL
4.13 OS/2 notes
4.14 TcX binaries
4.15 Post-installation setup and testing
4.15.1 Problems running mysql_install_db
4.15.2 Problems starting the MySQL server
4.15.3 Starting and stopping MySQL automatically
4.15.4 Option files
4.16 Is there anything special to do when upgrading/downgrading MySQL?
4.16.1 Upgrading from a 3.22 version to 3.23
4.16.2 Upgrading from a 3.21 version to 3.22
4.16.3 Upgrading from a 3.20 version to 3.21
4.16.4 Upgrading to another architecture
5 How standards-compatible is MySQL?
5.1 MySQL extensions to ANSI SQL92
5.2 Runnning MySQL in ANSI mode
5.3 MySQL differences compared to ANSI SQL92
5.4 Functionality missing from MySQL
5.4.1 Sub-selects
5.4.2 SELECT INTO TABLE
5.4.3 Transactions
5.4.4 Stored procedures and triggers
5.4.5 Foreign Keys
5.4.5.1 Reasons NOT to use foreign keys
5.4.6 Views
5.4.7 `--' as the start of a comment
5.5 What standards does MySQL follow?
5.6 How to cope without COMMIT/ROLLBACK
6 The MySQL access privilege system
6.1 General security
6.2 How to make MySQL secure against crackers
6.3 What the privilege system does
6.4 MySQL user names and passwords
6.5 Connecting to the MySQL server
6.6 Keeping your password secure
6.7 Privileges provided by MySQL
6.8 How the privilege system works
6.9 Access control, stage 1: Connection verification
6.10 Access control, stage 2: Request verification
6.11 When privilege changes take effect
6.12 Setting up the initial MySQL privileges
6.13 Adding new user privileges to MySQL
6.14 How to set up passwords
6.15 Causes of Access denied errors
7 MySQL language reference
7.1 Literals: how to write strings and numbers
7.1.1 Strings
7.1.2 Numbers
7.1.3 Hexadecimal values
7.1.4 NULL values
7.1.5 Database, table, index, column and alias names
7.1.5.1 Case sensitivity in names
7.2 User variables
7.3 Column types
7.3.1 Column type storage requirements
7.3.5 Numeric types
7.3.6 Date and time types
7.3.6.1 Y2K issues and date types
7.3.6.2 The DATETIME, DATE and TIMESTAMP types
7.3.6.3 The TIME type
7.3.6.4 The YEAR type
7.3.7 String types
7.3.7.1 The CHAR and VARCHAR types
7.3.7.2 The BLOB and TEXT types
7.3.7.3 The ENUM type
7.3.7.4 The SET type
7.3.8 Choosing the right type for a column
7.3.9 Column indexes
7.3.10 Multiple-column indexes
7.3.11 Using column types from other database engines
7.4 Functions for use in SELECT and WHERE clauses
7.4.1 Grouping functions
7.4.2 Normal arithmetic operations
7.4.3 Bit functions
7.4.4 Logical operations
7.4.5 Comparison operators
7.4.6 String comparison functions
7.4.7 Cast operators
7.4.8 Control flow functions
7.4.9 Mathematical functions
7.4.10 String functions
7.4.11 Date and time functions
7.4.12 Miscellaneous functions
7.4.13 Functions for use with GROUP BY clauses
7.5 CREATE DATABASE syntax
7.6 DROP DATABASE syntax
7.7 CREATE TABLE syntax
7.7.1 Silent column specification changes
7.8 ALTER TABLE syntax
7.9 DROP TABLE syntax
7.10 OPTIMIZE TABLE syntax
7.11 CHECK TABLE syntax
7.12 REPAIR TABLE syntax
7.13 DELETE syntax
7.14 SELECT syntax
7.15 JOIN syntax
7.16 INSERT syntax
7.17 REPLACE syntax
7.18 LOAD DATA INFILE syntax
7.19 UPDATE syntax
7.20 USE syntax
7.21 FLUSH syntax (clearing caches)
7.22 KILL syntax
7.23 SHOW syntax (Get information about tables, columns,...)
7.24 EXPLAIN syntax (Get information about a SELECT)
7.25 DESCRIBE syntax (Get information about columns)
7.26 LOCK TABLES/UNLOCK TABLES syntax
7.27 SET syntax
7.28 GRANT and REVOKE syntax
7.29 CREATE INDEX syntax
7.30 DROP INDEX syntax
7.31 Comment syntax
7.32 CREATE FUNCTION/DROP FUNCTION syntax
7.33 Is MySQL picky about reserved words?
8 MySQL table types
8.1 MyISAM tables
8.1.1 Space needed for keys
8.1.2 MyISAM table formats
8.1.2.1 Static (Fixed-length) table characteristics
8.1.2.2 Dynamic table characteristics
8.1.2.3 Compressed table characteristics
8.2 ISAM tables
8.3 HEAP tables
9 MySQL Tutorial
9.1 Connecting to and disconnecting from the server
9.2 Entering queries
9.3 Examples of common queries
9.3.1 The maximum value for a column
9.3.2 The row holding the maximum of a certain column
9.3.3 Maximum of column: per group: only the values
9.3.4 The rows holding the group-wise maximum of a certain field
9.3.5 Using foreign keys
9.4 Searching on two keys
9.5 Creating and using a database
9.5.1 Creating and selecting a database
9.5.2 Creating a table
9.5.3 Loading data into a table
9.5.4 Retrieving information from a table
9.5.4.1 Selecting all data
9.5.4.2 Selecting particular rows
9.5.4.3 Selecting particular columns
9.5.4.4 Sorting rows
9.5.4.5 Date calculations
9.5.4.6 Working with NULL values
9.5.4.7 Pattern matching
9.5.4.8 Counting rows
9.5.5 Using more than one table
9.6 Getting information about databases and tables
9.7 Using mysql in batch mode
9.8 Queries from twin project
9.8.1 Find all non-distributed twins
9.8.2 Show a table on twin pair status
10 MySQL server functions
10.1 What languages are supported by MySQL?
10.1.1 The character set used for data and sorting
10.1.2 Adding a new character set
10.1.3 String collating support
10.1.4 Multi-byte character support
10.2 How big MySQL tables can be
11 Getting maximum performance from MySQL
11.1 Optimization overview
11.2 System/Compile time and startup parameter tuning
11.2.1 How compiling and linking affects the speed of MySQL
11.2.2 Disk issues
11.2.2.1 Using symbolic links for databases and tables
11.2.3 Tuning server parameters
11.2.4 How MySQL opens and closes tables
11.2.5 Drawbacks of creating large numbers of tables in the same database
11.2.6 Why so many open tables?
11.2.7 How MySQL uses memory
11.2.8 How MySQL locks tables
11.2.9 Table locking issues
11.3 Get your data as small as possible
11.4 MySQL index use
11.5 Speed of queries that access or update data
11.5.1 Estimating query performance
11.5.2 Speed of SELECT queries
11.5.3 How MySQL optimizes WHERE clauses
11.5.4 How MySQL optimizes LEFT JOIN
11.5.5 How MySQL optimizes LIMIT
11.5.6 Speed of INSERT queries
11.5.7 Speed of UPDATE queries
11.5.8 Speed of DELETE queries
11.6 Other optimization tips
11.7 Using your own benchmarks
11.8 Design choices
11.9 MySQL design limitations/tradeoffs
11.10 Portability
11.11 What have we used MySQL for?
12 The MySQL benchmark suite
13 MySQL Utilites
13.1 Overview of the different MySQL programs
13.2 The command line tool
13.3 Administering a MySQL server
13.4 Dumping the structure and data from MySQL databases and tables
13.5 Importing data from text files
13.6 Showing databases, tables and columns
13.7 The MySQL compressed read-only table generator
14 Maintaining a MySQL installation
14.1 Using myisamchk for table maintenance and crash recovery
14.1.1 myisamchk invocation syntax
14.1.2 myisamchk memory usage
14.2 Setting up a table maintenance regimen
14.3 Getting information about a table
14.4 Using myisamchk for crash recovery
14.4.1 How to check tables for errors
14.4.2 How to repair tables
14.4.3 Table optimization
14.5 Log file maintenance
15 Adding new functions to MySQL
15.1 Adding a new user-definable function
15.1.1 UDF calling sequences
15.1.2 Argument processing
15.1.3 Return values and error handling
15.1.4 Compiling and installing user-definable functions
15.2 Adding a new native function
16 Adding new procedures to MySQL
16.1 Procedure analyse
16.2 Writing a procedure.
17 MySQL ODBC Support
17.1 Operating systems supported by MyODBC
17.2 How to fill in the various fields in the ODBC administrator program
17.3 How to report problems with MyODBC
17.4 Programs known to work with MyODBC
17.5 How to get the value of an AUTO_INCREMENT column in ODBC
17.6 Reporting problems with MyODBC
18 Using MySQL with some common programs
18.1 Using MySQL with Apache
19 Problems and common errors
19.1 How to determinate what is causing problems
19.2 What to do if MySQL keeps crashing
19.3 Some common errors when using MySQL
19.3.1 MySQL server has gone away error
19.3.2 Can't connect to [local] MySQL server error
19.3.3 Host '...' is blocked error
19.3.4 Too many connections error
19.3.5 Out of memory error
19.3.6 Packet too large error
19.3.7 The table is full error
19.3.8 Commands out of sync error in client
19.3.9 Ignoring user error
19.3.10 Table 'xxx' doesn't exist error
19.4 How MySQL handles a full disk
19.5 How to run SQL commands from a text file
19.6 Where MySQL stores temporary files
19.7 How to protect `/tmp/mysql.sock' from being deleted
19.8 Access denied error
19.9 How to run MySQL as a normal user
19.10 How to reset a forgotten password.
19.11 Problems with file permissions
19.12 File not found
19.13 Problems using DATE columns
19.14 Timezone problems
19.15 Case sensitivity in searches
19.16 Problems with NULL values
19.17 Problems with alias
19.18 Deleting rows from related tables
19.19 Solving problems with no matching rows
19.20 Problems with ALTER TABLE.
19.21 How to change the order of columns in a table
20 Solving some common problems with MySQL
20.1 Database replication
20.2 Database backups
20.3 The update log
20.4 Running multiple MySQL servers on the same machine
21 MySQL client tools and APIs
21.1 MySQL C API
21.2 C API datatypes
21.3 C API function overview
21.4 C API function descriptions
21.4.1 mysql_affected_rows()
21.4.2 mysql_close()
21.4.3 mysql_connect()
21.4.4 mysql_change_user()
21.4.5 mysql_create_db()
21.4.6 mysql_data_seek()
21.4.7 mysql_debug()
21.4.8 mysql_drop_db()
21.4.9 mysql_dump_debug_info()
21.4.10 mysql_eof()
21.4.11 mysql_errno()
21.4.12 mysql_error()
21.4.13 mysql_escape_string()
21.4.14 mysql_fetch_field()
21.4.15 mysql_fetch_fields()
21.4.16 mysql_fetch_field_direct()
21.4.17 mysql_fetch_lengths()
21.4.18 mysql_fetch_row()
21.4.19 mysql_field_count()
21.4.20 mysql_field_seek()
21.4.21 mysql_field_tell()
21.4.22 mysql_free_result()
21.4.23 mysql_get_client_info()
21.4.24 mysql_get_host_info()
21.4.25 mysql_get_proto_info()
21.4.26 mysql_get_server_info()
21.4.27 mysql_info()
21.4.28 mysql_init()
21.4.29 mysql_insert_id()
21.4.30 mysql_kill()
21.4.31 mysql_list_dbs()
21.4.32 mysql_list_fields()
21.4.33 mysql_list_processes()
21.4.34 mysql_list_tables()
21.4.35 mysql_num_fields()
21.4.36 mysql_num_rows()
21.4.37 mysql_options()
21.4.38 mysql_ping()
21.4.39 mysql_query()
21.4.40 mysql_real_connect()
21.4.41 mysql_real_escape_string()
21.4.42 mysql_real_query()
21.4.43 mysql_reload()
21.4.44 mysql_row_seek()
21.4.45 mysql_row_tell()
21.4.46 mysql_select_db()
21.4.47 mysql_shutdown()
21.4.48 mysql_stat()
21.4.49 mysql_store_result()
21.4.50 mysql_thread_id()
21.4.51 mysql_use_result()
21.4.52 Why is it that after mysql_query() returns success, mysql_store_result() sometimes returns NULL?
21.4.53 What results can I get from a query?
21.4.54 How can I get the unique ID for the last inserted row?
21.4.55 Problems linking with the C API
21.4.56 How to make a thread-safe client
21.5 MySQL Perl API
21.5.1 DBI with DBD::mysql
21.5.2 The DBI interface
21.5.3 More DBI/DBD information
21.6 MySQL Eiffel wrapper
21.7 MySQL Java connectivity (JDBC)
21.8 MySQL PHP API
21.8.1 Common problems with MySQL and PHP
21.9 MySQL C++ APIs
21.10 MySQL Python APIs
21.11 MySQL Tcl APIs
22 How MySQL compares to other databases
22.1 How MySQL compares to mSQL
22.1.1 How to convert mSQL tools for MySQL
22.1.2 How mSQL and MySQL client/server communications protocols differ
22.1.3 How mSQL 2.0 SQL syntax differs from MySQL
22.2 How MySQL compares to PostgreSQL
23 MySQL internals
23.1 MySQL threads
A Environment variables
B Some MySQL users
B.1 General news sites
B.2 Some Web search engines
B.3 Some Information search engines concentrated on some area
B.4 Online magazines
B.5 Web sites that use MySQL as a backend
B.6 Some Domain/Internet/Web and related services
B.7 Web sites that use PHP and MySQL
B.8 Some MySQL consultants
B.9 Programming
B.10 Uncategorized pages
C Contributed programs
C.1 API's
C.2 Clients
C.3 Web tools
C.4 Authentication tools
C.5 Converters
C.6 Using MySQL with other products
C.7 Useful tools
C.8 RPMs for common tools (Most are for RedHat 6.1)
C.9 Useful functions
C.10 Uncategorized
D Contributors to MySQL
E MySQL change history
E.1 Changes in release 3.23.x (Released as alpha)
E.1.1 Changes in release 3.23.15
E.1.2 Changes in release 3.23.14
E.1.3 Changes in release 3.23.13
E.1.4 Changes in release 3.23.12
E.1.5 Changes in release 3.23.11
E.1.6 Changes in release 3.23.10
E.1.7 Changes in release 3.23.9
E.1.8 Changes in release 3.23.8
E.1.9 Changes in release 3.23.7
E.1.10 Changes in release 3.23.6
E.1.11 Changes in release 3.23.5
E.1.12 Changes in release 3.23.4
E.1.13 Changes in release 3.23.3
E.1.14 Changes in release 3.23.2
E.1.15 Changes in release 3.23.1
E.1.16 Changes in release 3.23.0
E.2 Changes in release 3.22.x
E.2.1 Changes in release 3.22.34
E.2.2 Changes in release 3.22.33
E.2.3 Changes in release 3.22.32
E.2.4 Changes in release 3.22.31
E.2.5 Changes in release 3.22.30
E.2.6 Changes in release 3.22.29
E.2.7 Changes in release 3.22.28
E.2.8 Changes in release 3.22.27
E.2.9 Changes in release 3.22.26
E.2.10 Changes in release 3.22.25
E.2.11 Changes in release 3.22.24
E.2.12 Changes in release 3.22.23
E.2.13 Changes in release 3.22.22
E.2.14 Changes in release 3.22.21
E.2.15 Changes in release 3.22.20
E.2.16 Changes in release 3.22.19
E.2.17 Changes in release 3.22.18
E.2.18 Changes in release 3.22.17
E.2.19 Changes in release 3.22.16
E.2.20 Changes in release 3.22.15
E.2.21 Changes in release 3.22.14
E.2.22 Changes in release 3.22.13
E.2.23 Changes in release 3.22.12
E.2.24 Changes in release 3.22.11
E.2.25 Changes in release 3.22.10
E.2.26 Changes in release 3.22.9
E.2.27 Changes in release 3.22.8
E.2.28 Changes in release 3.22.7
E.2.29 Changes in release 3.22.6
E.2.30 Changes in release 3.22.5
E.2.31 Changes in release 3.22.4
E.2.32 Changes in release 3.22.3
E.2.33 Changes in release 3.22.2
E.2.34 Changes in release 3.22.1
E.2.35 Changes in release 3.22.0
E.3 Changes in release 3.21.x
E.3.1 Changes in release 3.21.33
E.3.2 Changes in release 3.21.32
E.3.3 Changes in release 3.21.31
E.3.4 Changes in release 3.21.30
E.3.5 Changes in release 3.21.29
E.3.6 Changes in release 3.21.28
E.3.7 Changes in release 3.21.27
E.3.8 Changes in release 3.21.26
E.3.9 Changes in release 3.21.25
E.3.10 Changes in release 3.21.24
E.3.11 Changes in release 3.21.23
E.3.12 Changes in release 3.21.22
E.3.13 Changes in release 3.21.21a
E.3.14 Changes in release 3.21.21
E.3.15 Changes in release 3.21.20
E.3.16 Changes in release 3.21.19
E.3.17 Changes in release 3.21.18
E.3.18 Changes in release 3.21.17
E.3.19 Changes in release 3.21.16
E.3.20 Changes in release 3.21.15
E.3.21 Changes in release 3.21.14b
E.3.22 Changes in release 3.21.14a
E.3.23 Changes in release 3.21.13
E.3.24 Changes in release 3.21.12
E.3.25 Changes in release 3.21.11
E.3.26 Changes in release 3.21.10
E.3.27 Changes in release 3.21.9
E.3.28 Changes in release 3.21.8
E.3.29 Changes in release 3.21.7
E.3.30 Changes in release 3.21.6
E.3.31 Changes in release 3.21.5
E.3.32 Changes in release 3.21.4
E.3.33 Changes in release 3.21.3
E.3.34 Changes in release 3.21.2
E.3.35 Changes in release 3.21.0
E.4 Changes in release 3.20.x
E.4.1 Changes in release 3.20.18
E.4.2 Changes in release 3.20.17
E.4.3 Changes in release 3.20.16
E.4.4 Changes in release 3.20.15
E.4.5 Changes in release 3.20.14
E.4.6 Changes in release 3.20.13
E.4.7 Changes in release 3.20.11
E.4.8 Changes in release 3.20.10
E.4.9 Changes in release 3.20.9
E.4.10 Changes in release 3.20.8
E.4.11 Changes in release 3.20.7
E.4.12 Changes in release 3.20.6
E.4.13 Changes in release 3.20.3
E.4.14 Changes in release 3.20.0
E.5 Changes in release 3.19.x
E.5.1 Changes in release 3.19.5
E.5.2 Changes in release 3.19.4
E.5.3 Changes in release 3.19.3
F Known errors and design deficiencies in MySQL
G List of things we want to add to MySQL in the future (The TODO)
G.1 Things that must done in the real near future
G.2 Things that have to be done sometime
G.3 Some things we don't have any plans to do
H Comments on porting to other systems
H.1 Debugging a MySQL server
H.2 Debugging a MySQL client
H.3 Comments about RTS threads
H.4 Differences between different thread packages
I Description of MySQL regular expression syntax
J What is Unireg?
K The MySQL server license for non Microsoft operating systems
L The MySQL license for Microsoft operating systems
SQL command, type and function index
Concept Index
This document was generated on 1 May 2000 using the
texi2html
translator version 1.52 (extended by davida@detron.se).