|
This script creates columns of int(1) and int(2) data types:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 AS SELECT
|
1,
|
-1,
|
COALESCE(1,1),
|
COALESCE(-1,-1),
|
COALESCE(1,-1),
|
COALESCE(-1,1);
|
SHOW CREATE TABLE t1;
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`1` int(1) NOT NULL,
|
`-1` int(2) NOT NULL,
|
`COALESCE(1,1)` int(1) NOT NULL,
|
`COALESCE(-1,-1)` int(2) NOT NULL,
|
`COALESCE(1,-1)` int(2) NOT NULL,
|
`COALESCE(-1,1)` int(2) NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
int is preserved for literals.
int is also preserved when data type aggregation takes place in hybrid function, such as COALESCE.
However, int is not preserved when aggregation for UNION happens:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 AS SELECT 1 AS c1,1 AS c2,-1 AS c3,-1 AS c4 UNION SELECT 1,-1,1,-1;
|
SHOW CREATE TABLE t1;
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`c1` bigint(20) NOT NULL DEFAULT 0,
|
`c2` bigint(20) NOT NULL DEFAULT 0,
|
`c3` bigint(20) NOT NULL DEFAULT 0,
|
`c4` bigint(20) NOT NULL DEFAULT 0
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
int was not preserved. It created bigint columns instead, which looks too excessive.
|