服务器SQL模式

Submitted by yejr on 周四, 2006/11/16 - 17:00

A.3. MySQL 5.0 FAQ - 服务器SQL模式

Questions

  • 26.3.1:
    什么是服务器的SQL模式?

    What are server SQL modes?

  • 26.3.2:
    有多少种服务器SQL模式?

    How many server SQL modes are there?

  • 26.3.3:
    怎么判断服务器的SQL模式是什么?

    How do you determine the server SQL mode?

  • 26.3.4:
    SQL模式是否依赖于数据库或者不同的连接?

    Is the mode dependent on the database or connection?

  • 26.3.5:
    严格SQL模式的规则能否被扩展?

    Can the rules for strict mode be extended?

  • 26.3.6:
    严格SQL模式是否能提升性能?

    Does strict mode impact performance?

  • 26.3.7:
    MySQL 5.0安装完之后默认的SQL模式是什么?

    What is the default server SQL mode when My SQL
    5.0 is installed?

Questions and Answers

26.3.1:
What are server SQL modes?

服务器SQL模式定义了MySQL支持什么样的SQL语法格式,并且执行什么样的数据有效性检查.它让MySQL在各种不同环境下使用起来更简便,并且也能让MySQL和其他不同的数据库服务器一起工作.MySQL对不同的客户端运用不同的SQL模式.详情请看 Section 5.2.6, “SQL Modes”.

Server SQL 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. The
MySQL Server apply these modes individually to different
clients. For more information, see
Section 5.2.6, “SQL Modes”.

26.3.2:
How many server SQL modes are there?

每种SQL模式否可以被立刻开启或者停止.想要查看全部的SQL模式列表请看 Section 5.2.6, “SQL Modes” .

Each mode can be independently switched on and off. See
Section 5.2.6, “SQL Modes”, for a complete list of
available modes.

26.3.3:
How do you determine the server SQL mode?

可以(在启动 mysqld 的时候)用参数 --sql_mode 来设定SQL模式.在每次连接的时候,可以使用语句 SET [SESSION|GLOBAL] sql_mode='modes' 来设定不同的SQL模式,可以设置为本次连接还是全局的.执行 SELECT @@sql_mode 语句可以取得当前的模式.

You can set the default SQL mode (for
mysqld startup) with the
--sql-mode option. Using the statement
SET [SESSION|GLOBAL]
sql_mode='modes', you
can change the settings from within a connection, either
locally to the connection, or to take effect globally. You
can retrieve the current mode by issuing a SELECT
@@sql_mode statement.

26.3.4:
Is the mode dependent on the database or connection?

模式跟某个特定的数据库没有关联.模式可以设置为本地的(会话级变量)或者是全局的变量.使用 SET [SESSION|GLOBAL] sql_mode='modes' 语法来设置.

A mode is not linked to a particular database. Modes can be
set locally to the session (connection), or globally for the
server. you can change these settings using SET
[SESSION|GLOBAL]
sql_mode='modes'.

26.3.5:
Can the rules for strict mode be extended?

所谓的 strict mode,意思是指至少启用 TRADITIONAL, STRICT_TRANS_TABLES, STRICT_ALL_TABLES 等几种模式的模式.这些选项可以互相结合使用,因此可以添加额外的严格模式.详情请看 Modes">Section 5.2.6, “SQL Modes”.

When we refer to strict mode, we mean a
mode where at least one of the modes
TRADITIONAL,
STRICT_TRANS_TABLES, or
STRICT_ALL_TABLES is enabled. Options can
be combined, so you can add additional restrictions to a
mode. See Section 5.2.6, “SQL Modes”, for more
information.

26.3.6:
Does strict mode impact performance?

如果设置了类似大小写有效性检查等,那么为此带来的开销肯定比没有设置来得多.不过系统性能提升的并不高,如果你无需这些有效性检查(可能你的引用程序已经检查过了)的话,MySQL允许你禁用严格SQL模式.当然了,如果你需要的话,那么严格模式正合你意.

The intensive validation of input data that some settings
requires more time than if the validation is not done. While
the performance impact is not that great, if you do not
require such validation (perhaps your application already
handles all of this), then MySQL gives you the option of
leaving strict mode disabled. However — if you do
require it — strict mode can provide such validation.

26.3.7:
What is the default server SQL mode when My SQL
5.0 is installed?

默认地,不会启用任何模式.想要了解所有的可用模式以及MySQL默认的动作,请看 Section 5.2.6, “SQL Modes”.

By default, no special modes are enabled. See
Section 5.2.6, “SQL Modes”, for information about all
available modes and MySQL's default behavior.

技术相关: