mysql数据类型

002-MySQL数据类型

 

==========================================数据类型  ==========================================

1、数字类型：

    整数类型          字节         范围（有符号）        范围（unsigned）          用途
    TINYINT          1字节          (-128，127)            (0，255)                小整数值
    SMALLINT         2字节       (-32768，32767)          (0，65535)             大整数值
    MEDIUMINT        3字节      (-8388608，8388607)     (0，16777215)          大整数值
    INT或INTEGER     4字节    (-2147483648，2147483647) (0，4294967295)       大整数值：21亿，10位数
    BIGINT                 8字节   (-9.23E+18，9.22E+18)       (0，1.84E+19)         极大整数值 19位数
    FLOAT                  4字节   (-3.4E+38，1.17E-38)，0,(1.17E-38，3.4E+38)     单精度浮点数值
    DOUBLE               8字节   (1.79E+308，2.22E-308)，0，2.22E-308，1.79E+308) 双精度浮点数值
    DECIMAL             对DECIMAL(M,D)   其中M表示十进制数字总的个数，D表示小数点后面数字的位数。如果M>D，为M+2否则为D+2
                  M的默认取值为10，D默认取值为0。不带参数时，等同decimal(10,0),带一个参数时，D取默认值。
                  M的取值范围为1~65，取0时会被设为默认值，超出范围会报错。
                  D的取值范围为0~30，而且必须<=M，超出范围会报错。
                  所以，很显然，当M=65，D=0时，可以取得最大和最小值。

DECIMAL(5,2),整数部分超出了范围（如上面的例子中，添加数值为1000.01），MySql就会报错，不允许存这样的值。
小数点部分若超出范围，就分以下情况：
    若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后保存。如999.994实际被保存为999.99。
    若四舍五入后，整数部分超出范围，则MySql报错，并拒绝处理。如999.995和-999.995都会报错。

如果数据太大，可选方案，1
    将小数，整数分开存储成2个整数，取和存都分开存储2个字段。
可选方案2：存储成字符串。

float(M,D): 一共M位整数，其中D位位于小数点后面。假如超过D位则四舍五入，即1.233四舍五入为1.23，1.237四舍五入为1.24。
FLOAT和DOUBLE中的M和D的取值默认都为0，会按照实际的精度来处理。
FLOAT和DOUBLE中如果M和D都有明确定义，其超出范围后的处理同decimal。

M取值范围为0~255。FLOAT只保证6位有效数字的准确性，所以FLOAT(M,D)中，M<=6时，数字通常是准确的。
double只保证16位有效数字的准确性，所以DOUBLE(M,D)中，M<=16时，数字通常是准确的。

int(N)我们只需要记住两点：

    无论N等于多少，int永远占4个字节
    N表示的是显示宽度，不足的用0补足，但这要整型设置了unsigned zerofill才有效.超过的无视长度而直接显示整个数字.

    2、BIT位运算

    BIT数据类型可用来保存位字段值。BIT(M)类型允许存储M位值。M范围为1~64，默认为1。
    BIT其实就是存入二进制的值，类似010110。
    如果存入一个BIT类型的值，位数少于M值，则左补0.
    如果存入一个BIT类型的值，位数多于M值，MySQL的操作取决于此时有效的SQL模式：
    如果模式未设置，MySQL将值裁剪到范围的相应端点，并保存裁减好的值。
    如果模式设置为traditional(“严格模式”)，超出范围的值将被拒绝并提示错误，并且根据SQL标准插入会失败。

    mysql> CREATE TABLE t (b BIT(8));
    mysql> INSERT INTO t SET b = b'11111111';
    mysql> INSERT INTO t SET b = b'1010';
    mysql> INSERT INTO t SET b = b'0101';
    mysql> SELECT b+0, BIN(b+0), OCT(b+0), HEX(b+0) FROM t;
    +------+----------+----------+----------+
    | b+0  | BIN(b+0) | OCT(b+0) | HEX(b+0) |
    +------+----------+----------+----------+
    |  255 | 11111111 | 377      | FF       |
    |   10 | 1010     | 12       | A        |
    |    5 | 101      | 5        | 5        |
    +------+----------+----------+----------+

    3.字符串

    MySQL 提供了8个基本的字符串类型,分别:CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和SET等多种字符串类型。

      字符串类型     范围                      描述及存储需求
        CHAR(M)       最多255个字符                   定长字符串
        VARCHAR(M)    最多65535个字符                 变长字符串
        BINARY(M)                                     定长二进制字符串
        VARBINARY                                    变长二进制字符串
        TINYBLOB     可变长度，最多255个字符        不超过 255 个字符的二进制字符串
        TINYTEXT     可变长度，最多255个字符        短文本字符串
        BLOB         可变长度，最多65535个字符      二进制形式的长文本数据
        TEXT         可变长度，最多65535个字符      长文本数据
        MEDIUMBLOB   最多0-16777215个字符          二进制形式的中等长度文本数据
        MEDIUMTEXT   最多0-16777215个字符          中等长度文本数据
        LOGNGBLOB    最多0-4294967295个字符        二进制形式的极大文本数据
        LONGTEXT     最多0-4294967295个字符        极大文本数据

CHAR 和 VARCHAR 类型

1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。
3.varchar为可变长度字符串，在utf8编码的数据库中其长度范围为0~21844。

MySQL要求一个行的定义长度不能超过65535即64K
    （1）对于未指定varchar字段not null的字段，会有1个字节专门表示该字段是否为null。
    （2）varchar(M)，当M范围为0<=M<=255时会专门有一个字节记录varchar型字符串长度，当M>255时会专门有两个字节记录varchar型字符串的长度，把这一点和上一点结合，那么65535个字节实际可用的为65535-3=65532个字节
    （3）所有英文无论其编码方式，都占用1个字节，但对于gbk编码，一个汉字占两个字节，因此最大M=65532/2=32766；对于utf8编码，一个汉字占3个字节，因此最大M=65532/3=21844。
    （4）对于utfmb4编码方式，1个字符最大可能占4个字节，那么varchar(M)，M最大为65532/4=16383。
同样的，上面是表中只有varchar型数据的情况，如果表中同时存在int、double、char这些数据，需要把这些数据所占据的空间减去，才能计算varchar(M)型数据M最大等于多少。

text和varchar是一组既有区别又有联系的数据类型，其联系在于当varchar(M)的M大于某些数值时，varchar会自动转为text：

    M>255时转为tinytext
    M>500时转为text
    M>20000时转为mediumtext

所以过大的内容varchar和text没有区别，同事varchar(M)和text的区别在于：

    单行64K即65535字节的空间，varchar只能用63352/65533个字节，但是text可以65535个字节全部用起来
    text可以指定text(M)，但是M无论等于多少都没有影响
    text不允许有默认值，varchar允许有默认值。
    varchar和text两种数据类型，使用建议是能用varchar就用varchar而不用text（存储效率高），varchar(M)的M有长度限制，之前说过，如果大于限制，可以使用mediumtext（16M）或者longtext（4G）。

至于text和blob，text存储的是字符串而blob存储的是二进制字符串，简单说blob是用于存储例如图片、音视频这种文件的二进制数据的。

 

    4、日期和时间类型

     类型       大小(字节)        范围                                   格式                  用途
     DATE         4        1000-01-01/9999-12-31                     YYYY-MM-DD          日期值
     TIME         3        '-838:59:59'/'838:59:59'                    HH:MM:SS              时间值或持续时间
     YEAR         1         1901/2155                                    YYYY                   年份值
     DATETIME     8     1000-01-01 00:00:00/9999-12-31 23:59:59    YYYY-MM-DD HH:MM:SS   混合日期和时间值
     TIMESTAMP  4     1970-01-01 00:00:00/2038-01-19 03:14:07    YYYYMMDD HHMMSS        混合日期和时间值，时间戳

    MySQL在5.6.4版本之后，TIMESTAMP和DATETIME支持到微秒。
    TIMESTAMP会根据系统时区进行转换，DATETIME则不会，一般使用TIMESTAMP国际化

 MySQL 日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)

        MySQL datediff(date1,date2)：两个日期相减 date1 - date2，返回天数。
        select datediff('2008-08-08', '2008-08-01'); -- 7
        select datediff('2008-08-01', '2008-08-08'); -- -7

        MySQL timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值。
        select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
        select timediff('08:08:08', '00:00:00'); -- 08:08:08

注意：timediff(time1,time2) 函数的两个参数类型必须相同。

    5、复合类型

　　MySQL 还支持两种复合数据类型 ENUM 和 SET，一个 ENUM 类型只允许从一个集合中取得一个值；而 SET 类型允许从一个集合中取得任意多个值。

    ENUM 类型字段可以从集合中取得一个值或使用 null 值，插入值的大小写与集合中值的大小写不匹配，MySQL 会自动使用插入值的大小写转换成与集合中大小写一致的值。
    一个 ENUM 类型最多可以包含 65536 个元素，MySQL 认为 ENUM 类型集合中出现的值是合法输入，除此之外其它任何输入都将失败。

  　SET 类型与 ENUM 类型相似但不相同。SET 类型可以从预定义的集合中取得任意数量的值。并且与 ENUM 类型相同的是
任何试图在 SET 类型字段中插入非预定义的值都会使MySQL 插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录，
MySQL 将会保留合法的元素，除去非法的元素。

　 一个 SET 类型最多可以包含 64 项元素。SET 类型中不可能包含两个相同的元素。
希望从 enum，SET 类型字段中找出非法的记录只需查找包含空字符串或二进制值为 0 的行。

 

================================= 8.0 空间+json ==================
8.0的新类型
空间类型：geometry point linestring polygon
空间集合：geometrycollection  multipoint multilinestring multipolygon
JSON类型： json

查询出当前库的，所用到的字段类型：
SELECT table_name,column_name,DATA_TYPE FROM `COLUMNS` where TABLE_SCHEMA='icloud_jutong' GROUP BY DATA_TYPE;

找出当前数据库，不同字段类型最大定义长度：

select * from (
select table_name,column_name,data_type,column_type,max_len,
if(instr(max_len,',')=0,max_len,left(max_len,instr(max_len,',')-1)) as valid_len
from
(select table_name,column_name,data_type,column_type,
SUBSTRING(column_type,instr(column_type,'(')+1,instr(column_type,')')-instr(column_type,'(')-1) as max_len
from `COLUMNS` where TABLE_SCHEMA='icloud_jutong' GROUP BY column_type) s
ORDER BY data_type,cast(str_len as signed) desc limit 100000
) tt GROUP BY data_type

 

TABLE_NAME            COLUMN_NAME         DATA_TYPE     COLUMN_TYPE     MAX_LEN    VALID_LEN
project_upload_img    upload_img_size     decimal         decimal(16,2)       16,2        16
dust_video_monitor    video_size            double          double(11,2)        11,2        11
project_info            floorage              float            float(255,2)          255,2       255
ass_agent_business    as_id                   int               int(11)               11           11
about_us                about_id              smallint         smallint(6)          6             6
curing_sample          memo                  text             text
labour_attendance     work_time             time              time
sys_feedback           feed_status           tinyint          tinyint(32)          32          32
about_us                about_text           varchar           varchar(3000)       3000       3000