Oracle 5.0, Reference Manual

Page: 555 / 3232

Server SQL Modes
535
variable requires the
SUPER
[578] privilege and affects the operation of all clients that connect from
that time on. Setting the
SESSION
variable affects only the current client. Any client can change its own
session
sql_mode
[495] value at any time.
You can retrieve the current global or session
sql_mode
[495] value with the following statements:
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
The most important
sql_mode
[495] values are probably these:
•
ANSI
[540]
This mode changes syntax and behavior to conform more closely to standard SQL.
•
STRICT_TRANS_TABLES
[539]
If a value could not be inserted as given into a transactional table, abort the statement. For a
nontransactional table, abort the statement if the value occurs in a single-row statement or the first
row of a multiple-row statement. More detail is given later in this section. (Implemented in MySQL
5.0.2)
•
TRADITIONAL
[540]
Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is
“give an error instead of a warning” when inserting an incorrect value into a column.
Note
The
INSERT
/
UPDATE
aborts as soon as the error is noticed. This may not be
what you want if you are using a nontransactional storage engine, because
data changes made prior to the error may not be rolled back, resulting in a
“partially done” update. (Added in MySQL 5.0.2)
When this manual refers to “strict mode,” it means a mode where at least one of
STRICT_TRANS_TABLES
[539] or
STRICT_ALL_TABLES
[539] is enabled.
The following list describes all supported modes:
•
ALLOW_INVALID_DATES
[535]
Do not perform full checking of dates. Check only that the month is in the range from 1 to 12 and the
day is in the range from 1 to 31. This is very convenient for Web applications where you obtain year,
month, and day in three different fields and you want to store exactly what the user inserted (without
date validation). This mode applies to
DATE
and
DATETIME
columns. It does not apply
TIMESTAMP
columns, which always require a valid date.
This mode is implemented in MySQL 5.0.2. Before 5.0.2, this was the default MySQL date-
handling mode. As of 5.0.2, the server requires that month and day values be legal, and
not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid
dates such as
'2004-04-31'
are converted to
'0000-00-00'
and a warning is generated.
With strict mode enabled, invalid dates generate an error. To permit such dates, enable
ALLOW_INVALID_DATES
[535] .
•
ANSI_QUOTES
[535]
Treat “
"
” as an identifier quote character (like the “
`
” quote character) and not as a string
quote character. You can still use “
`
” to quote identifiers with this mode enabled. With
ANSI_QUOTES
[535] enabled, you cannot use double quotation marks to quote literal strings,
because it is interpreted as an identifier.