关于coalesce的一些经验记录【陆续增补】

mysql> select cast(1 as date);

Type:       DATE
Collation:  binary (63)
Length:     10
Max_length: 0
Decimals:   31
Flags:      BINARY

mysql> select coalesce(NULL);
Type:       NULL
Collation:  binary (63)
Length:     0
Max_length: 0
Decimals:   31
Flags:      BINARY NUM

mysql> select coalesce(cast(1 as date));
Type:       DATE
Collation:  latin1_swedish_ci (8)
Length:     10
Max_length: 0
Decimals:   31
Flags:

DATE + LONGLONG => VAR_STRING

mysql> select  coalesce(cast(1 as date), 3);
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     10
Max_length: 1
Decimals:   31
Flags:

DATE + DATE => NEWDATE

mysql> select coalesce(cast(1 as date), cast(3 as date));

Type:       NEWDATE
Collation:  latin1_swedish_ci (8)
Length:     10
Max_length: 0
Decimals:   31

DATE + DATETIME => DATETIME

mysql> select  coalesce(cast(1 as date), cast(3 as datetime));

Type:       DATETIME
Collation:  latin1_swedish_ci (8)
Length:     29
Max_length: 0
Decimals:   31
Flags:

DATE + DATETIME + LONGLONG => VAR_STRING

mysql> select coalesce(cast(1 as date), cast(3 as datetime), 3);

Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     29
Max_length: 1
Decimals:   31
Flags:

mysql> select coalesce(cast(1 as BINARY));
Type:       VAR_STRING
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   31
Flags:      NOT_NULL BINARY

mysql> select coalesce(cast(1 as CHAR));
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     1
Max_length: 1
Decimals:   31
Flags:      NOT_NULL

BINARY + CHAR => BINARY (or BINARY_VARCHAR?)

mysql> select coalesce(cast(1 as BINARY), cast(2 as CHAR));
Type:       VAR_STRING
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   31
Flags:      NOT_NULL BINARY

+-------------+----------------+------+-----+-------------------+-----------------------------+
| Field       | Type           | Null | Key | Default           | Extra                       |
+-------------+----------------+------+-----+-------------------+-----------------------------+
| id          | int(11)        | NO   | PRI | NULL              |                             |
| binaryvalue | varbinary(255) | YES  |     | NULL              |                             |
| charvalue   | char(2)        | YES  |     | NULL              |                             |
| bin2        | binary(3)      | YES  |     | NULL              |                             |
| varch       | varchar(1024)  | YES  |     | NULL              |                             |
| ts          | timestamp      | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| ty          | year(4)        | YES  |     | NULL              |                             |
+-------------+----------------+------+-----+-------------------+-----------------------------+

VARCHAR + BINARY => BINARY_VARCHAR

mysql> select coalesce(varch, bin2) from t1;
Type:       VAR_STRING
Collation:  binary (63)
Length:     1024
Max_length: 1
Decimals:   31
Flags:      BINARY

mysql> select coalesce(utinyint) from t1;
Type:       TINY
Collation:  binary (63)
Length:     3
Max_length: 1
Decimals:   0
Flags:      NOT_NULL UNSIGNED BINARY NUM

mysql> select coalesce(utinyint, usmallint);
Type:       SHORT
Collation:  binary (63)
Length:     5
Max_length: 1
Decimals:   0
Flags:      NOT_NULL UNSIGNED BINARY NUM

最后更新：2015-8-7