day57-2 MySQL基本数据类型

痛定思痛。 2023-10-09 11:30 43阅读 0赞

*  mysql中的数据类型
    
     *  1 整数
        
         *  注意：默认情况整数是有符号的
     *  2 浮点数
        
         *  注意：同样是默认有符号，设置无符号的方式和上面相同
     *  3 字符串
        
         *  char(10)
         *  text
         *  blob
         *  注意：字符串右端空格自动清除
     *  4 日期和时间
        
         *  date/time/datetime 示例
         *  timestamp示例
     *  5 枚举 集合
     *  设置严格/非严格模式

# mysql中的数据类型 #

## 1 整数 ##

<table> 
 <thead> 
  <tr> 
   <th style="text-align:center;">类型</th> 
   <th style="text-align:center;">大小</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:center;">tinyint</td> 
   <td style="text-align:center;">1 字节</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">smallint</td> 
   <td style="text-align:center;">2 字节</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">mediumint</td> 
   <td style="text-align:center;">3 字节</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">int</td> 
   <td style="text-align:center;">4 字节</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">bigint</td> 
   <td style="text-align:center;">8字节</td> 
  </tr> 
 </tbody> 
</table>

1个字节就是8位，范围是有符号(-128, 127)，有符号(0, 255)，(之前的博客中有一篇详细介绍)

### 注意：默认情况整数是有符号的 ###

设置为无符号的三种方式

*  新建表的时候：`create table 表名(字节名 tinyint unsigned);`
 *  新建字节：`alter table 表名 add 字节名 tinyint unsigned;`
 *  修改字节：`alter table 表名 modify 字节名 tinyint unsigned;`

# 测试整数默认情况下有无符号 mysql> create table t1(id tinyint); # 严格模式就会报错，非严格模式就会保存成127，说明是有符号的 mysql> insert into t1 values(200); # 指定长度 mysql> create table t2(id int(10) zerofill); mysql> insert into t2 values(1); mysql> select * from t2; +------------+ | id | +------------+ | 0000000001 | +------------+ 1 row in set (0.01 sec) # int(10) 如果你的数据不足10位，用0填充（需要添加zerofill 这个约束） # 对于整数和小数而言，都是用来控制显示填充的最小长度

## 2 浮点数 ##

<table> 
 <thead> 
  <tr> 
   <th style="text-align:center;">类型</th> 
   <th style="text-align:center;">大小</th> 
   <th style="text-align:center;">精确程度</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:center;">float</td> 
   <td style="text-align:center;">4字节</td> 
   <td style="text-align:center;">不精确（大约5位小数）</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">double</td> 
   <td style="text-align:center;">8字节</td> 
   <td style="text-align:center;">不精确（比float更加精确）</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">decimal</td> 
   <td style="text-align:center;">需要指定</td> 
   <td style="text-align:center;">精确(65, 30)</td> 
  </tr> 
 </tbody> 
</table>

# float mysql> create table t3(id float); mysql> insert into t3 values(1.2222222222); mysql> select * from t3; +---------+ | id | +---------+ | 1.22222 | +---------+ 1 row in set (0.00 sec) # double mysql> create table t4(id double); mysql> insert into t4 values(1.22222222222222222); mysql> select * from t4; +--------------------+ | id | +--------------------+ | 1.2222222222222223 | +--------------------+ 1 row in set (0.00 sec) # decimal mysql> create table t5(id decimal(4, 3)); Query OK, 0 rows affected (0.04 sec) # float(10) 和int同样，补充整数位

*  decimal
    
     *  使用方法decimal(x, y) x是总长度，y是小数点后长度
     *  范围内不会丢失精度，范围外会提醒丢失
     *  总长度最大65，小数长度最大30，超出范围就报错

### 注意：同样是默认有符号，设置无符号的方式和上面相同 ###

## 3 字符串 ##

<table> 
 <thead> 
  <tr> 
   <th style="text-align:center;">类型</th> 
   <th style="text-align:center;">大小</th> 
   <th style="text-align:left;">用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:center;">CHAR</td> 
   <td style="text-align:center;">0-255字节</td> 
   <td style="text-align:left;">定长字符串</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">VARCHAR</td> 
   <td style="text-align:center;">0-65535 字节</td> 
   <td style="text-align:left;">变长字符串</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">TINYBLOB</td> 
   <td style="text-align:center;">0-255字节</td> 
   <td style="text-align:left;">不超过 255 个字符的二进制字符串</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">TINYTEXT</td> 
   <td style="text-align:center;">0-255字节</td> 
   <td style="text-align:left;">短文本字符串</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">BLOB</td> 
   <td style="text-align:center;">0-65 535字节</td> 
   <td style="text-align:left;">二进制形式的长文本数据</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">TEXT</td> 
   <td style="text-align:center;">0-65 535字节</td> 
   <td style="text-align:left;">长文本数据</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">MEDIUMBLOB</td> 
   <td style="text-align:center;">0-16 777 215字节</td> 
   <td style="text-align:left;">二进制形式的中等长度文本数据</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">MEDIUMTEXT</td> 
   <td style="text-align:center;">0-16 777 215字节</td> 
   <td style="text-align:left;">中等长度文本数据</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">LONGBLOB</td> 
   <td style="text-align:center;">0-4 294 967 295字节</td> 
   <td style="text-align:left;">二进制形式的极大文本数据</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">LONGTEXT</td> 
   <td style="text-align:center;">0-4 294 967 295字节</td> 
   <td style="text-align:left;">极大文本数据</td> 
  </tr> 
 </tbody> 
</table>

### char(10) ###

*  存储'abc' 占10个字符长度
 *  优点：:读取存储速度快于 varchar
 *  缺点：浪费磁盘空间
    
    ### varchar(10) ###
 *  存储'abc' 占4个字符(3个长度字符外加一个bit存储字符长度)
 *  优点：节省空间
 *  缺点：速度慢于char类型

# char如果不规定长度，那默认是1 mysql> create table t8(id, char); mysql> insert into t8 values('123'); # 报错 # char规定长度，即便输入的长度小于最大长度，也就保存为最大长度 # carchar规定长度，保存长度为输入长度+1 # 测不出来 # 在字符串后面加空格，mysql会自动删掉 mysql> insert into t8 values(1 , 2 ); mysql> select * from t8;

**如果字符长度不大，建议使用char类型，反之则选择varchar**

### text ###

*  具备编码的大字符串

### blob ###

*  没有编码的大二进制
 *  可以用来储存多来提数据，但一般不会存，都是建一个文件服务器，然后用数据库保存文件的地址信息

### 注意：字符串右端空格自动清除 ###

*  mysql会自动将字符串后面的空格删掉，所以你的数据在存储前应该先把空格处理掉

## 4 日期和时间 ##

<table> 
 <thead> 
  <tr> 
   <th style="text-align:left;">类型</th> 
   <th style="text-align:left;">大小 (字节)</th> 
   <th style="text-align:left;">范围</th> 
   <th style="text-align:left;">格式</th> 
   <th style="text-align:left;">用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:left;">date</td> 
   <td style="text-align:left;">3</td> 
   <td style="text-align:left;">1000-01-01/9999-12-31</td> 
   <td style="text-align:left;">YYYY-MM-DD</td> 
   <td style="text-align:left;">年月日</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">time</td> 
   <td style="text-align:left;">3</td> 
   <td style="text-align:left;">'-838:59:59'/'838:59:59'</td> 
   <td style="text-align:left;">HH:MM:SS</td> 
   <td style="text-align:left;">时分秒</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">year</td> 
   <td style="text-align:left;">1</td> 
   <td style="text-align:left;">1901/2155</td> 
   <td style="text-align:left;">YYYY</td> 
   <td style="text-align:left;">年份值</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">datetime</td> 
   <td style="text-align:left;">8</td> 
   <td style="text-align:left;">1000-01-01 00:00:00/9999-12-31 23:59:59</td> 
   <td style="text-align:left;">YYYY-MM-DD\HH:MM:SS</td> 
   <td style="text-align:left;">年月日时分秒</td> 
  </tr> 
  <tr> 
   <td style="text-align:left;">timeshamp</td> 
   <td style="text-align:left;">4</td> 
   <td style="text-align:left;">1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07</td> 
   <td style="text-align:left;">YYYYMMDD\HHMMSS</td> 
   <td style="text-align:left;">混合日期和时间值，时间戳</td> 
  </tr> 
 </tbody> 
</table>

year date time 都可以用字符串或数字两种方式输入值

datetime timestamp 必须用字符串????

**上述所有的类型年份都可以是两位数，0-69翻译为2000-2069 70-99 翻译为1970-1999**

# date time year datetime timestamp都可以使用字符串和整形两种方式添加记录，并且年份可以简写 # date mysql> create table t9(y date); mysql> insert into t9 values('09-01-01'); mysql> select * from t9; +------------+ | y | +------------+ | 2009-01-01 | +------------+ 1 row in set (0.00 sec) mysql> insert into t9 values(090101); mysql> select * from t9; # datetime mysql> create table t11(dt datetime); # 时间也可以不写，月日必须写，年可以不写，默认2000年 mysql> insert into t11 values(0101); mysql> insert into t11 values(120202); mysql> insert into t11 values('13-03-03 10:10:00') mysql> select * from t11; +---------------------+ | dt | +---------------------+ | 2000-01-01 00:00:00 | | 2012-02-02 00:00:00 | | 2013-03-03 10:10:00 | +---------------------+ 3 rows in set (0.00 sec) #timestamp mysql> create table t12(ts timestamp); mysql> insert into t12 values(121001); mysql> insert into t12 values('10-05-01 10:10:10'); mysql> insert into t12 values(100101123000); mysql> select * from t12; +---------------------+ | ts | +---------------------+ | 2012-10-01 00:00:00 | | 2010-05-01 10:10:10 | | 2010-01-01 12:30:00 | +---------------------+ 3 rows in set (0.00 sec) # 添加当前时间 # 时间相关的函数:now() mysql> insert into t12 values(now()); # 时间相关的常量:CURRENT_TIME mysql> insert into t12 values(current_time);

### date/time/datetime 示例 ###

mysql> create table t4 (d date,t time,dt datetime); mysql> desc t4; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | d | date | YES | | NULL | | | t | time | YES | | NULL | | | dt | datetime | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.01 sec) # 使用now()获取当前时间 mysql> insert into t4 values (now(),now(),now()); mysql> select * from t4; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 | +------------+----------+---------------------+ 1 row in set (0.00 sec) # 添加null mysql> insert into t4 values (null,null,null); mysql> select * from t4; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 | | NULL | NULL | NULL | +------------+----------+---------------------+ 2 rows in set (0.00 sec)

### timestamp示例 ###

mysql> create table t5 (id1 timestamp); mysql> desc t5; +-------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-----------------------------+ | id1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------+-----------+------+-----+-------------------+-----------------------------+ row in set (0.00 sec) # 插入数据null，会自动插入当前时间的时间 mysql> insert into t5 values (null); mysql> select * from t5; +---------------------+ | id1 | +---------------------+ | 2019-07-12 00:36:41 | +---------------------+ 1 row in set (0.00 sec) #添加一列 默认值是'0000-00-00 00:00:00' mysql> alter table t5 add id2 timestamp; mysql> show create table t5 \G # 这里加;会报错 *************************** 1. row *************************** Table: t5 Create Table: CREATE TABLE `t5` ( `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) # 手动修改新的列默认值为当前时间 mysql> alter table t5 modify id2 timestamp default current_timestamp; mysql> show create table t5 \G *************************** 1. row *************************** Table: t5 Create Table: CREATE TABLE `t5` ( `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> insert into t5 values (null,null); Query OK, 1 row affected (0.01 sec) mysql> select * from t5; +---------------------+---------------------+ | id1 | id2 | +---------------------+---------------------+ | 2019-07-12 00:36:41 | 0000-00-00 00:00:00 | | 2019-07-12 00:41:10 | 2019-07-12 00:41:10 | +---------------------+---------------------+ 2 rows in set (0.00 sec)

## 5 枚举 集合 ##

枚举是提前规定一个范围 你的值只能是其中之一 多选一

集合是提前规定一个范围 你的值可以是其中的多个 多选多

集合在插入数据时 ,多个值之前用逗号隔开 但他们必须包含在同一个字符串内!

# enum，不能插入不属于设定值的值，也只能插入一个值 mysql> create table t11(gender enum('man', 'woman')); mysql> insert into t11 values('123'); # 报错 mysql> insert into t11 values('man, woman'); # 报错 mysql> insert into t11 values('man'); # set 不能插入不属于设定值的值，可以插入多个值，会去重 mysql> create table t13(hobby set('swim', 'play', 'run', 'read')); mysql> insert into t13 values ('swim,play,swim');

## 设置严格/非严格模式 ##

mysql 5.6默认是非严格模式，如果你的数据超出范围，不会报错，会自动取该数据类型的最大值，所以容易丢失数据，可以进行一些修改。

# 查看当前的sql模式 select @@sql_mode; # @表示全局变量 @@表示会话级变量(局部) +--------------------------------------------+ | @@sql_mode | +--------------------------------------------+ | NO_ENGINE_SUBSTITUTION | +--------------------------------------------+ # 临时设置sql_mode，退出登录后又得设置 set @@sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'; # 原有的 设置为严格模式 # 想要永久设置，直接修改配置文件即可 [mysqld] sql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

*  局部变量：只对当前用户有效
 *  全局变量：所有客户端都有效

转载于:https://www.cnblogs.com/lucky75/p/11173708.html