萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> mysql中int和varchar的長度詳解

mysql中int和varchar的長度詳解

在mysql中一般人可能不會去問int與varchar區別了,這個估計一個數字類型,一個字符類型沒有可比性了,同時他們長度也不一樣,下面我來介紹一下mysql 中int和varchar的長度一些細節吧。

int

從 -2^31 (-2,147,483,648) 到 2^31 – 1 (2,147,483,647) 的整型數據(所有數字)。存儲大小為 4 個字節。int 的 SQL-92 同義字為 integer

varchar

長度是0-255個字符哦

mysql 字段中int後面所跟數字有何意義? varchar後的數字又有何意義?

 代碼如下 復制代碼

mysql> create table t(a int(1));
Query OK, 0 rows affected (0.10 sec)
mysql> insert into t values(123);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t values(12345678);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t;
+----------+
| a        |
+----------+
|      123 |
| 12345678 |
+----------+

可見,int(1)並不表示一個字節。
如果更長的數字會不會報錯?

 代碼如下 復制代碼 mysql> insert into t values(1234567812345678);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
mysql> insert into t values(2147483648);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
mysql> insert into t values(2147483647);
Query OK, 1 row affected (0.03 sec)

int 型長度最大值是2^31 -1 ,加上有符號數,應該是四個字節的長度。

 代碼如下 復制代碼

mysql> alter table t add column b int;
Query OK, 5 rows affected (0.25 sec)
mysql> insert into t values(2147483647,2147483648);
ERROR 1264 (22003): Out of range value for column 'b' at row 1
mysql> insert into t values(2147483647,2147483647);
Query OK, 1 row affected (0.03 sec)

mysql> select * from t;
+------------+------------+
| a          | b          |
+------------+------------+
|        123 |       NULL |
|   12345678 |       NULL |
|      65536 |       NULL |
| 1073741824 |       NULL |
| 2147483647 |       NULL |
| 2147483647 | 2147483647 |
+------------+------------+
6 rows in set (0.00 sec)

可見,int後是否跟數字與最大值沒有關系。

再看char 和varchar後的數字

 代碼如下 復制代碼

mysql> alter table t add column c char(2);
Query OK, 6 rows affected (0.17 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> alter table t add column d varchar(2);
Query OK, 6 rows affected (0.17 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> desc t;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a     | int(1)     | YES  |     | NULL    |       |
| b     | int(11)    | YES  |     | NULL    |       |
| c     | char(2)    | YES  |     | NULL    |       |
| d     | varchar(2) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> insert into t values(2147483647,2147483647,'abc','abc');
ERROR 1406 (22001): Data too long for column 'c' at row 1
mysql> insert into t values(2147483647,2147483647,'ab','abc');
ERROR 1406 (22001): Data too long for column 'd' at row 1
mysql> insert into t values(2147483647,2147483647,'ab','ab');
Query OK, 1 row affected (0.02 sec)

可見char和varchar後的數字是存儲長度。
那麼對於中文字,會不會有字節數大於字數而溢出的問題呢?

 代碼如下 復制代碼

mysql> insert into t values(2147483647,2147483647,'ab','中化');
Query OK, 1 row affected (0.02 sec)

mysql> select * from t;
+------------+------------+------+------+
| a          | b          | c    | d    |
+------------+------------+------+------+
|        123 |       NULL | NULL | NULL |
|   12345678 |       NULL | NULL | NULL |
|      65536 |       NULL | NULL | NULL |
| 1073741824 |       NULL | NULL | NULL |
| 2147483647 |       NULL | NULL | NULL |
| 2147483647 | 2147483647 | NULL | NULL |
| 2147483647 | 2147483647 | ab   | ab   |
| 2147483647 | 2147483647 | ab   | 中化 |
+------------+------------+------+------+
8 rows in set (0.00 sec)

中文字也是包含在2個字內的。

 代碼如下 復制代碼

mysql> insert into t values(2147483647,2147483647,'ab','中化a');
ERROR 1406 (22001): Data too long for column 'd' at row 1
mysql> insert into t values(2147483647,2147483647,'ab','?糸F');
Query OK, 1 row affected (0.05 sec)

mysql> select * from t;
+------------+------------+------+------+
| a          | b          | c    | d    |
+------------+------------+------+------+
|        123 |       NULL | NULL | NULL |
|   12345678 |       NULL | NULL | NULL |
|      65536 |       NULL | NULL | NULL |
| 1073741824 |       NULL | NULL | NULL |
| 2147483647 |       NULL | NULL | NULL |
| 2147483647 | 2147483647 | NULL | NULL |
| 2147483647 | 2147483647 | ab   | ab   |
| 2147483647 | 2147483647 | ab   | 中化 |
| 2147483647 | 2147483647 | ab   | ?糸F |
+------------+------------+------+------+
9 rows in set (0.01 sec)

多一個字節都不行。
看一下字符編碼。mysql字符編碼由服務器,數據庫,表,字段四級組成。
在windows下的mysql設置:

 代碼如下 復制代碼 mysql> show variables like "character%";
+--------------------------+--------------------------+
| Variable_name            | Value                    |
+--------------------------+--------------------------+
| character_set_client     | gbk                      |
| character_set_connection | gbk                      |
| character_set_database   | utf8                     |
| character_set_filesystem | binary                   |
| character_set_results    | gbk                      |
| character_set_server     | utf8                     |
| character_set_system     | utf8                     |
| character_sets_dir       | D:mysqlsharecharsets |
+--------------------------+--------------------------+
8 rows in set (0.00 sec)
mysql> show create table t;
 t     | CREATE TABLE `t` (
 `a` int(1) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 `c` char(2) DEFAULT NULL,
 `d` varchar(2) DEFAULT NULL
 ENGINE=InnoDB DEFAULT CHARSET=utf8

linux下centos設置:

 代碼如下 復制代碼 mysql> show variables like "character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /opt/mysql/share/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> show full fields from t;
mysql> show create database test;
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */

字符編碼是utf8,
因此,對於utf8中文字符,varchar後面的數字,無論是中文還是英文,都表示相應的字數,不用擔心截斷。

copyright © 萬盛學電腦網 all rights reserved