Using SQL to Manage Data
- 2.1. The Server SQL Mode
- 2.2. MySQL Identifier Syntax and Naming Rules
- 2.3. Case Sensitivity in SQL Statements
- 2.4. Character Set Support
- 2.5. Selecting, Creating, Dropping, and Altering Databases
- 2.6. Creating, Dropping, Indexing, and Altering Tables
- 2.7. Obtaining Database Metadata
- 2.8. Performing Multiple-Table Retrievals with Joins
- 2.9. Performing Multiple-Table Retrievals with Subqueries
- 2.10. Performing Multiple-Table Retrievals with UNION
- 2.11. Multiple-Table Deletes and Updates
- 2.12. Performing Transactions
- 2.13. Foreign Keys and Referential Integrity
- 2.14. Using FULLTEXT Searches
This chapter is from the book
This chapter is from the book
This chapter is from the book
The MySQL server understands Structured Query Language (SQL). Therefore, SQL is the means by which you tell the server how to perform data management operations, and fluency with it is necessary for effective communication. When you use a program such as the mysql client, it functions primarily as a way for you to send SQL statements to the server to be executed. If you write programs in a language that has a MySQL interface, such as the Perl DBI module or PHP PDO extension, these interfaces enable you to communicate with the server by issuing SQL statements.
Chapter 1, “Getting Started with MySQL,” presented a tutorial introducing many of MySQL’s capabilities, including some basic use of SQL. We’ll build on that material here to go into more detail on several topics:
- Changing the SQL mode to affect server behavior
- Referring to elements of databases
- Using multiple character sets
- Creating and destroying databases, tables, and indexes
- Obtaining information about databases and their contents
- Retrieving data using joins, subqueries, and unions
- Using multiple-table deletes and updates
- Performing transactions that enable statements to be grouped or canceled
- Setting up foreign key relationships
- Using the FULLTEXT search engine
The items just listed cover a broad range of topics of what you can do with SQL. Other chapters provide additional SQL-related information:
- Chapter 4, “Views and Stored Programs,” discusses how to create and use views (virtual tables that provide alternative ways of looking at data) and stored programs (functions and procedures, triggers, and events).
- Chapter 12, “General MySQL Administration,” describes how to use administrative statements such as GRANT and REVOKE to manage user accounts. It also discusses the privilege system that controls what operations accounts are permitted to perform.
- Appendix E, “SQL Syntax Reference,” shows the syntax for SQL statements implemented by MySQL and the privileges required to use them. It also covers the syntax for using comments in your SQL statements.
See also the MySQL Reference Manual, especially for changes made in recent versions of MySQL.
2.1. The Server SQL Mode
The MySQL SQL mode affects several aspects of SQL statement execution, and the server has a system variable named sql_mode that enables you to configure this mode. The variable can be set globally to affect all clients, and each individual client can change the mode to affect its own session with (connection to) the server. This means that any client can change how the server treats it without impact on other clients.
The SQL mode affects behaviors such as handling of invalid values during data entry and identifier quoting. The following list describes a few of the possible mode values:
- STRICT_ALL_TABLES and STRICT_TRANS_TABLES enable “strict” mode. In strict mode, the server is more restrictive about accepting bad data values. (Specifically, it rejects bad values rather than changing them to the closest legal value.)
- TRADITIONAL is a composite mode. It is like strict mode, but enables other modes that impose additional constraints for even stricter data checking. Traditional mode causes the server to behave like more traditional SQL servers with regard to how it handles bad data values.
- ANSI_QUOTES tells the server to recognize double quote as an identifier quoting character.
- PIPES_AS_CONCAT causes || to be treated as the standard SQL string concatenation operator rather than as a synonym for the OR operator.
- ANSI is another composite mode. It turns on ANSI_QUOTES, PIPES_AS_CONCAT, and several other mode values that cause the server to conform more closely to standard SQL.
When you set the SQL mode, specify a value consisting of one or more mode values separated by commas, or an empty string to clear the value. Mode values are not case sensitive.
To set the SQL mode when you start the server, set the sql_mode system variable on the mysqld command line or in an option file. On the command line, you might use a setting like one of these:
--sql_mode="TRADITIONAL" --sql_mode="ANSI_QUOTES,PIPES_AS_CONCAT"
To change the SQL mode at runtime, set the sql_mode system variable with a SET statement. Any client can set its own session-specific SQL mode:
SET sql_mode = 'TRADITIONAL';
To set the SQL mode globally, add the GLOBAL keyword:
SET GLOBAL sql_mode = 'TRADITIONAL';
Setting the global variable requires the SUPER administrative privilege. The global value becomes the default SQL mode for clients that connect afterward.
To determine the current value of the session or global SQL mode, use these statements:
SELECT @@SESSION.sql_mode; SELECT @@GLOBAL.sql_mode;
The value returned consists of a comma-separated list of enabled modes, or an empty value if no modes are enabled.
Section 3.3, “How MySQL Handles Invalid Data Values,” discusses the SQL mode values that affect handling of erroneous or missing values during data entry. Appendix D, “System, Status, and User Variable Reference,” describes the full set of permitted mode values for the sql_mode variable. For additional information about using system variables, see Section 12.3.1, “Checking and Setting System Variable Values.”