SQL 資料型態 – FLOAT , DOUBLE & DECIMAL

測試環境為 CentOS 8 x86_64 虛擬機.
浮點數有 FLOAT , DOUBLE 跟 DECIMAL ,資料大小範圍如下.

• FLOAT[(M,D)] [SIGNED | UNSIGNED | ZEROFILL]
  -3.402823466E+38 to -1.175494351E-38
  0
  1.175494351E-38 to 3.402823466E+38.
• DOUBLE[(M,D)] [SIGNED | UNSIGNED | ZEROFILL]
  -1.7976931348623157E+308 to -2.2250738585072014E-308
  0
  2.2250738585072014E-308 to 1.7976931348623157E+308
• DECIMAL[(M[,D])] [SIGNED | UNSIGNED | ZEROFILL]
  M 數字最大到 65 個.
  D 小數點在 MariadB 10.2.1 之前 最大到 30 個,之後的版本到 38 個.
  如 D 被省略,預設為 0.
  如 M被省略,預設為 10.

除了範圍不同外,其他皆類似,以下來看 M[,D] , SIGNED , UNSIGNED , ZEROFILL 的使用.

先建立測試用資料庫.

[root@localhost ~]# mysql -u root -p
Enter password:
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
  
MariaDB [(none)]> USE testdb;
Database changed

• M[,D]
  以下範例限制數字(最大到 5 個數字,包含小數點的 1 個數字)空間.

  MariaDB [testdb]> CREATE TABLE t1 (d DECIMAL (5,1));
  Query OK, 0 rows affected (0.003 sec)
  
  MariaDB [testdb]> INSERT INTO t1 VALUES (1.1);
  Query OK, 1 row affected (0.001 sec)
  
  MariaDB [testdb]> SELECT * FROM t1;
  +------+
  | d    |
  +------+
  |  1.1 |
  +------+
  1 row in set (0.000 sec)
  
  MariaDB [testdb]> INSERT INTO t1 VALUES (1.11);
  Query OK, 1 row affected, 1 warning (0.001 sec)
  
  MariaDB [testdb]> SHOW WARNINGS;
  +-------+------+----------------------------------------+
  | Level | Code | Message                                |
  +-------+------+----------------------------------------+
  | Note  | 1265 | Data truncated for column 'd' at row 1 |
  +-------+------+----------------------------------------+
  1 row in set (0.000 sec)
  
  MariaDB [testdb]> SELECT * FROM t1;
  +------+
  | d    |
  +------+
  |  1.1 |
  |  1.1 |
  +------+
  2 rows in set (0.000 sec)
  

  上面範例可以看到雖然輸入 1.11 但因為 M[,D] 為 (5,1) 所以被裁成 1.1 了.

  MariaDB [testdb]> INSERT INTO t1 VALUES (11111.1);
  ERROR 1264 (22003): Out of range value for column 'd' at row 1
  
  MariaDB [testdb]> INSERT INTO t1 VALUES (11111);
  ERROR 1264 (22003): Out of range value for column 'd' at row 1
  

  上面範例可以看到輸入的值皆超過最大 5 個數字(4個整數與1個浮點數).

• SIGNED , UNSIGNED
  預設都是 SIGNED (正負數). 下面使用 UNSIGNED (只能正數).

  MariaDB [testdb]> CREATE TABLE t2 (d DECIMAL (5,1) UNSIGNED);
  Query OK, 0 rows affected (0.003 sec)
  
  MariaDB [testdb]> INSERT INTO t2 VALUES (-11.1);
  ERROR 1264 (22003): Out of range value for column 'd' at row 1
  

  上面範例可以看到輸入含有 -(負值) 的數字是不行的.

• ZEROFILL
  ZEROFILL 會在沒有數字的地方填上 0 .

  MariaDB [testdb]> CREATE TABLE t3 (d DECIMAL (5,1) ZEROFILL);
  t3 VALUES (11.1);
  SELECT * FROM t3;Query OK, 0 rows affected (0.017 sec)
  
  MariaDB [testdb]> INSERT INTO t3 VALUES (11.1);
  Query OK, 1 row affected (0.003 sec)
  
  MariaDB [testdb]> SELECT * FROM t3;
  +--------+
  | d      |
  +--------+
  | 0011.1 |
  +--------+
  1 row in set (0.000 sec)
  

遇過的問題:

透過 CAST 或是 CONVERT 做型別轉換時(將字串轉換成數字),出現以下的錯誤.

MariaDB [(none)]> SELECT CAST('3.1415' AS FLOAT);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FLOAT)' at line 1

官方說明透過 CAST 或是 CONVERT 將型別轉換成 FLOAT 需使用 MariaDB 10.4.5 以上的版本才支援.

MariaDB [(none)]> SELECT CAST('3.1415' AS DOUBLE);
+--------------------------+
| CAST('3.1415' AS DOUBLE) |
+--------------------------+
|                   3.1415 |
+--------------------------+
1 row in set (0.000 sec)