![background image](http://html1.mh-extra.com/html/oracle/5-0/5-0_reference-manual_1646196559.webp)
Server SQL Modes
539
•
PIPES_AS_CONCAT
[539]
Treat
||
[881]
as a string concatenation operator (same as
CONCAT()
[887]
) rather than as a
synonym for
OR
[881]
.
•
REAL_AS_FLOAT
[539]
Treat
REAL
as a synonym for
FLOAT
. By default, MySQL treats
REAL
as a synonym for
DOUBLE
.
•
STRICT_ALL_TABLES
[539]
Enable strict mode for all storage engines. Invalid data values are rejected. Additional detail follows.
(Added in MySQL 5.0.2)
•
STRICT_TRANS_TABLES
[539]
Enable strict mode for transactional storage engines, and when possible for nontransactional storage
engines. Additional details follow. (Implemented in MySQL 5.0.2)
Strict mode controls how MySQL handles input values that are invalid or missing. A value can be
invalid for several reasons. For example, it might have the wrong data type for the column, or it might
be out of range. A value is missing when a new row to be inserted does not contain a value for a
non-
NULL
column that has no explicit
DEFAULT
clause in its definition. (For a
NULL
column,
NULL
is
inserted if the value is missing.)
For transactional tables, an error occurs for invalid or missing values in a statement when either of the
STRICT_ALL_TABLES
[539]
or
STRICT_TRANS_TABLES
[539]
modes are enabled. The statement
is aborted and rolled back.
For nontransactional tables, the behavior is the same for either mode, if the bad value occurs in the
first row to be inserted or updated. The statement is aborted and the table remains unchanged. If the
statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the
result depends on which strict option is enabled:
• For
STRICT_ALL_TABLES
[539]
, MySQL returns an error and ignores the rest of the rows.
However, in this case, the earlier rows still have been inserted or updated. This means that you
might get a partial update, which might not be what you want. To avoid this, it is best to use single-
row statements because these can be aborted without changing the table.
• For
STRICT_TRANS_TABLES
[539]
, MySQL converts an invalid value to the closest valid value
for the column and insert the adjusted value. If a value is missing, MySQL inserts the implicit default
value for the column data type. In either case, MySQL generates a warning rather than an error and
continues processing the statement. Implicit defaults are described in
Section 11.1.7, “Data Type
Default Values”
.
Strict mode disallows invalid date values such as
'2004-04-31'
. It does not disallow dates with zero
month or day parts such as
'2004-04-00'
or “zero” dates. To disallow these as well, enable the
NO_ZERO_IN_DATE
[538]
and
NO_ZERO_DATE
[538]
SQL modes in addition to strict mode.
If you are not using strict mode (that is, neither
STRICT_TRANS_TABLES
[539]
nor
STRICT_ALL_TABLES
[539]
is enabled), MySQL inserts adjusted values for invalid or missing values
and produces warnings. In strict mode, you can produce this behavior by using
INSERT IGNORE
or
UPDATE IGNORE
. See
Section 13.7.5.37, “
SHOW WARNINGS
Syntax”
.
Strict mode does not affect whether foreign key constraints are checked.
foreign_key_checks
[451]
can be used for that. (See
Section 5.1.4, “Server System Variables”
.)
The following special modes are provided as shorthand for combinations of mode values from
the preceding list. All are available in MySQL 5.0 beginning with version 5.0.0, except for
TRADITIONAL
[540]
, which was implemented in MySQL 5.0.2.
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...