mysql> select cast(1 as date);
Type: DATECollation: 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