Home arrow MySQL arrow Page 6 - MySQL Database Administration

4.2.2 The Server SQL Mode - MySQL

If you need to administer MySQL, this article gets you off to a good start. The first of a multi-part series, it is excerpted from chapter four of the book MySQL Administrator's Guide, written by Paul Dubois (Sams; ISBN: 0672326345).

TABLE OF CONTENTS:
  1. MySQL Database Administration
  2. 4.1.2 The mysqld-max Extended MySQL Server
  3. 4.1.3 The mysqld_safe Server Startup Script
  4. 4.1.4 The mysql.server Server Startup Script
  5. 4.2 Configuring the MySQL Server
  6. 4.2.2 The Server SQL Mode
  7. 4.2.3 Server System Variables
By: Sams Publishing
Rating: starstarstarstarstar / 28
May 25, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

The MySQL server can operate in different SQL modes, and (as of MySQL 4.1) can apply these modes differentially for different clients. This allows applications to tailor server operation to their own requirements.

Modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.

You can set the default SQL mode by starting mysqld with the --sql-mode="modes" option. Beginning with MySQL 4.1, you can also change the mode after startup time by setting the sql_mode variable with a SET [SESSION|GLOBAL] sql_mode='modes' statement. Setting the GLOBAL variable affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. modes is a list of different modes separated by comma (',') characters. You can retrieve the current mode by issuing a SELECT @@sql_mode statement. The default value is empty (no modes set).

The value also can be empty (--sql-mode="") if you want to reset it.

The following list describes the supported modes:

  • ANSI_QUOTES

    Treat '"' as an identifier quote character (like the '' quote character) and not as a string quote character. You can still use '' to quote identifers in ANSI mode. With ANSI_QUOTES enabled, you cannot use double quotes to quote a literal string, because it will be interpreted as an identifier. (New in MySQL 4.0.0.)

  • IGNORE_SPACE

    Allow spaces between a function name and the '(' character. This forces all function names to be treated as reserved words. As a result, if you want to access any database, table, or column name that is a reserved word, you must quote it. For example, because there is a USER() function, the name of the user table in the mysql database and the User column in that table become reserved, so you must quote them:

    SELECT "User" FROM mysql."user";

    (New in MySQL 4.0.0.)

  • NO_AUTO_VALUE_ON_ZERO

    NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number. This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (This is not a recommended practice, by the way.) For example, if you dump the table with mysqldump and then reload it, normally MySQL generates new sequence numbers when it encounters the 0 values, resulting in a table with different contents than the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. As of MySQL 4.1.1, mysqldump automatically includes statements in the dump output to enable NO_AUTO_VALUE_ON_ZERO. (New in MySQL 4.1.1.)

  • NO_DIR_IN_CREATE

    When creating a table, ignore all INDEX DIRECTORY and DATA DIRECTORY directives. This option is useful on slave replication servers. (New in MySQL 4.0.15.)

  • NO_FIELD_OPTIONS

    Don't print MySQL -specific column options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode. (New in MySQL 4.1.1.)

  • NO_KEY_OPTIONS

    Don't print MySQL -specific index options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode. (New in MySQL 4.1.1.)

  • NO_TABLE_OPTIONS

    Don't print MySQL -specific table options (such as ENGINE) in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode. (New in MySQL 4.1.1.)

  • NO_UNSIGNED_SUBTRACTION

    In subtraction operations, don't mark the result as UNSIGNED if one of the operands is unsigned. Note that this makes UNSIGNED BIGINT not 100% usable in all contexts. (New in MySQL 4.0.2.)

  • ONLY_FULL_GROUP_BY

    Don't allow queries that in the GROUP BY part refer to a not selected column. (New in MySQL 4.0.0.)

  • PIPES_AS_CONCAT

    Treat || as a string concatenation operator (same as CONCAT()) rather than as a synonym for OR. (New in MySQL 4.0.0.)

  • REAL_AS_FLOAT

    Treat REAL as a synonym for FLOAT rather than as a synonym for DOUBLE. (New in MySQL 4.0.0.)

The following special modes are provided as shorthand for combinations of mode values from the preceding list. They are available as of MySQL 4.1.1.

  • ANSI

    Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY. See Section 1.8.3, "Running MySQL in ANSI Mode."

  • DB2

    Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

  • MAXDB

    Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

  • MSSQL

    Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

  • MYSQL323

    Equivalent to NO_FIELD_OPTIONS.

  • MYSQL40

    Equivalent to NO_FIELD_OPTIONS.

  • ORACLE

    Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.

  • POSTGRESQL

    Equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS.



 
 
>>> More MySQL Articles          >>> More By Sams Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: