MySQL常用的数据类型

蔚落 2023-07-03 02:52 92阅读 0赞

>  *   Keywords and Reserved Words : [https://dev.mysql.com/doc/refman/8.0/en/keywords.html][https_dev.mysql.com_doc_refman_8.0_en_keywords.html] 
>  *  MySQL 数据类型 : [https://www.runoob.com/mysql/mysql-data-types.html][https_www.runoob.com_mysql_mysql-data-types.html]

>  *  [http://cmsblogs.com/?p=5501&vip=1][http_cmsblogs.com_p_5501_vip_1]

--------------------

# 1 常用的整数类型 #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly95dWFueXUuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70][]

--------------------

# 2 常用的浮点类型 #

![20200131214802179.png][]

![20200607210313150.png][]

-- 创建数据库
    CREATE DATABASE test ;
    USE test ;
    -- 创建表
    CREATE TABLE t (d1 DOUBLE, d2 DECIMAL (10, 3)) ;
    -- 插入数据
    INSERT INTO t 
    VALUES
      (11.2, 11.2),
      (2.56, 2.56),
      (9.01, 9.01),
      (132.33, 132.33) ;
    -- 查询数据
    SELECT * FROM t ;

SELECT 
      SUM(d1),
      SUM(d2) 
    FROM
      t ;

--------------------

# 3 常用的时间类型 #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly95dWFueXUuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 1][]

>  TIMESTAMP在不同时区显示的时间不一样

-- 验证时区信息
    CREATE TABLE t_timestamp (dt TIMESTAMP) ;
    INSERT INTO t_timestamp 
    SELECT 
      NOW() ;

-- 2020-01-31 22:14:41
    SELECT 
      * 
    FROM
      t_timestamp ;
    --
    SET time_zone = '+10:00' ;
    -- 2020-02-01 00:14:41
    SELECT 
      * 
    FROM
      t_timestamp ;

--------------------

# 3 常用的字符串类型 #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly95dWFueXUuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 2][]

--------------------

# 4 如何为数据选择合适的数据类型 #

> **优先选择符合存储数据需求的最小数据类型**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly95dWFueXUuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 3][]

> 1.  谨慎使用ENUM，TEXT字符串类型
> 2.  同财务相关的数值型数据，必需使用DECIMAL类型

--------------------

# 5 JAVA vs MySQL #

> [https://www.cnblogs.com/jerrylz/p/5814460.html][https_www.cnblogs.com_jerrylz_p_5814460.html]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70][]

<table> 
 <thead> 
  <tr> 
   <th style="width:98px;">数据库类型</th> 
   <th style="width:232px;">JAVA类型</th> 
   <th><strong>描述</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:98px;">tinyint(1)</td> 
   <td style="width:232px;">java.lang.Boolean</td> 
   <td>对于boolen类型，在mysql数据库中，个人认为用int类型代替较好，对bit操作不是很方便，尤其是在具有web页面开发的项目中，表示0/1，对应java类型的Integer较好。</td> 
  </tr> 
  <tr> 
   <td style="width:98px;">&nbsp;</td> 
   <td style="width:232px;">&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:98px;">&nbsp;</td> 
   <td style="width:232px;">&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

CREATE TABLE `t_verification`
    (
        `num1`  INT(10) UNSIGNED comment '对应JAVA中的Integer',
        `num2`  BIGINT comment '对应JAVA中的Long',
        `judge` tinyint(1) comment '对应JAVA中的Boolean',
        `time`  TIMESTAMP comment '对应JAVA中的Timestamp、Date'
    );

[https_dev.mysql.com_doc_refman_8.0_en_keywords.html]: https://dev.mysql.com/doc/refman/8.0/en/keywords.html
[https_www.runoob.com_mysql_mysql-data-types.html]: https://www.runoob.com/mysql/mysql-data-types.html
[http_cmsblogs.com_p_5501_vip_1]: http://cmsblogs.com/?p=5501&vip=1
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly95dWFueXUuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200131214420908.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly95dWFueXUuYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70
[20200131214802179.png]: https://img-blog.csdnimg.cn/20200131214802179.png
[20200607210313150.png]: https://img-blog.csdnimg.cn/20200607210313150.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly95dWFueXUuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200131221217731.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly95dWFueXUuYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly95dWFueXUuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200131222323756.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly95dWFueXUuYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly95dWFueXUuYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/2020013122563592.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly95dWFueXUuYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70
[https_www.cnblogs.com_jerrylz_p_5814460.html]: https://www.cnblogs.com/jerrylz/p/5814460.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200608152150992.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwNzk0OTcz,size_16,color_FFFFFF,t_70