`
iihero
  • 浏览: 258188 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

Comparison of different SQL implementations(整理)

SQLSQL ServerOracleMySQLDB2 
阅读更多

Comparison of different SQL implementations

The goal of this page — which is a work in progress — is to gather information relevant for people who are porting SQL from one product to another and/or are interested in possibilities and limits of 'cross-product' SQL.

The following tables compare how different DBMS products handle various SQL (and related) features. If possible, the tables also state how the implementations should do things, according to the SQL standard.

I will only write about subjects that I've worked with personally, or subjects which I anticipate to find use for in the near future. Subjects on which there are no significant implementation variances are not covered. Beta-versions of software are not examined.

I'm sorry about the colors. They are a result of wanting to mark each DBMS differently and at the same time wanting to be relatively nice to printers.

If you have corrections or suggestions, please contact me; even notifications about spelling errors are welcome.

Contents:

Legend, definitions, and notes

The following SQL standard and implementations have been examined, if not otherwise stated:

Standard The latest official version of SQL is SQL:2008.

I don't have access to the official ISO standard text, but Whitemarsh Information Systems Corporation provides a rather final draft as a zip-archive, containing several files. Most important to this page is the file 5CD2-02-Foundation-2006-01.pdf .

No books cover SQL:2008 yet. Regarding the previous standard, SQL:2003, the only book covering the subject is in German which I was never any good at. Therefore, I also use the following book as reference:
Jim Melton and Alan Simon: SQL:1999—Understanding Relational Language Components (ISBN 1-55860-456-1).
PostgreSQL PostgreSQL 8.4.1 on CentOS Linux.
Documentation
DB2 DB2 Express-C v. 9.1 on Fedora Linux. Note that there are differences between various DB2 flavors ; this page is about DB2 for "LUW" (Linux/Unix/Windows).
Documentation
MS SQL Server MS SQL Server 2005 on Windows XP. Microsoft's SQL implementation is sometimes named Transact-SQL , or TSQL . In this document, I'll generally write MSSQL as a short-hand for Microsoft's SQL Server product.
Documentation
MySQL MySQL Database Server 5.0.18 on Fedora Linux (i.e. MySQL AB's "classic" DBMS product—not MaxDB).
Documentation
Oracle Oracle Database 11g Release 2 on Red Hat Enterprise Linux.
Documentation
Informix Informix Dynamic Server Workgroup Edition v. 11.50 on Red Hat Enterprise Linux.
Documentation

The products are running with their default settings. This is important for MySQL and MSSQL: Their interpretation of SQL may be changed rather drastically by adjusting certain configuration options, potentially increasing the level of standard compliance (for MySQL, there is a dedicated documentation page about this). However, such non-default configuration options are not of great value for people writing SQL applications because the developer often cannot rely on non-default configuration settings.

Features

Views

Standard Views are part of the standard, and they may be updated, as long as it 'makes sense'.

SQL:2008 has a rather complicated set of rules governing when a view is updatable, basically saying that a view is updatable, as long as the update-operation translates into an unambiguous change.

SQL-92 was more restrictive, specifying that updatable views cannot be derived from more than one base table.
PostgreSQL Has views. Breaks that standard by not allowing updates to views; offers the non-standard 'rules'-system as a work-around.
DB2 Conforms to at least SQL-92.
MSSQL Conforms to at least SQL-92.
MySQL Conforms to at least SQL-92.
Oracle Conforms to at least SQL-92.
Informix Conforms to at least SQL-92.

Join types and features

All the DBMSes support basic INNER JOINs, but vary in their support for other join types.

In the following feature chart, a yes means yes ; an empty table cell means no .

Join type/feature PostgreSQL DB2 MSSQL MySQL Oracle Informix <!-- {12738888657262}--> <!-- {12738888657263}--> <!-- {12738888657264}--> <!-- {12738888657265}--> <!-- {12738888657266}--> <!-- {12738888657267}--> <!-- {12738888657268}--> <!-- {12738888657269}--> <!-- {127388886572610}--> <!-- {127388886572611}--> <!-- {127388886572612}--> <!-- {127388886572613}--> <!-- {127388886572614}--> <!-- {127388886572615}--> <!-- {127388886572616}--> <!-- {127388886572617}--> <!-- {127388886572618}--> <!-- {127388886572619}--> <!-- {127388886572620}--> <!-- {127388886572621}--> <!-- {127388886572622}--> <!-- {127388886572623}--> <!-- {127388886572624}--> <!-- {127388886572625}--> <!-- {127388886572626}-->
Natural joins (only tested: NATURAL LEFT JOIN )yesyesyes
USING -clauseyesyesyes
FULL joins1 (tested: SELECT...FULL JOIN...ON...=... )yesyesyesyesyes
Explicit CROSS JOIN (cartesian product)yesyesyesyesyesyes

Remarks:

  1. Note that FULL joins may be emulated with a union of a left and a right join .

Data definition language (DDL)

Copying structure

Objective: An existing table, t1 needs to be copied to a new table, t2 , without copying data. I.e., only the structure/definition of the table is copied.

Standard Optional feature T171 defines LIKE clause in table definition :
CREATE TABLE t2 ( LIKE t1 )

The DBMS may support an extension of this (feature T173) which allows for more table properties to be copied:
CREATE TABLE t2 ( LIKE t1 INCLUDING IDENTITY INCLUDING DEFAULTS INCLUDING GENERATED )

If INCLUDING DEFAULTS is not specified, column defaults will not be part of t2 ; likewise with IDENTITY and GENERATED properties.

Triggers, CHECK constraints, and other 'non-trivial' table features are not copied to the new table.
PostgreSQL Complies with the core of the feature (T171). The extended T173 feature is only partially supported, and extended with a few non-standard options:
  • The INCLUDING IDENTITY and INCLUDING GENERATED options are not supported
  • INCLUDING CONSTRAINTS and INCLUDING INDEXES options are added

PostgreSQL does not allow you to copy the structure of a view, using CREATE TABLE ... (LIKE ...) . For that, you may use another construct:
CREATE TABLE copytable AS SELECT * FROM viewname WHERE false

Documentation

DB2 Behaves as if inspired by the standard. I.e., DB2 conforms to the standard, except:
  • the LIKE ... clause is stated outside any parenthesis
  • the extended INCLUDING GENERATED option is not supported
  • DB2 defaults to copy IDENTITY, DEFAULTS, and GENERATED properties, unless EXCLUDING IDENTITY and/or EXCLUDING DEFAULTS is specified.

Example:
CREATE TABLE t2 LIKE t1 INCLUDING DEFAULTS

DB2 allows you to copy the structure of a view into a table.

Documentation

MSSQL Does not support the standard. Instead, MSSQL has a special SELECT ... INTO ... FROM ... construct which can be combined with an impossible WHERE-clause to copy structure only:
SELECT * INTO t2 FROM t1 WHERE 1<>1

The source (t1 ) may be a view, as well as a table.

SELECT ... INTO copies NOT NULL column attributes, but nothing else.

Documentation

MySQL Complies with the core of the feature (T171), but not with the extended features (T173).

MySQL does not allow you to copy the structure of a view into a table.

Documentation

Oracle Does not support the standard. Oracle lets you copy a table structure using a special CREATE TABLE ... AS construct, combined with an impossible WHERE -clause: CREATE TABLE t2 AS SELECT * FROM t1 WHERE 1<>1

Documentation

Informix On my TODO.

The SELECT statement

Ordering result sets

Standard The SQL-standard states that relations are unordered, but result sets may be ordered when returned to the user through a cursor:

DECLARE cursorname CURSOR FOR
SELECT ... FROM ... WHERE ...
ORDER BY column_name1 ,column_name2 ,...

The DBMS may additionally allow ORDER BY outside cursor definitions (optional feature IDs F850, F851, F852, F855).(Since SQL:2008)

The standard doesn't specify how NULLs should be ordered in comparison with non-NULL values, except that any two NULLs are to be considered equally ordered, and that NULLs should sort either above or below all non-NULL values. However, the DBMS may optionally (as part of feature ID T611, "Elementary OLAP operations") allow the user to specify whether NULLs should sort first or last:
... ORDER BY ... NULLS FIRST
or
... ORDER BY ... NULLS LAST

PostgreSQL As well as in cursor definitions, it allows ORDER BY in other contexts.

By default, NULLs are considered higher than any non-NULL value; however,(since version 8.3) this sorting behaviour may be changed by adding NULLS FIRST or NULLS LAST to the ORDER BY expression.

DOCUMENTATION

DB2 As well as in cursor definitions, it allows ORDER BY in other contexts. NULLs are considered higher than any non-NULL value.

DOCUMENTATION

MSSQL As well as in cursor definitions, it allows ORDER BY in other contexts. NULLs are considered lower than any non-NULL value.

DOCUMENTATION

MySQL As well as in cursor definitions, it allows ORDER BY in other contexts.

NULLs are considered lower than any non-NULL value, except if a - (minus) character is added before the column name and ASC is changed to DESC, or DESC to ASC; this minus-before-column-name feature seems undocumented.

Documentation

Oracle As well as in cursor definitions, it allows ORDER BY in other contexts.

By default, NULLs are considered higher than any non-NULL value; however, this sorting behaviour may be changed by adding NULLS FIRST or NULLS LAST to the ORDER BY expression.

Beware of Oracle's strange treatment of empty strings and NULLs as the same 'value'.

DOCUMENTATION

Informix As well as in cursor definitions, it allows ORDER BY in other contexts. NULLs are considered lower than any non-NULL value.

DOCUMENTATION

Limiting result sets

Simple limit

Objective: Want to only get n rows in the result set. Usually only makes sense in connection with an ORDER BY expression.

Note: This is not the same as a top-n query — see next section .

Note also: Some of the queries below may not be legal in all situations, such as in views or sub-queries.

Standard The SQL standard provides three ways of performing a 'simple limit':
  • Using FETCH FIRST :(since SQL:2008)

    Non-core feature IDs F856, F857, F858, and F859 describe using
    SELECT ... FROM ... WHERE ... ORDER BY ... FETCH FIRST n ROWS ONLY

    You may write ROW instead of ROWS .

  • Using a Window function :(since SQL:2003)

    Non-core Feature ID T611 specifies window functions , of which one is ROW_NUMBER()OVER :

    SELECT*FROM(
    SELECT
    ROW_NUMBER()OVER(ORDERBYkey ASC)ASrownumber ,
    columns
    FROMtablename
    )ASfoo
    WHERErownumber <=n

  • Using a cursor :

    If your application is stateful (in contrast to web applications which normally have to be seen as stateless), then you might look at cursors (core feature ID E121) instead. This involves:

    • DECLARE cursor-name CURSOR FOR ...
    • OPEN cursor-name
    • FETCH ...
    • CLOSE cursor-name
PostgreSQL Supports all standards-based approaches.

In old PostgreSQL versions (versions 8.3 and older), a special PostgreSQL (and MySQL) specific method was used:

SELECT columns
FROM tablename
ORDER BY key ASC
LIMIT n

Note that LIMIT changes the semantics of SELECT...FORUPDATE .

Documentation:
DB2 Supports all standards-based approaches.

Documentation:

  • OLAP functions
  • FETCH FIRST (general page about the SELECT statement; use your browser's search function to locate FETCH FIRST )
MSSQL Supports the ROW_NUMBER() (since MSSQL 2005) and cursor standards-based approaches; doesn't support FETCH FIRST .

MSSQL 2000 didn't support ROW_NUMBER() . Instead, a MSSQL 2000-specific syntax was needed:
SELECT TOP n columns
FROM tablename
ORDER BY key ASC
The TOP construct is still available in MSSQL 2008, and it's handy for casual SQL work.

Documentation

MySQL Doesn't support the standard. Alternative solution:

SELECT columns
FROM tablename
ORDER BY key ASC
LIMIT n

Documentation

Oracle Supports ROW_NUMBER ; doesn't support FETCH FIRST .

As Oracle doesn't allow AS for subquery naming (and doesn't need a subquery-name at all in this case), the standard SQL code above needs to be rewritten slightly:

SELECT*FROM(
SELECT
ROW_NUMBER()OVER(ORDERBYkey ASC)ASrownumber ,
columns
FROMtablename
)
WHERErownumber <=n

Documentation

A reader of this page told me that using the Oracle-specific ROWNUM 'magic' column yields better performance than using the ROW_NUMBER function. You may want to experiment with this. Ask Tom has an article on ROWNUM.
Informix Doesn't support ROW_NUMBER(), nor FETCH FIRST.

Alternative solution (which is illegal in plain sub-queries):
SELECT FIRST n columns
FROM tablename
ORDER BY key ASC

Documentation

Top-n query

Objective: Like the simple limit-query above, but include rows with tie conditions. Thus, the query may return more than n rows.

Some call this a quota -query.

The following examples are based on this table:

SELECT * FROM person ORDER BY age ASC;
+----------+-------------+-----+
|PERSON_ID | PERSON_NAME | AGE |
+----------+-------------+-----+
|        7 | Hilda       |  12 |
|        8 | Bill        |  12 |
|        4 | Joe         |  23 |
|        2 | Veronica    |  23 |
|        3 | Michael     |  27 |
|        9 | Marianne    |  27 |
|        1 | Ben         |  50 |
|       10 | Michelle    |  50 |
|        5 | Irene       |  77 |
|        6 | Vivian      |  77 |
+----------+-------------+-----+

Now, we only want the three (n =3) youngest persons displayed, i.e. a result set like this:

+----------+-------------+-----+
|PERSON_ID | PERSON_NAME | AGE |
+----------+-------------+-----+
|        7 | Hilda       |  12 |
|        8 | Bill        |  12 |
|        4 | Joe         |  23 |
|        2 | Veronica    |  23 |
+----------+-------------+-----+
Standard With standard SQL, there are two principal ways to obtain the wanted data:
  • The fast variant :

    One of the major additions in SQL:2003 was the addition of non-core (i.e. optional) OLAP (online analytic processing) features. If the DBMS supports elementary OLAP (feature ID F611), then the top-n query may be formulated using a window function , such as RANK()OVER :

    SELECT * FROM (
    SELECT
    RANK()OVER(ORDERBYageASC )ASranking,
    person_id,
    person_name,
    age
    FROM person
    ) AS foo
    WHERE ranking <= 3

    (Change ASC to DESC in the position marked like this in order to get a top-3 oldest query instead.)

  • The slow variant :

    If the DBMS doesn't support the elementary OLAP features, then the top-n solution may be obtained in an alternative way which is so slow that it's not a real option in most situations:

    Correlated subquery method , mentioned in the book Practical Issues in Database Management (chapter 9: Quota Queries ) by Fabian Pascal (who, again, quotes Date for the solution):

    SELECT * FROM person AS px
    WHERE (
    SELECT COUNT(*)
    FROM person AS py
    WHERE py.age < px.age
    ) < 3

    The query may make more sense if the objective is re-phrased as "Find all persons (px) such that the number of younger, other persons (py) is less than 3".

    (Change < to > in the position marked like this in order to get a top-3 oldest query instead.)

In the article Going To Extremes by Joe Celko , there is a description of yet another principle for performing quota queries, using scalar subqueries . Scalar subqueries are more tedious to write but might yield better performance on your system.
PostgreSQL Supports the fast standard SQL variant.

In version 8.3 and older, PostgreSQL only supported the slow standard SQL query variant. In practice, a PostgreSQL-only method was used instead, in order to obtain acceptable query performance:
SELECT *
FROM person
WHERE (
age <= (
SELECT age FROM person
ORDER BY age ASC
LIMIT 1 OFFSET 2-- 2=n-1
)
) IS NOT FALSE

(Change <= to >= and ASC to DESC in the positions marked like this in order to get a top-3 oldest query instead.)

Documentation

DB2 Supports the fast standard SQL variant.

Documentation

MSSQL Supports the fast standard SQL variant.

MSSQL 2000 supported the slow standard SQL variant. In practice, a MSSQL-only expression had to be used instead, in order to obtain acceptable query performance:
SELECT TOP 3 WITH TIES *
FROM person
ORDER BY age ASC
(Change ASC to DESC in the position marked like this in order to get a top-3 oldest query instead.)

Documentation

MySQL Supports the slow standard SQL solution. In practice, this MySQL-specific solution should be used instead, in order to obtain acceptable query performance:

SELECT*
FROMperson
WHEREage<= COALESCE(--note:nospacebetween "COALESCE"andopeningparenthesis
(
SELECTage
FROMperson
ORDERBYageASC
LIMIT1OFFSET2--2=n-1
),
(
SELECTMAX (age)
FROMperson
)
)

(Change <= to >= and ASC to DESC and MAX to MIN in the positions marked like this in order to get a top-3 oldest query instead.)

The offset-value 2 is the result of n-1 (remember: n is 3 in these examples).

The second argument to the COALESCE call makes the query work in cases where the cardinality of the table is lower than n .
Oracle Supports the fast standard SQL variant. However, as Oracle doesn't like "AS... " after subqueries (and doesn't require naming of subqueries), the query has to be paraphrased slightly:

SELECT * FROM (
SELECT
RANK()OVER(ORDERBYageASC )ASranking,
person_id,
person_name,
age
FROM person
)
WHERE ranking <= 3

(Change ASC to DESC in the position marked like this in order to get a top-3 oldest query instead.)

Documentation

Informix On my TODO.

Limit—with offset

Objective: Want to only get n rows in the result set, and we want the first skip rows in the result set discarded. Usually only makes sense in connection with an ORDER BY expression.

In the recipes below, basic ordering is ASCending, i.e. lowest-first queries. If you want the opposite, then change ASC->DESC and DESC->ASC at the places emphasized like this .

Standard The SQL standard provides three ways of performing 'limit with offset':
  • Using OFFSET and FETCH FIRST :(since SQL:2008)

    SELECT...
    FROM ...
    WHERE ...
    ORDER BY ...
    OFFSET skip ROWS
    FETCH FIRST n ROWS ONLY

    You may write ROW instead of ROWS .

  • Using a window function :(since SQL:2003)

    Non-core Feature ID T611 specifies window functions , one of which is ROW_NUMBER() OVER :

    SELECT*FROM(
    SELECT
    ROW_NUMBER()OVER(ORDERBYkeyASC ) AS rownum,
    columns
    FROMtablename
    )ASfoo
    WHERErownum>skip ANDrownum<=(n+skip)

  • Using a cursor :

    You may use a cursor (core feature ID E121), if the programming environment permits it. This involves:

    • DECLARE cursor-name CURSOR FOR ...
    • OPEN cursor-name
    • FETCH RELATIVE number-of-rows-to-skip ...
    • CLOSE cursor-name
PostgreSQL Supports all the standards-based approaches.

In version 8.3 and older, cursors should be used, or a special construct:
SELECT columns
FROM tablename
ORDER BY key ASC
LIMIT n OFFSET skip

Documentation:
DB2 Supports the window function based approach.

Regarding cursors: DB2 for Linux/Unix/Windows doesn't support FETCHRELATIVE (which is strange, because DB2 for the mainframe seems to support it). Instead, see if the DB2 driver for your programming environment supports SQLFetchScroll() .

Documentation : OLAP functions , the FETCH statement .
MSSQL Supports the window function and cursor based approaches.

MSSQL 2000 didn't support ROW_NUMBER() ; instead, a MSSQL-specific syntax had to be used:
SELECT*FROM(
SELECTTOPn *FROM(
SELECTTOPz columns --(z=n+skip)
FROMtablename
ORDERBYkey ASC
)ASFOOORDERBYkey DESC --('FOO'maybeanything)
)ASBARORDERBYkey ASC --('BAR'maybeanything)

Documentation

MySQL Doesn't support the standard approaches. Alternative solution:

SELECT columns
FROM tablename
ORDER BY key ASC
LIMIT n OFFSET skip

In older versions of MySQL, the LIMIT-syntax is less clear:
... LIMIT [skip ,] n
(i.e. the skip argument is optional).
The old syntax is still supported by later MySQL versions (the old syntax is widely used).

Documentation

Oracle Supports ROW_NUMBER() . I'm unsure if Oracle's cursor support is standards-compliant.

As Oracle doesn't accept AS for subquery naming (and doesn't require naming of subqueries in this case), the standard SQL solution has to be re-written slightly. An other reason for the re-write is that ROWNUM is a reserved word in Oracle, with special meaning. The Oracle code becomes:

SELECT*FROM(
SELECT
ROW_NUMBER()OVER(ORDERBYkeyASC ) AS rn,
columns
FROMtablename
)
WHERErn>skip ANDrn<=(n+skip)

Documentation

A reader of this page told me that using the Oracle-specific ROWNUM 'magic' column yields better performance than using the ROW_NUMBER function. You may want to experiment with this. Ask Tom has an article on ROWNUM.
Informix Supports neither OFFSET ...FETCH FIRST nor ROW_NUMBER . Supports cursors.

An alternative to using cursors is to us an Informix-specific construct:
SELECT SKIP skip FIRST n *
FROM tablename

Documentation : SKIP and FIRST

Note:

FETCH FIRST/LIMIT/TOP queries with offset are often used in a result presentation context: To retrieve only—say—30 rows at a time so that the end-user isn't overwhelmed by the complete result set, but instead is offered a paginated result presentation. In this case, be careful not to (only) sort on a non-unique column.

Consider the following example (where PostgreSQL is used):

SELECT * FROM person ORDER BY age ASC;
 person_id | person_name | age
-----------+-------------+-----
         7 | Hilda       |  12
         8 | Bill        |  12
         4 | Joe         |  23
         2 | Veronica    |  23
         3 | Michael     |  27
         9 | Marianne    |  27
         1 | Ben         |  50
        10 | Michelle    |  50
         5 | Irene       |  77
         6 | Vivian      |  77

When ordering is performed on the non-unique age-value, ties may occur and it's not guaranteed that the DBMS will fetch the rows in the same order every time.

Instead of the above listing, the DBMS is allowed to return the following display order where Michael and Marianne are displayed in the opposite order compared to above:

SELECT * FROM person ORDER BY age ASC;
 person_id | person_name | age
-----------+-------------+-----
         7 | Hilda       |  12
         8 | Bill        |  12
         4 | Joe         |  23
         2 | Veronica    |  23
         9 | Marianne    |  27
         3 | Michael     |  27
         1 | Ben         |  50
        10 | Michelle    |  50
         5 | Irene       |  77
         6 | Vivian      |  77

Now, suppose the end-user wants the results displayed five rows at a time. The result set is fetched in two queries where the DBMS happens to sort differently, as above. We will use PostgreSQL's legacy syntax in the example:

SELECT * FROM person ORDER BY age ASC LIMIT 5;
 person_id | person_name | age
-----------+-------------+-----
         7 | Hilda       |  12
         8 | Bill        |  12
         4 | Joe         |  23
         2 | Veronica    |  23
         3 | Michael     |  27

SELECT * FROM person ORDER BY age ASC LIMIT 5 OFFSET 5;
 person_id | person_name | age
-----------+-------------+-----
         3 | Michael     |  27
         1 | Ben         |  50
        10 | Michelle    |  50
         5 | Irene       |  77
         6 | Vivian      |  77

Notice that Marianne was not displayed in any of the two split result set presentations.

The problem could be avoided if the result set ordering had been done in a deterministic way, i.e. where the unique person_id value was considered in case of a tie:
SELECT * FROM person ORDER BY age ASC, person_id ASC ...
This is safer than to pray for the DBMS to behave in a predictable way when handling non-unique values.

Note : If the table is updated between parts of the result set pagination, then the user might still get an inconsistent presentation. If you want to guard against this, too, then you should see if use of an insensitive cursor is an option in your application. Use of cursors to paginate result sets usually require that your application is stateful , which is not the case in many web-application settings. Alternatively, you could let the application cache the complete result set (e.g. in a session if your web application environment provides for sessions).

The INSERT statement

Inserting several rows at a time

Standard An optional SQL feature is row value constructors (feature ID F641). One handy use of row value constructors is when inserting several rows at a time, such as:

INSERT INTO tablename
VALUES (0,'foo') , (1,'bar') , (2,'baz');

— which may be read as a shorthand for

INSERT INTO tablename VALUES (0,'foo');
INSERT INTO tablename VALUES (1,'bar');
INSERT INTO tablename VALUES (2,'baz');

PostgreSQL Supported .(since version 8.2)
DB2 Supported .
MSSQL Supported .(since version 2008)
MySQL Supported .
Oracle An Oracle-specific kludge:
INSERT INTO tablename
SELECT 0,'foo' FROM DUAL
UNION ALL
SELECT 1,'bar' FROM DUAL
UNION ALL
SELECT 2,'baz' FROM DUAL
Informix On my TODO.

Data types

The BOOLEAN type

Standard The BOOLEAN type is optional (has feature ID T031), which is a bit surprising for such a basic type. However, it seems that endless discussions of how NULL is to be interpreted for a boolean value is holding BOOLEAN from becoming a core type.

The standard says that a BOOLEAN may be one of the following literals:

  • TRUE
  • FALSE
  • UNKNOWN or NULL (unless prohibited by a NOTNULL constraint)

The DBMS may interpret NULL as equivalent to UNKNOWN. It is unclear from the specification if the DBMS must support UNKNOWN, NULL or both as boolean literals. In this author's opinion, you should forget about the UNKNOWN literal in order to simplify the situation and let the normal SQL three-way logic apply.

It's defined that TRUE>FALSE (true larger than false).
PostgreSQL Follows the standard.

Accepts NULL as a boolean literal; doesn't accept UNKNOWN as a boolean literal.

Documentation

DB2 Doesn't support the BOOLEAN type.

Judging from various JDBC-documentation, it seems that IBM recommends a CHAR(1) field constrained to values '0' and '1' (and perhaps NULL) as the way to store boolean values.
MSSQL Doesn't support the BOOLEAN type.

Possible alternative type: the BIT type which may have 0 or 1 (or NULL) as value. If you insert an integer value other than these into a field of type BIT, then the inserted value will silently be converted to 1.

Rudy Limeback has some notes about oddities with the MSSQL BIT type.

Documentation

MySQL Offers a non-conforming BOOLEAN type. MySQL's BOOLEAN is one of many aliases to its TINYINT(1) type.

(Never use TINYINT(1) as the column type if you use JDBC with MySQL and expect to get non-boolean values from it .)

MySQL accepts the literals TRUE and FALSE as aliases to 1 and 0, respectively. However, you may also assign a value of — e.g. — 9 to a column of type BOOLEAN (which is non-conforming).

If you use JDBC with MySQL, then BOOLEAN is the preferred type for booleans: MySQL's JDBC-driver implicitly converts between Java's boolean and MySQL's pseudo-BOOLEAN type.

Side note: MySQL has a BIT type which may be interesting for people with enormous amounts of boolean-type data.

Documentation

Oracle Doesn't support the BOOLEAN type.

Judging from various JDBC documentation and a discussion at Ask Tom , it seems that Oracle recommends NUMBER(1) as the way to store boolean values; it's probably wise to constrain such columns to values 0 and 1 (and perhaps NULL).
Informix On my TODO.

Warning to JDBC users:
According to the JDBC standard, getBoolean() must convert a SQL-'value' of NULL to the false Java value. To check if the database-value was really NULL, use wasNull() .

The CHAR type

For the following section, I have used this test-SQL to try to illuminate differences (unfortunately, even standard SQL as simple as this has to be adjusted for some products):

Test steps:
CREATE TABLE chartest (
charval1 CHAR(10) NOT NULL,
charval2 CHAR(10) NOT NULL,
varcharval VARCHAR(30) NOT NULL
);
INSERT INTO chartest VALUES ('aaa','aaa','aaa');
INSERT INTO chartest
VALUES ('aaaaaa','aaa','aaa'); -- should truncate to 'aaaaaa'
INSERT INTO chartest
VALUES ('aaaaaaaaaaaa','aaa','aaa'); -- should raise error
SELECT * FROM chartest; -- should show two rows
DELETE FROM chartest WHERE charval1='aaaaaa';
SELECT * FROM chartest; -- should show one row
SELECT * FROM chartest WHERE charval1=varcharval;
SELECT charval1 || 'X' AS res FROM chartest;
SELECT CHAR_LENGTH(charval1 || charval2) AS res FROM chartest;
SELECT CHAR_LENGTH(charval1) + CHAR_LENGTH(charval2)
AS res
FROM chartest;

Expected results, after CREATE and INSERTs:

SELECT * FROM chartest; -- should show two rows
CHARVAL1   CHARVAL2   VARCHARVAL
========== ========== ==============================
aaa        aaa        aaa
aaaaaa     aaa        aaa


DELETE FROM chartest WHERE charval1='aaaaaa';


SELECT * FROM chartest; -- should show one row
CHARVAL1   CHARVAL2   VARCHARVAL
========== ========== ==============================
aaa        aaa        aaa


SELECT * FROM chartest WHERE charval1=varcharval;
CHARVAL1   CHARVAL2   VARCHARVAL
========== ========== ==============================
aaa        aaa        aaa


SELECT charval1 || 'X' FROM chartest AS res;
    res
===========
aaa       X


SELECT CHAR_LENGTH(charval1 || charval2) AS res FROM chartest;
    res
===========
         20


SELECT character_length(charval1) + character_length(charval2)
AS res
FROM chartest;
    res
============
          20

Actual results .

Standard
  • Return with an exception state if the inserted string is too long, unless the characters exceeding the limit are all spaces.
  • Pad CHAR columns with spaces if the inserted string is shorter than the specified CHAR-length.
  • Pad with trailing spaces as needed when casting or comparing to other string-like values (e.g. VARCHARs).
PostgreSQL Stores CHARs in space padded form, but violates the standard by (conceptually) truncating trailing white-space before performing most functions, operators, and comparisons (like the CHARACTER_LENGTH -function and the concatenation(|| ) operator).

Documentation

DB2 Follows the standard.

Documentation

MSSQL Generally follows standard, but (conceptually) truncates trailing white-space before performing some functions (at least before LEN() ).

Documentation

MySQL Breaks the standard by silently inserting the string, truncated to specified column CHAR-length.
(It's actually not completely silent, as it issues warnings if values were truncated: If you manually check for warnings, you will know that something bad happened, but not which of the rows are now invalid.)

Violates the standard by effectively truncating all trailing spaces.
The documentation states that MySQL truncates trailing spaces when CHAR values are retrieved . That may be true, but
分享到：
| oracle 10g 手动创建scott(tiger) schema
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics