MySql

语言

版本

Preface and Legal Notices
General Information
- About This Manual
- Typographical and Syntax Conventions
- Overview of the MySQL Database Management System
- What Is New in MySQL 5.7
- Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 5.7
- MySQL Information Sources
- How to Report Bugs or Problems
- MySQL Standards Compliance
- Credits
Installing and Upgrading MySQL
- General Installation Guidance
- Installing MySQL on Unix/Linux Using Generic Binaries
- Installing MySQL on Microsoft Windows
- Installing MySQL on macOS
- Installing MySQL on Linux
- Installing MySQL Using Unbreakable Linux Network (ULN)
- Installing MySQL on Solaris
  - Installing MySQL on Solaris Using a Solaris PKG
- Installing MySQL on FreeBSD
- Installing MySQL from Source
- Postinstallation Setup and Testing
- Upgrading MySQL
- Downgrading MySQL
- Perl Installation Notes
Tutorial
- Connecting to and Disconnecting from the Server
- Entering Queries
- Creating and Using a Database
- Getting Information About Databases and Tables
- Using mysql in Batch Mode
- Examples of Common Queries
- Using MySQL with Apache
MySQL Programs
- Overview of MySQL Programs
- Using MySQL Programs
- Server and Server-Startup Programs
- Installation-Related Programs
- Client Programs
- Administrative and Utility Programs
- Program Development Utilities
- Miscellaneous Programs
- Environment Variables
- Unix Signal Handling in MySQL
MySQL Server Administration
- The MySQL Server
- The MySQL Data Directory
- The mysql System Database
- MySQL Server Logs
- MySQL Server Plugins
- MySQL Server User-Defined Functions
- Running Multiple MySQL Instances on One Machine
- Tracing mysqld Using DTrace
  - mysqld DTrace Probe Reference
Security
- General Security Issues
- Access Control and Account Management
- Using Encrypted Connections
- Security Plugins
- SELinux
Backup and Recovery
- Backup and Recovery Types
- Database Backup Methods
- Example Backup and Recovery Strategy
- Using mysqldump for Backups
- Point-in-Time (Incremental) Recovery
  - Point-in-Time Recovery Using Binary Log
  - Point-in-Time Recovery Using Event Positions
- MyISAM Table Maintenance and Crash Recovery
Optimization
- Optimization Overview
- Optimizing SQL Statements
- Optimization and Indexes
- Optimizing Database Structure
- Optimizing for InnoDB Tables
- Optimizing for MyISAM Tables
- Optimizing for MEMORY Tables
- Understanding the Query Execution Plan
- Controlling the Query Optimizer
- Buffering and Caching
- Optimizing Locking Operations
- Optimizing the MySQL Server
- Measuring Performance (Benchmarking)
- Examining Server Thread (Process) Information
Language Structure
- Literal Values
- Schema Object Names
- Keywords and Reserved Words
- User-Defined Variables
- Expressions
- Comment Syntax
Character Sets, Collations, Unicode
- Character Sets and Collations in General
- Character Sets and Collations in MySQL
  - Character Set Repertoire
  - UTF-8 for Metadata
- Specifying Character Sets and Collations
- Connection Character Sets and Collations
- Configuring Application Character Set and Collation
- Error Message Character Set
- Column Character Set Conversion
- Collation Issues
- Unicode Support
- Supported Character Sets and Collations
- Restrictions on Character Sets
- Setting the Error Message Language
- Adding a Character Set
- Adding a Collation to a Character Set
- Character Set Configuration
- MySQL Server Locale Support
Data Types
- Numeric Data Types
- Date and Time Data Types
- String Data Types
- Spatial Data Types
- The JSON Data Type
- Data Type Default Values
- Data Type Storage Requirements
- Choosing the Right Type for a Column
- Using Data Types from Other Database Engines
Functions and Operators
- Function and Operator Reference
- Type Conversion in Expression Evaluation
- Operators
  - Operator Precedence
  - Comparison Functions and Operators
  - Logical Operators
  - Assignment Operators
- Control Flow Functions
- Numeric Functions and Operators
  - Arithmetic Operators
  - Mathematical Functions
- Date and Time Functions
- String Functions and Operators
  - String Comparison Functions and Operators
  - Regular Expressions
  - Character Set and Collation of Function Results
- What Calendar Is Used By MySQL?
- Full-Text Search Functions
  - Natural Language Full-Text Searches
  - Boolean Full-Text Searches
  - Full-Text Searches with Query Expansion
  - Full-Text Stopwords
  - Full-Text Restrictions
  - Fine-Tuning MySQL Full-Text Search
  - Adding a Collation for Full-Text Indexing
  - ngram Full-Text Parser
  - MeCab Full-Text Parser Plugin
- Cast Functions and Operators
- XML Functions
- Bit Functions and Operators
- Encryption and Compression Functions
- Locking Functions
- Information Functions
- Spatial Analysis Functions
  - Spatial Function Reference
  - Argument Handling by Spatial Functions
  - Functions That Create Geometry Values from WKT Values
  - Functions That Create Geometry Values from WKB Values
  - MySQL-Specific Functions That Create Geometry Values
  - Geometry Format Conversion Functions
  - Geometry Property Functions
  - Spatial Operator Functions
  - Functions That Test Spatial Relations Between Geometry Objects
    - Spatial Relation Functions That Use Object Shapes
    - Spatial Relation Functions That Use Minimum Bounding Rectangles
  - Spatial Geohash Functions
  - Spatial GeoJSON Functions
  - Spatial Convenience Functions
- JSON Functions
  - JSON Function Reference
  - Functions That Create JSON Values
  - Functions That Search JSON Values
  - Functions That Modify JSON Values
  - Functions That Return JSON Value Attributes
  - JSON Utility Functions
- Functions Used with Global Transaction Identifiers (GTIDs)
- MySQL Enterprise Encryption Functions
  - MySQL Enterprise Encryption Installation
  - MySQL Enterprise Encryption Usage and Examples
  - MySQL Enterprise Encryption Function Reference
  - MySQL Enterprise Encryption Function Descriptions
- Aggregate Functions
  - Aggregate Function Descriptions
  - GROUP BY Modifiers
  - MySQL Handling of GROUP BY
  - Detection of Functional Dependence
- Miscellaneous Functions
- Precision Math
  - Types of Numeric Values
  - DECIMAL Data Type Characteristics
  - Expression Handling
  - Rounding Behavior
  - Precision Math Examples
SQL Statements
- Data Definition Statements
- Data Manipulation Statements
- Transactional and Locking Statements
- Replication Statements
- Prepared Statements
- Compound Statements
- Database Administration Statements
- Utility Statements
The InnoDB Storage Engine
- Introduction to InnoDB
  - Benefits of Using InnoDB Tables
  - Best Practices for InnoDB Tables
  - Verifying that InnoDB is the Default Storage Engine
  - Testing and Benchmarking with InnoDB
  - Turning Off InnoDB
- InnoDB and the ACID Model
- InnoDB Multi-Versioning
- InnoDB Architecture
- InnoDB In-Memory Structures
  - Buffer Pool
  - Change Buffer
  - Adaptive Hash Index
  - Log Buffer
- InnoDB On-Disk Structures
  - Tables
  - Indexes
  - Tablespaces
  - InnoDB Data Dictionary
  - Doublewrite Buffer
  - Redo Log
  - Undo Logs
- InnoDB Locking and Transaction Model
  - InnoDB Locking
  - InnoDB Transaction Model
  - Locks Set by Different SQL Statements in InnoDB
  - Phantom Rows
  - Deadlocks in InnoDB
- InnoDB Configuration
  - InnoDB Startup Configuration
  - Configuring InnoDB for Read-Only Operation
  - InnoDB Buffer Pool Configuration
  - Configuring the Memory Allocator for InnoDB
  - Configuring Thread Concurrency for InnoDB
  - Configuring the Number of Background InnoDB I/O Threads
  - Using Asynchronous I/O on Linux
  - Configuring InnoDB I/O Capacity
  - Configuring Spin Lock Polling
  - Purge Configuration
  - Configuring Optimizer Statistics for InnoDB
  - Configuring the Merge Threshold for Index Pages
- InnoDB Table and Page Compression
  - InnoDB Table Compression
  - InnoDB Page Compression
- InnoDB File-Format Management
  - Enabling File Formats
  - Verifying File Format Compatibility
    - Compatibility Check When InnoDB Is Started
    - Compatibility Check When a Table Is Opened
  - Identifying the File Format in Use
  - Modifying the File Format
- InnoDB Row Formats
- InnoDB Disk I/O and File Space Management
  - InnoDB Disk I/O
  - File Space Management
  - InnoDB Checkpoints
  - Defragmenting a Table
  - Reclaiming Disk Space with TRUNCATE TABLE
- InnoDB and Online DDL
  - Online DDL Operations
  - Online DDL Performance and Concurrency
  - Online DDL Space Requirements
  - Simplifying DDL Statements with Online DDL
  - Online DDL Failure Conditions
  - Online DDL Limitations
- InnoDB Data-at-Rest Encryption
- InnoDB Startup Options and System Variables
- InnoDB INFORMATION_SCHEMA Tables
  - InnoDB INFORMATION_SCHEMA Tables about Compression
  - InnoDB INFORMATION_SCHEMA Transaction and Locking Information
  - InnoDB INFORMATION_SCHEMA System Tables
  - InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables
  - InnoDB INFORMATION_SCHEMA Buffer Pool Tables
  - InnoDB INFORMATION_SCHEMA Metrics Table
  - InnoDB INFORMATION_SCHEMA Temporary Table Info Table
  - Retrieving InnoDB Tablespace Metadata from INFORMATION_SCHEMA.FILES
- InnoDB Integration with MySQL Performance Schema
  - Monitoring ALTER TABLE Progress for InnoDB Tables Using Performance Schema
  - Monitoring InnoDB Mutex Waits Using Performance Schema
- InnoDB Monitors
  - InnoDB Monitor Types
  - Enabling InnoDB Monitors
  - InnoDB Standard Monitor and Lock Monitor Output
- InnoDB Backup and Recovery
  - InnoDB Backup
  - InnoDB Recovery
- InnoDB and MySQL Replication
- InnoDB memcached Plugin
  - Benefits of the InnoDB memcached Plugin
  - InnoDB memcached Architecture
  - Setting Up the InnoDB memcached Plugin
  - Security Considerations for the InnoDB memcached Plugin
  - Writing Applications for the InnoDB memcached Plugin
  - The InnoDB memcached Plugin and Replication
  - InnoDB memcached Plugin Internals
  - Troubleshooting the InnoDB memcached Plugin
- InnoDB Troubleshooting
  - Troubleshooting InnoDB I/O Problems
  - Forcing InnoDB Recovery
  - Troubleshooting InnoDB Data Dictionary Operations
  - InnoDB Error Handling
- InnoDB Limits
- InnoDB Restrictions and Limitations
Alternative Storage Engines
- Setting the Storage Engine
- The MyISAM Storage Engine
- The MEMORY Storage Engine
- The CSV Storage Engine
  - Repairing and Checking CSV Tables
  - CSV Limitations
- The ARCHIVE Storage Engine
- The BLACKHOLE Storage Engine
- The MERGE Storage Engine
  - MERGE Table Advantages and Disadvantages
  - MERGE Table Problems
- The FEDERATED Storage Engine
- The EXAMPLE Storage Engine
- Other Storage Engines
- Overview of MySQL Storage Engine Architecture
  - Pluggable Storage Engine Architecture
  - The Common Database Server Layer
Replication
- Configuring Replication
- Replication Implementation
- Replication Solutions
- Replication Notes and Tips
Group Replication
- Group Replication Background
- Getting Started
  - Deploying Group Replication in Single-Primary Mode
  - Deploying Group Replication Locally
- Monitoring Group Replication
- Group Replication Operations
- Group Replication Security
- Group Replication System Variables
- Requirements and Limitations
  - Group Replication Requirements
  - Group Replication Limitations
- Frequently Asked Questions
- Group Replication Technical Details
MySQL Shell
Using MySQL as a Document Store
- Key Concepts
- Setting Up MySQL as a Document Store
  - Installing MySQL Shell
  - Starting MySQL Shell
- Quick-Start Guide: MySQL for Visual Studio
- X Plugin
InnoDB Cluster
- Introducing InnoDB Cluster
- Creating an InnoDB Cluster
- Using MySQL Router with InnoDB Cluster
- Working with InnoDB Cluster
- Known Limitations
MySQL NDB Cluster 7.5 and NDB Cluster 7.6
- NDB Cluster Overview
- NDB Cluster Installation
- Configuration of NDB Cluster
- NDB Cluster Programs
- Management of NDB Cluster
- NDB Cluster Replication
- NDB Cluster Release Notes
Partitioning
- Overview of Partitioning in MySQL
- Partitioning Types
- Partition Management
- Partition Pruning
- Partition Selection
- Restrictions and Limitations on Partitioning
Stored Objects
- Defining Stored Programs
- Using Stored Routines
- Using Triggers
  - Trigger Syntax and Examples
  - Trigger Metadata
- Using the Event Scheduler
- Using Views
- Stored Object Access Control
- Stored Program Binary Logging
- Restrictions on Stored Programs
- Restrictions on Views
INFORMATION_SCHEMA Tables
- Introduction
- The INFORMATION_SCHEMA CHARACTER_SETS Table
- The INFORMATION_SCHEMA COLLATIONS Table
- The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
- The INFORMATION_SCHEMA COLUMNS Table
- The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
- The INFORMATION_SCHEMA ENGINES Table
- The INFORMATION_SCHEMA EVENTS Table
- The INFORMATION_SCHEMA FILES Table
- The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables
- The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables
- The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
- The INFORMATION_SCHEMA ndb_transid_mysql_connection_map Table
- The INFORMATION_SCHEMA OPTIMIZER_TRACE Table
- The INFORMATION_SCHEMA PARAMETERS Table
- The INFORMATION_SCHEMA PARTITIONS Table
- The INFORMATION_SCHEMA PLUGINS Table
- The INFORMATION_SCHEMA PROCESSLIST Table
- The INFORMATION_SCHEMA PROFILING Table
- The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table
- The INFORMATION_SCHEMA ROUTINES Table
- The INFORMATION_SCHEMA SCHEMATA Table
- The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
- The INFORMATION_SCHEMA STATISTICS Table
- The INFORMATION_SCHEMA TABLES Table
- The INFORMATION_SCHEMA TABLESPACES Table
- The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
- The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
- The INFORMATION_SCHEMA TRIGGERS Table
- The INFORMATION_SCHEMA USER_PRIVILEGES Table
- The INFORMATION_SCHEMA VIEWS Table
- INFORMATION_SCHEMA InnoDB Tables
  - The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table
  - The INFORMATION_SCHEMA INNODB_BUFFER_PAGE_LRU Table
  - The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table
  - The INFORMATION_SCHEMA INNODB_CMP and INNODB_CMP_RESET Tables
  - The INFORMATION_SCHEMA INNODB_CMPMEM and INNODB_CMPMEM_RESET Tables
  - The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables
  - The INFORMATION_SCHEMA INNODB_FT_BEING_DELETED Table
  - The INFORMATION_SCHEMA INNODB_FT_CONFIG Table
  - The INFORMATION_SCHEMA INNODB_FT_DEFAULT_STOPWORD Table
  - The INFORMATION_SCHEMA INNODB_FT_DELETED Table
  - The INFORMATION_SCHEMA INNODB_FT_INDEX_CACHE Table
  - The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table
  - The INFORMATION_SCHEMA INNODB_LOCKS Table
  - The INFORMATION_SCHEMA INNODB_LOCK_WAITS Table
  - The INFORMATION_SCHEMA INNODB_METRICS Table
  - The INFORMATION_SCHEMA INNODB_SYS_COLUMNS Table
  - The INFORMATION_SCHEMA INNODB_SYS_DATAFILES Table
  - The INFORMATION_SCHEMA INNODB_SYS_FIELDS Table
  - The INFORMATION_SCHEMA INNODB_SYS_FOREIGN Table
  - The INFORMATION_SCHEMA INNODB_SYS_FOREIGN_COLS Table
  - The INFORMATION_SCHEMA INNODB_SYS_INDEXES Table
  - The INFORMATION_SCHEMA INNODB_SYS_TABLES Table
  - The INFORMATION_SCHEMA INNODB_SYS_TABLESPACES Table
  - The INFORMATION_SCHEMA INNODB_SYS_TABLESTATS View
  - The INFORMATION_SCHEMA INNODB_SYS_VIRTUAL Table
  - The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table
  - The INFORMATION_SCHEMA INNODB_TRX Table
- INFORMATION_SCHEMA Thread Pool Tables
  - The INFORMATION_SCHEMA TP_THREAD_GROUP_STATE Table
  - The INFORMATION_SCHEMA TP_THREAD_GROUP_STATS Table
  - The INFORMATION_SCHEMA TP_THREAD_STATE Table
- INFORMATION_SCHEMA Connection-Control Tables
  - The INFORMATION_SCHEMA CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS Table
- Extensions to SHOW Statements
MySQL Performance Schema
- Performance Schema Quick Start
- Performance Schema Build Configuration
- Performance Schema Startup Configuration
- Performance Schema Runtime Configuration
  - Performance Schema Event Timing
  - Performance Schema Event Filtering
  - Event Pre-Filtering
  - Pre-Filtering by Instrument
  - Pre-Filtering by Object
  - Pre-Filtering by Thread
  - Pre-Filtering by Consumer
  - Example Consumer Configurations
  - Naming Instruments or Consumers for Filtering Operations
  - Determining What Is Instrumented
- Performance Schema Queries
- Performance Schema Instrument Naming Conventions
- Performance Schema Status Monitoring
- Performance Schema Atom and Molecule Events
- Performance Schema Tables for Current and Historical Events
- Performance Schema Statement Digests
- Performance Schema General Table Characteristics
- Performance Schema Table Descriptions
  - Performance Schema Table Index
  - Performance Schema Setup Tables
  - Performance Schema Instance Tables
  - Performance Schema Wait Event Tables
  - Performance Schema Stage Event Tables
  - Performance Schema Statement Event Tables
  - Performance Schema Transaction Tables
  - Performance Schema Connection Tables
  - Performance Schema Connection Attribute Tables
    - The session_account_connect_attrs Table
    - The session_connect_attrs Table
  - Performance Schema User-Defined Variable Tables
  - Performance Schema Replication Tables
  - Performance Schema Lock Tables
    - The metadata_locks Table
    - The table_handles Table
  - Performance Schema System Variable Tables
  - Performance Schema Status Variable Tables
  - Performance Schema Summary Tables
  - Performance Schema Miscellaneous Tables
- Performance Schema Option and Variable Reference
- Performance Schema Command Options
- Performance Schema System Variables
- Performance Schema Status Variables
- The Performance Schema Memory-Allocation Model
- Performance Schema and Plugins
- Using the Performance Schema to Diagnose Problems
  - Query Profiling Using Performance Schema
- Migrating to Performance Schema System and Status Variable Tables
- Restrictions on Performance Schema
MySQL sys Schema
- Prerequisites for Using the sys Schema
- Using the sys Schema
- sys Schema Progress Reporting
- sys Schema Object Reference
Connectors and APIs
- MySQL Connector/C++
- MySQL Connector/J
- MySQL Connector/NET
- MySQL Connector/ODBC
- MySQL Connector/Python
- libmysqld, the Embedded MySQL Server Library
- MySQL C API
- MySQL PHP API
- MySQL Perl API
- MySQL Python API
- MySQL Ruby APIs
  - The MySQL/Ruby API
  - The Ruby/MySQL API
- MySQL Tcl API
- MySQL Eiffel Wrapper
Extending MySQL
- MySQL Internals
  - MySQL Threads
  - The MySQL Test Suite
- The MySQL Plugin API
- MySQL Services for Plugins
  - The Locking Service
    - The Locking Service C Interface
    - The Locking Service UDF Interface
  - The Keyring Service
- Adding Functions to MySQL
- Debugging and Porting MySQL
MySQL Enterprise Edition
- MySQL Enterprise Monitor Overview
- MySQL Enterprise Backup Overview
- MySQL Enterprise Security Overview
- MySQL Enterprise Encryption Overview
- MySQL Enterprise Audit Overview
- MySQL Enterprise Firewall Overview
- MySQL Enterprise Thread Pool Overview
- MySQL Enterprise Data Masking and De-Identification Overview
MySQL Workbench
MySQL 5.7 Frequently Asked Questions
- MySQL 5.7 FAQ: General
- MySQL 5.7 FAQ: Storage Engines
- MySQL 5.7 FAQ: Server SQL Mode
- MySQL 5.7 FAQ: Stored Procedures and Functions
- MySQL 5.7 FAQ: Triggers
- MySQL 5.7 FAQ: Views
- MySQL 5.7 FAQ: INFORMATION_SCHEMA
- MySQL 5.7 FAQ: Migration
- MySQL 5.7 FAQ: Security
- MySQL 5.7 FAQ: NDB Cluster
- MySQL 5.7 FAQ: MySQL Chinese, Japanese, and Korean Character Sets
- MySQL 5.7 FAQ: Connectors & APIs
- MySQL 5.7 FAQ: C API, libmysql
- MySQL 5.7 FAQ: Replication
- MySQL 5.7 FAQ: MySQL Enterprise Thread Pool
- MySQL 5.7 FAQ: InnoDB Change Buffer
- MySQL 5.7 FAQ: InnoDB Data-at-Rest Encryption
- MySQL 5.7 FAQ: Virtualization Support
Error Messages and Common Problems
- Error Message Sources and Components
- Error Information Interfaces
- Problems and Common Errors
Indexes
- General Index
- Function Index
- Command Index
- Function Index
- INFORMATION_SCHEMA Index
- Join Types Index
- Operator Index
- Option Index
- Privileges Index
- Modes Index
- Statement/Syntax Index
- Status Variable Index
- System Variable Index
- Transaction Isolation Level Index
MySQL Glossary

Chapter 9 Language Structure

Table of Contents

9.1 Literal Values

9.1.1 String Literals
9.1.2 Numeric Literals
9.1.3 Date and Time Literals
9.1.4 Hexadecimal Literals
9.1.5 Bit-Value Literals
9.1.6 Boolean Literals
9.1.7 NULL Values

9.2 Schema Object Names

9.2.1 Identifier Length Limits
9.2.2 Identifier Qualifiers
9.2.3 Identifier Case Sensitivity
9.2.4 Mapping of Identifiers to File Names
9.2.5 Function Name Parsing and Resolution

9.3 Keywords and Reserved Words

9.4 User-Defined Variables

9.5 Expressions

9.6 Comment Syntax

This chapter discusses the rules for writing the following elements of SQL statements when using MySQL:

Literal values such as strings and numbers
Identifiers such as database, table, and column names
Keywords and reserved words
User-defined and system variables
Comments

首页