MySQL数据类型详解-优快云博客

本文链接：https://blog.youkuaiyun.com/googtiki/article/details/78960791

整形

/*
    MySQL主要提供的整数类型有TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT.
    这五种数据类型分别占用的字节数为TINYINT(1),SMALLINT(2),MEDIUMINT(3),
    INT(4),BIGINT(8),不同类型的整数占用的字节数也不用，当然所能表示的数字
    范围也不一样，可以根据字节数计算出每一种整数类型的取值范围，如下表
    TINYINT                 -128~127                           0~255
    SMALLINT                -32768~32767                       0~65535
    MEDIUMINT               -8388608~8388607                   0~16777215
    INT                 -2147483648~2147483647                 0~4294967295
    BIGINT          -9223372036854775808~-9223372036854775807  0~18446744073709551615
    整数类型的字段属性可以添加AUTO_INCREMENT自增约束条件。

    MySQL支持可以在该类型的关键字后面的括号内指定整数值的显示宽度，可使用
    INT(M)进行设置。其中M指示最大显示宽度，例如，INT(4)表示最大有效显示
    宽度为4.需要注意的是显示宽度与存储大小或类型包含的值的范围无关。
*/

/*整型字段的显示宽度*/
USE onlinegame;
CREATE TABLE dtEx(datayear INT(4));
/*使用DESC命令查看表中的字段*/
DESC dtEx;
/*插入3个年份记录*/
INSERT INTO dtEx VALUES(123),(2345),(12345);
/*使用SELECT命令查询表中的年份记录信息*/
SELECT * FROM dtEx;
/*
    说明:显示宽度只是指明MySQL最大可能显示的数值个数，数值的个数如果小于指定的宽度时，
    显示会由空格填充；如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，
    数据依然可以插入，而且显示无误。
*/

/*
    默认显示宽度：
    所有整数类型的值，如果在定义表结构的使用没有使用类似于INT(M)这种方式给出一个明确
    的显示宽度，那么系统就会为每一种类型指定一个默认的宽度值。
*/
USE onlinegame;
CREATE TABLE dtEx1(a TINYINT, b SMALLINT, c MEDIUMINT, d INT, e BIGINT);
DESC dtEx1;
/*
    说明:系统为每种数据类型添加了不同的默认显示宽度，这些现实宽度能够保证
    每一种数据类型的取值范围内所有的值都能被正常显示。
    因此整数类型的默认显示宽度应该与其有符号数的最小值的宽度相同。
*/

浮点型

/*
    MySQL中使用浮点数和定点数表示小数。
    浮点类型有两种:单精度浮点型(FLOAT)和双精度浮点型(DOUBLE)
    定点类型只有一种DECIMAL.
    浮点类型和定点类型都可以用(M, D)来表示，其中M称为精度，表示总共的位数。
    D称为标度，表示小数的位数。
    浮点类型的取值范围为:M(1~255),D(1~30，且不大于M-2)，分别表示显示宽度和小数位数。
    M和D在FLOAT和DOUBLE中是可选的，MySQL3.23.6以上版本中，FLOAT和DOUBLE类型将被
    保存为硬件所支持的最大精度。
    DECIMAL的M和D在MySQL3.23.6以后的版本中变为可选，默认D值为0，M值为10.
*/

/*
    数据在内存中所占用字节数如下：
    FLOAT               4字节
    DOUBLE              8字节
    DECIMAL(M, D)       M+2个字节
    这里要做一个解释说明，DECIMAL类型不同于FLOAT和DOUBLE，其中DECIMAL实际上是
    以字符串存储的。DECIMAL可能的最大取值范围与DOUBLE一样，但是其有效的取值范围
    由M和D来决定。如果改变M而固定D，则其取值范围将随M的变大而变大。如果固定M而
    改变D，则其取值范围将随D的变大而变小(但精度增加)。因此DECIMAL的存储空间也是
    不固定的，由其精度值M来决定，占用M+2个字节。

    每一种浮点类型的取值范围，如下表
    1.FLOAT:
    (1)有符号的取值范围:-3.402823466E+38 ~ -1.175494351E-38
    (2)无符号的取值范围:0和1.175494351E-38 ~ 3.402823466E+38
    2.DOUBLE:
    (1)有符号的取值范围:-1.7976931348623157E+308 ~ -2.2250738585072014E-308
    (2)无符号的取值范围:0和2.2250738585072014E-308 ~ 1.7976931348623157E+308

    PS:无论是定点类型还是浮点类型，如果用户指定的精度值超过了精度范围，则会
    进行四舍五入的处理。
*/

USE onlinegame;
/*创建字段为浮点数据类型的表*/
CREATE TABLE dtEx2(a FLOAT(4, 1), b DOUBLE(4, 1), c DECIMAL(4, 1));
/*向表中插入3个不同精度的浮点数据*/
INSERT INTO dtEx2 VALUES(4.23, 4.26, 4.234);
/*使用SHOW WARNINGS命令查看警告信息*/
SHOW WARNINGS;
/*使用SELECT命令查询表中的数据记录信息*/
SELECT * FROM dtEx2;

/*
    如果字段是FLOAT或者DOUBLE类型那么当插入数据超过其精度范围时，MySQL系统不会给出
    任何警告，但是会对数据做四舍五入处理。
    如果字段是DECIMAL类型那么当插入数据超过其精度范围时，MySQL系统会给出一个警告，
    并且会对数据做四舍五入处理。
    当程序中对精度要求比较高的时候(如货币，科学数据等)，尽量选择使用DECIMAL类型。
    另外当两个浮点数在进行减法和比较运算的时候容易出现问题，因此在使用浮点数类型时
    需要注意，应该尽量避免做两个浮点数的比较操作。
*/

日期与时间类型

/*
    MySQL中有多种表示日期的数据类型，主要有:
    YEAR,TIME,DATE,DATETIME,TIMESTAMP。
    例如:只需记录年份信息时，可以只用YEAR类型，而没有必要使用DATE。每一个类型都有合法的取值范围。
    当输入不合法的值时，系统会将"0"值插入到字段中。参看下表:
    YEAR                YYYY                        1901~2155                  1字节
    TIME              HH:MM:SS                  -838:59:59~838:59:59           3字节
    DATE             YYYY-MM-DD                 1000-01-01~9999-12-31          3字节
    DATETIME      YYYY-MM-DD HH:MM:SS           1000-01-01 00:00:00~
                                                9999-12-31 23:59:59             8字节
    TIMESTAMP     YYYY-MM-DD HH:MM:SS           1970-01-01 00:00:01~
                                                2038-01-19 03:14:07             4字节
*/

/*
一.YEAR类型
    YEAR类型使用单字节表示年份，存储时只需要一个字节。可以使用不同格式指定YEAR的值。
    (1)以4位字符串或者4位数字格式表示YEAR，其范围为'1901'~'2155'，输入格式为'YYYY'或YYYY。
    例如，输入'2015'或2015，插入到数据库的值都是2015.
    (2)以2位字符串格式表示YEAR，范围为'00'到'99'。'00'~'69'和'70'~'99'范围的值分别被转化为
    2000~2069以及1970~1999范围的YEAR值。输入'0'与'00'取值相同，都是2000.插入超过取值范围
    的值将被转换为2000.
    (3)以2位数字格式表示YEAR，范围为1~99.1~69和70~99范围的值分别被转换为2001~2069和
    1970~1999范围的YEAR值。注意:0值被转换为0000，而不是2000.
    两位整数和两位字符串的取值范围稍有不同。例如:插入2000年，很多人以为使用数字格式的0表示
    YEAR，实际上插入数据库的值为0000，而不是期望的2000.只有使用字符串格式的'0'和'00'，才可以
    得到2000.非法YEAR值被转换为0000.
*/

/*创建数据表Ex3，定义数据类型为YEAR的字段a，向表中插入值2015，'2015'，'2288'。*/
USE onlinegame;
CREATE TABLE dtEx3(a YEAR);
/*测试插入4位表示的数据*/
INSERT INTO dtEx3 VALUES(2015), ('2015');
/*超出范围则报错*/
INSERT INTO dtEx3 VALUES('2288');
DESC dtEx3;
SELECT * FROM dtEx3;

/*清空表中的所有记录*/
DELETE FROM dtEx3;
SHOW VARIABLES LIKE 'sql_safe%';
SET sql_safe_updates=off;
/*测试插入1位或2位字符串表示的数据*/
INSERT INTO dtEx3 VALUES('0'), ('00'), ('89'), ('15');
SELECT * FROM dtEx3;
/*测试插入1位或2位数值表示的数据*/
INSERT INTO dtEx3 VALUES(0), (00), (89), (15);
SELECT * FROM dtEx3;

/*
二.TIME类型
    TIME类型用在只需记录时间信息的值，需要3个字节存储。格式为'HH:MM:SS'，HH表示小时，MM表示
    分钟，SS表示秒。可以使用多种格式指定TIME的值。
    (1)'D HH:MM:SS'格式的字符串。也可以使用以下任何一种非严格的语法:
    'HH:MM:SS' 'HH:MM' 'D HH:MM' 'D HH' 'SS'。其中D表示日，取值范围是0~34.再插入数据库时，
    D被转换为小时保存，格式为'D*24+HH'。
    (2)'HHMMSS'格式的，没有分隔符的字符串或HHMMSS格式的数值，假定是有意义的时间。例如:'105508'
    被理解为'10:55:08'，但'106508'是不合法的，因为其分钟部分超出合理范围，存储时将变为00:00:00。

    注意:为TIME类型输入值时，如果没有冒号隔开，MySQL解释值得时候，假定最右边的两位表示秒。MySQL解释
    TIME值为过去的时间不是当天的时间。例如'1011'和1011初学者可能认为表示的是10:11:00，即10点11分，
    但是MySQL的解释为00:10:11，即10分11秒。同样'20'和20被解释为00:00:20。
    但是如果TIME值中使用冒号时则一定被当作当天的时间，'10:11'表示10:11:00。
*/

/*测试MySQL TIME数据类型*/
USE onlinegame;
CREATE TABLE dtEx4(a TIME);
DESC dtEx4;
INSERT INTO dtEx4 VALUES('10:55:08'), ('22:59'), ('3 10:55'), ('4 08'), ('20');
SELECT * FROM dtEx4;
/*在使用'D HH'格式的时候，小时一定要使用双位数值，如果是小于10的小时数，应在前面补0.*/

/*测试TIME 向表中插入系统当前时间*/
DELETE FROM dtEx4;
INSERT INTO dtEx4 VALUES(CURRENT_TIME),(NOW());

/*
三.DATE类型
    DATE类型用在仅需要存储日期值的时候，不存储时间，存储需要3个字节。YYYY表示年，
    MM表示月，DD表示日。给DATE数据赋值时可以使用字符串类型或者数值类型的数据，只要符合以下
    日期格式即可。
    (1)以'YYYY-MM-DD'或者'YYYYMMDD'字符串格式表示，取值范围为'1000-01-01'~'9999-12-31'。
    (2)以'YY-MM-DD'或者'YYMMDD'字符串格式表示，YY表示两位年份值。'00~69'转换为'2000~2069'，
    '70~99'转换为'1970~1999'。
    (3)以YYMMDD数值格式表示日期，00~69'转换为'2000~2069，70~99转换为1970~1999。
    使用CURRENT_DATE或者NOW()插入当年计算机系统的日期。
*/

/*测试4位字符表示年份格式*/
CREATE TABLE dtEx5(a DATE);
INSERT INTO dtEx5 VALUES('1998-10-30'),('19981030'),('20150316');
SELECT * FROM dtEx5;
/*测试2位字符表示年份格式*/
DELETE FROM dtEx5;
INSERT INTO dtEx5 VALUES('98-12-31'), ('981231'), ('001231'), ('151213');
SELECT * FROM dtEx5;
/*测试2位数字表示年份格式*/
DELETE FROM dtEx5;
INSERT INTO dtEx5 VALUES(981231), (001231), (151213);
SELECT * FROM dtEx5;
INSERT INTO dtEx5 VALUES(98-12-31);
/*测试插入系统当前时间*/
DELETE FROM dtEx5;
INSERT INTO dtEx5 VALUES(CURRENT_DATE);
INSERT INTO dtEx5 VALUES(NOW());
SHOW WARNINGS;
SELECT * FROM dtEx5;

/*
    由于NOW()函数得到的是系统的日期和时间，但是字段a的定义是DATE类型，所以类型不一致，系统提出警告。
    虽然被警告，系统还是正确插入了两个日期。
    MySQL允许"不严格"的语法插入日期，任何标点符号都可以作为日期部分的间隔符。例如:'95-12-12',
    '95.12.31','95/12/31'和'95@12@31'等字符串都是等价的，这些都可以正确的插入到数据库中。
*/

/*
四.DATETIME类型
    DATETIME类型同时包含日期值和时间值，存储需要8个字节。YYYY表示年，MM表示月，DD表示日，HH表示小时，
    MM表示分钟，SS表示秒。给DATETIME数据赋值时可以使用字符串类型或者数值类型的数据，只要符合以下
    日期格式即可。
    (1)以'YYYY-MM-DD HH:MM:SS'或者'YYYYMMDDHHMMSS'字符串格式表示，取值范围为'1000-01-01 00:00:00'
    ~'9999-12-31 23:59:59'。
    (2)以'YY-MM-DD HH:MM:SS'或者'YYMMDDHHMMSS'字符串格式表示，YY表示两位年份值,与DATE的转换规则一样。
    '00~69'转换为'2000~2069'，'70~99'转换为'1970~1999'。
    (3)以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数值格式表示日期，00~69'转换为'2000~2069，70~99转换为1970~1999。
    使用CURRENT_DATE或者NOW()插入当年计算机系统的日期。
*/

/*测试4位字符表示年份格式的时间日期*/
CREATE TABLE dtEx6(a DATETIME);
INSERT INTO dtEx6 VALUES('1999-09-09 09:09:09'),('19990909090909'),('20150303030303');
SELECT * FROM dtEx6;
/*测试2位字符表示年份格式的时间日期*/
DELETE FROM dtEx6;
INSERT INTO dtEx6 VALUES('99-09-09 09:09:09'),('990909090909'),('150303030303');
SELECT * FROM dtEx6;
/*测试插入数值类型的时间日期*/
DELETE FROM dtEx6;
INSERT INTO dtEx6 VALUES(19981231121212),(150303030303);
SELECT * FROM dtEx6;
/*测试插入系统当前时间*/
DELETE FROM dtEx5;
INSERT INTO dtEx5 VALUES(CURRENT_DATE);
INSERT INTO dtEx5 VALUES(NOW());
SELECT * FROM dtEx5;
/*
    MySQL允许"不严格"的语法插入日期，任何标点符号都可以作为日期部分或时间部分的间隔符。例如:'95-12-13 11:28:30',
    '95.12.31 11+28+30','95/12/31 11*28*30'和'95@12@31 11^28^30'等字符串都是等价的，这些都可以正确的插入到数据库中。
*/

MySQL错误：ERROR 1175: You are using safe update mode 解决方法

MySQL错误：ERROR 1175: You are using safe update mode 解决方法
操作MySQL数据库，删除表中的某一行数据提示如下错误：ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

错误提示：正在使用安全更新模式，尝试更新表没有使用键列的where条件；

原因是：mysql有个叫SQL_SAFE_UPDATES的变量，为了数据库更新操作的安全性，此值默认为1，所以才会出现更新失败的情况。
举例如下：
mysql> select * from test;
+—-+——–+
| id | name |
+—-+——–+
| 1 | anglea |
| 2 | baby |
| 3 | jerry |
| 4 | tom |
| 5 | yong |
+—-+——–+
mysql> delete from test where name=’yong’;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
查看设置：
mysql> show variables like ‘sql_safe%’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| sql_safe_updates | ON |
+——————+——-+
下面是SQL_SAFE_UPDATES变量为0和1时的取值说明：
SQL_SAFE_UPDATES有两个取值0和1，或ON 和OFF;
SQL_SAFE_UPDATES = 1，ON时，不带where和limit条件的update和delete操作语句是无法执行的，即使是有where和limit条件但不带key column的update和delete也不能执行。
SQL_SAFE_UPDATES =0，OFF时，update和delete操作将会顺利执行。那么很显然，此变量的默认值是1。
所以，出现1175错误的时候，可以先设置SQL_SAFE_UPDATES的值为0 OFF，然后再执行更新;
以下2条命令都可以；
mysql> set sql_safe_updates=0;
mysql> set sql_safe_updates=off;
mysql> show variables like ‘sql_safe%’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| sql_safe_updates | OFF |
+——————+——-+
mysql> delete from test where name=’yong’;
Query OK, 1 row affected (0.00 sec)
更改只在当前生效，退出mysql，再次登录后恢复为默认。