MySQL數(shù)據(jù)庫數(shù)據(jù)類型的注意點(diǎn)和應(yīng)用實(shí)例

更新時(shí)間：2024年12月11日 08:36:10 作者：JhonKI

這篇文章主要介紹了MySQL數(shù)據(jù)庫數(shù)據(jù)類型的注意點(diǎn)和應(yīng)用的相關(guān)資料,MySQL數(shù)據(jù)類型主要包括tinyint、bit、float、decimal、char和varchar等,每種類型都有其特定的存儲(chǔ)范圍和適用場(chǎng)景,需要的朋友可以參考下

一、數(shù)據(jù)類型分類

MySQL 數(shù)據(jù)類型在數(shù)據(jù)庫中起著至關(guān)重要的作用。它決定了數(shù)據(jù)在數(shù)據(jù)庫中的存儲(chǔ)方式和可進(jìn)行的操作。合理選擇數(shù)據(jù)類型能夠帶來多方面的好處。

1.1 tinyint 類型

我們先按照下面步驟創(chuàng)建一個(gè)數(shù)據(jù)表，只存儲(chǔ) tinyint 類型的數(shù)據(jù)

// 創(chuàng)建數(shù)據(jù)庫 test_db
mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)

// 切換當(dāng)前使用的數(shù)據(jù)庫為 test_db
mysql> use test_db;
Database changed

// 在括號(hào)內(nèi)定義表 t1 的列結(jié)構(gòu)，這里只定義了一列，列名為num，其數(shù)據(jù)類型被指定為tinyint
mysql> create table if not exists t1(
    -> num tinyint
    -> );
Query OK, 0 rows affected (0.02 sec)

// 查看指定數(shù)據(jù)表（這里是t1表）的結(jié)構(gòu)信息
mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| num   | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

// 查看當(dāng)前所在數(shù)據(jù)庫（之前通過use test_db切換到了test_db數(shù)據(jù)庫）中包含哪些數(shù)據(jù)表
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t1                |
+-------------------+
1 row in set (0.00 sec)

// 查看創(chuàng)建指定數(shù)據(jù)表（這里是t1表）時(shí)使用的完整CREATE TABLE語句
mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table                                                                             |
+-------+------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `num` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

我們已知 tinyint'類型的數(shù)據(jù)存儲(chǔ)范圍為[-128, 127]往這個(gè)數(shù)據(jù)表中存儲(chǔ)數(shù)據(jù)，看看反應(yīng)

mysql> insert into t1 value (-128);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 value (127);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 value (129);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t1 value (0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 value (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 value (-1);
Query OK, 1 row affected (0.00 sec)

mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| num   | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+
| num  |
+------+
| -128 |
|  127 |
|    0 |
|    1 |
|   -1 |
+------+
5 rows in set (0.01 sec)

在插入 -128、127、0、1、-1 時(shí)都是正確的，而且最后也成功打印出來，證明存進(jìn)去了

但是插入 129 時(shí)顯示錯(cuò)誤，超出數(shù)據(jù)范圍，并且也沒有打印出來

所以我們可以得出結(jié)論

如果我們向mysq!特定的類型中插入不合法的數(shù)據(jù)，MySQL一般都是直接攔截我們，不讓我們做對(duì)應(yīng)的操作!
反過來，如果我們已經(jīng)有數(shù)據(jù)被成功插入到mysql中了，一定插入的時(shí)候是合法的!
所以，mysql中，一般而言，數(shù)據(jù)類型本身也是一種: 約束

1.2 bit 類型

我們先創(chuàng)建數(shù)據(jù)表 t2 用來表示一個(gè)用戶是否在線

mysql> create table if not exists t2(
    -> id int,
    -> online bit(8)
    -> )^C
mysql> create table if not exists t2(
    -> id int,
    -> online bit(1)
    -> ）；
    -> ^C
mysql> create table if not exists t2(
    -> id int,
    -> online bit(1)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc t2;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(11) | YES  |     | NULL    |       |
| online | bit(1)  | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

bit[(M)] : 位字段類型。M表示每個(gè)值的位數(shù)，范圍從1到64。如果M被忽略，默認(rèn)為1。

插入數(shù)據(jù)

mysql> insert into t2 (id, online) values (123, 0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 (id, online) values (123, 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 (id, online) values (123, 3);
ERROR 1406 (22001): Data too long for column 'online' at row 1
mysql> insert into t2 (id, online) values (123, 5);
ERROR 1406 (22001): Data too long for column 'online' at row 1
mysql> insert into t2 (id, online) values (123, 2);
ERROR 1406 (22001): Data too long for column 'online' at row 1

我們發(fā)現(xiàn)插入 0 或者 1 的 bit 值的時(shí)候都成功了，但是插入非01值時(shí)都失敗了

mysql> select * from t2;
+------+--------+
| id   | online |
+------+--------+
|  123 |        |
|  123 |       |
+------+--------+
2 rows in set (0.00 sec)

mysql> select id, hex(online) from t2;
+------+-------------+
| id   | hex(online) |
+------+-------------+
|  123 | 0           |
|  123 | 1           |
+------+-------------+
2 rows in set (0.00 sec)

我們打印表的內(nèi)容直接打印是打印不出來的，轉(zhuǎn)換為十六進(jìn)制表示形式后才能輸出

這是因?yàn)閎it類型在存儲(chǔ)時(shí)是以ASCLL碼的形式存儲(chǔ)的

我們將bit的值改成10看看，能否再插入3，5，2等數(shù)字

mysql> alter table t2 modify online bit(10);
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc t2;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(11) | YES  |     | NULL    |       |
| online | bit(10) | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into t2 (id, online) values (123, 3);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 (id, online) values (123, 5);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 (id, online) values (123, 2);
Query OK, 1 row affected (0.00 sec)

mysql> select id, hex(online) from t2;
+------+-------------+
| id   | hex(online) |
+------+-------------+
|  123 | 0           |
|  123 | 1           |
|  123 | 3           |
|  123 | 5           |
|  123 | 2           |
+------+-------------+
5 rows in set (0.00 sec)

就能插進(jìn)來了

1.3 float 類型

先創(chuàng)建一個(gè)數(shù)據(jù)表 t3

// float(4,2)表示的范圍是-99.99 ~ 99.99，MySQL在保存值時(shí)會(huì)進(jìn)行四舍五入
mysql> create table if not exists t3( 
    -> id int,
    -> salary float(4, 2)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t1                |
| t2                |
| t3                |
+-------------------+
3 rows in set (0.00 sec)

mysql> desc t3
    -> ;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id     | int(11)    | YES  |     | NULL    |       |
| salary | float(4,2) | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

float(4,2)表示的范圍是-99.99 ~ 99.99，MySQL在保存值時(shí)會(huì)進(jìn)行四舍五入

mysql> insert into t3 (id, salary) values (1, 99.99);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 (id, salary) values (2, -99.99);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 (id, salary) values (3, 100.00);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> select * from t3
    -> ;
+------+--------+
| id   | salary |
+------+--------+
|    1 |  99.99 |
|    2 | -99.99 |
+------+--------+
2 rows in set (0.00 sec)

1.4 decimal 類型

decimal(m, d) [unsigned] : 定點(diǎn)數(shù)m指定長度，d表示小數(shù)點(diǎn)的位數(shù)

mysql> create table if not exists t4(
    -> f1 float(10, 8),
    -> f2 decimal(4, 2)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc t4;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| f1    | float(10,8)  | YES  |     | NULL    |       |
| f2    | decimal(4,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into t4 (f1, f2) values (10.0, 99.99);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t4 (f1, f2) values (10.0, -99.99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 (f1, f2) values (10.0, -999.99);
ERROR 1264 (22003): Out of range value for column 'f2' at row 1
mysql> insert into t4 (f1, f2) values (10.0, 999.99);
ERROR 1264 (22003): Out of range value for column 'f2' at row 1
mysql> insert into t4 (f1, f2) values (10.0, 99.999);
ERROR 1264 (22003): Out of range value for column 'f2' at row 1
mysql> insert into t4 (f1, f2) values (10.0, 99.994);
Query OK, 1 row affected, 1 warning (0.01 sec)

decimal(4,2) 表示的范圍是 -99.99 ~ 99.99

decimal(4,2) unsigned 表示的范圍 0 ~ 99.99

decimal和float很像，但是有區(qū)別:

float和decimal表示的精度不一樣

mysql> select * from t4;
+-------------+--------+
| f1          | f2     |
+-------------+--------+
| 10.00000000 |  99.99 |
| 10.00000000 | -99.99 |
| 10.00000000 |  99.99 |
+-------------+--------+
3 rows in set (0.00 sec)

那既然這兩個(gè)類型很像，decimal 類型的作用是什么

我們將兩個(gè)類型的范圍改成一樣

mysql> alter table t4 modify f2 decimal(10, 8);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> insert into t4 (f1, f2) values (23.12345612, 23.12345612);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t4;
+-------------+--------------+
| f1          | f2           |
+-------------+--------------+
| 10.00000000 |  99.99000000 |
| 10.00000000 | -99.99000000 |
| 10.00000000 |  99.99000000 |
| 23.12345695 |  23.12345612 |
+-------------+--------------+
4 rows in set (0.00 sec)

我們發(fā)現(xiàn) float 類型存的和原始的數(shù)據(jù)有一點(diǎn)的精度差距，但 decimal 類型保證了精度

float表示的精度大約是7位

decimal整數(shù)最大位數(shù)m為65。支持小數(shù)最大位數(shù)d是30。如果d被省略，默認(rèn)為0.如果m被省略，默認(rèn)是10。

建議：如果希望小數(shù)的精度高，推薦使用decimal

1.5 char 類型

char(L): 固定長度字符串，L是可以存儲(chǔ)的長度，單位為字符，最大長度值可以為255

mysql>  create table if not exists t5(
    -> id int,
    -> name char(2)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc t5
    -> ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| name  | char(2) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into t5 (id, name) values (1, 'a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t5 (id, name) values (1, 'b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 (id, name) values (1, 'ab');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t5 (id, name) values (1, 'abc');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> select * from t5;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    1 | b    |
|    1 | ab   |
+------+------+
3 rows in set (0.00 sec)

char(2) 表示可以存放兩個(gè)字符，可以是字母或漢字，但是不能超過2個(gè)，最多只能是255

1.6 varchar 類型

varchar(L): 可變長度字符串，L表示字符長度，最大長度65535個(gè)字節(jié)

使用方法和char一模一樣

關(guān)于varchar(len),len到底是多大，這個(gè)len值，和表的編碼密切相關(guān)：

varchar長度可以指定為0到65535之間的值，但是有1 - 3 個(gè)字節(jié)用于記錄數(shù)據(jù)大小，所以說有效字
節(jié)數(shù)是65532。
當(dāng)我們的表的編碼是utf8時(shí)，varchar(n)的參數(shù)n最大值是65532/3=21844[因?yàn)閡tf中，一個(gè)字符占
用3個(gè)字節(jié)]，如果編碼是gbk，varchar(n)的參數(shù)n最大是65532/2=32766（因?yàn)間bk中，一個(gè)字符
占用2字節(jié)）

如何選擇定長或變長字符串？

如果數(shù)據(jù)確定長度都一樣，就使用定長（char），比如：身份證，手機(jī)號(hào)，md5
如果數(shù)據(jù)長度有變化,就使用變長(varchar), 比如：名字，地址，但是你要保證最長的能存的進(jìn)去。
定長的磁盤空間比較浪費(fèi)，但是效率高。
變長的磁盤空間比較節(jié)省，但是效率低。
定長的意義是，直接開辟好對(duì)應(yīng)的空間
變長的意義是，在不超過自定義范圍的情況下，用多少，開辟多少

總結(jié)

到此這篇關(guān)于MySQL數(shù)據(jù)庫數(shù)據(jù)類型的注意點(diǎn)和應(yīng)用的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)類型注意點(diǎn)和應(yīng)用內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: