mysql基本操作,数据类型

by admin on 2019年4月11日

一、基本操作

壹、基本操作

壹、基本操作

一. INT类型

接连Mysql后,甘休一条sql语句要在后头加一个分号”;“。

连天Mysql后,甘休一条sql语句要在后边加三个分店”;“。

连日来Mysql后,结束一条sql语句要在末端加一个分公司”;“。

一. INT类型的归类

  • TINYINT
    • 存款和储蓄空间 : 一 字节
    • 取值范围
      • 有符号(signed) : [-128, 127]
      • 无符号(unsigned) :[0, 255]
  • SMALLINT
    • 仓库储存空间 : 2 字节
    • 取值范围
      • 有符号(signed) : [-32768, 32767]
      • 无符号(unsigned) :[0, 65535]
  • MEDIUMINT
    • 积存空间 : 三 字节
    • 取值范围
      • 有符号(signed) : [-8388608, 8388607]
      • 无符号(unsigned) :[0, 16777215]
  • INT
    • 积存空间 : 四 字节
    • 取值范围
      • 有符号(signed) : [-2147483648, 2147483647]
      • 无符号(unsigned) :[0, 4294967295]
  • BIGINT
    • 储存空间 : 八 字节
    • 取值范围
      • 有符号(signed) : [-9223372036854775808,
        9223372036854775807]
      • 无符号(unsigned) :[0, 18446744073709551615]
  • 创办数据库

    #在成立数量时,能够在数据库名前后加一个反引号(TAB键上的3个键),那样就足以用关键字创制2个数据库了,不然报错。
    mysql> create database customer;
    Query OK, 一 row affected (0.00 sec)

  • 查看mysql版本

    mysql> select version();
    +———–+
    | version() |
    +———–+
    | 5.6.38 |
    +———–+
    1 row in set (0.00 sec)

  • 翻看当前数据库

    mysql> select database();
    +————+
    | database() |
    +————+
    | NULL |
    +————+
    1 row in set (0.00 sec)

  • 查看当前的报到用户名

    mysql> select user();
    +—————-+
    | user() |
    +—————-+
    | root@localhost |
    +—————-+
    1 row in set (0.00 sec)

  • 翻看当前的登录用户名

    mysql> select current_user();
    +—————-+
    | current_user() |
    +—————-+
    | root@localhost |
    +—————-+
    1 row in set (0.00 sec)

  • 查看当前天牛时间

    mysql> select curtime(),curdate(),now();
    +———–+————+———————+
    | curtime() | curdate() | now() |
    +———–+————+———————+
    | 15:43:50 | 2018-01-15 | 2018-01-15 15:43:50 |
    +———–+————+———————+
    1 row in set (0.00 sec)

  • 翻看有多少数据库

    mysql> show databases;
    +——————–+
    | Database |
    +——————–+
    | information_schema |
    | mysql |
    | performance_schema |
    | test |
    +——————–+
    4 rows in set (0.01 sec)

  • 剔除2个钦命的数据库

  • 开创数据库

    #在开创数量时,能够在数据库名前后加1个反引号(TAB键上的一个键),那样就足以用关键字创设一个数据库了,不然报错。
    mysql> create database customer;
    Query OK, 一 row affected (0.00 sec)

  • 查看mysql版本

    mysql> select version();
    +———–+
    | version() |
    +———–+
    | 5.6.38 |
    +———–+
    1 row in set (0.00 sec)

  • 翻看当前数据库

    mysql> select database();
    +————+
    | database() |
    +————+
    | NULL |
    +————+
    1 row in set (0.00 sec)

  • 查看当前的登录用户名

    mysql> select user();
    +—————-+
    | user() |
    +—————-+
    | root@localhost |
    +—————-+
    1 row in set (0.00 sec)

  • 翻看当前的登录用户名

    mysql> select current_user();
    +—————-+
    | current_user() |
    +—————-+
    | root@localhost |
    +—————-+
    1 row in set (0.00 sec)

  • 查看当前些天期时间

    mysql> select curtime(),curdate(),now();
    +———–+————+———————+
    | curtime() | curdate() | now() |
    +———–+————+———————+
    | 15:43:50 | 2018-01-15 | 2018-01-15 15:43:50 |
    +———–+————+———————+
    1 row in set (0.00 sec)

  • 翻看有多少数据库

    mysql> show databases;
    +——————–+
    | Database |
    +——————–+
    | information_schema |
    | mysql |
    | performance_schema |
    | test |
    +——————–+
    4 rows in set (0.01 sec)

  • 去除3个钦定的数据库

  • 开创数据库

    #在创设数量时,能够在数据库名前后加二个反引号(TAB键上的三个键),那样就能够用关键字成立一个数据库了,不然报错。
    mysql> create database customer;
    Query OK, 一 row affected (0.00 sec)

  • 查看mysql版本

    mysql> select version();
    +———–+
    | version() |
    +———–+
    | 5.6.38 |
    +———–+
    1 row in set (0.00 sec)

  • home88一必发,翻看当前数据库

    mysql> select database();
    +————+
    | database() |
    +————+
    | NULL |
    +————+
    1 row in set (0.00 sec)

  • 查看当前的记名用户名

    mysql> select user();
    +—————-+
    | user() |
    +—————-+
    | root@localhost |
    +—————-+
    1 row in set (0.00 sec)

  • 翻看当前的报到用户名

    mysql> select current_user();
    +—————-+
    | current_user() |
    +—————-+
    | root@localhost |
    +—————-+
    1 row in set (0.00 sec)

  • 查看当前天申时间

    mysql> select curtime(),curdate(),now();
    +———–+————+———————+
    | curtime() | curdate() | now() |
    +———–+————+———————+
    | 15:43:50 | 2018-01-15 | 2018-01-15 15:43:50 |
    +———–+————+———————+
    1 row in set (0.00 sec)

  • 翻开有多少数据库

    mysql> show databases;
    +——————–+
    | Database |
    +——————–+
    | information_schema |
    | mysql |
    | performance_schema |
    | test |
    +——————–+
    4 rows in set (0.01 sec)

  • 剔除2个内定的数据库

2. INT类型的采纳

  • 自增长ID
    • 推荐使用BIGINT,而不是INT;
  • unsigned or signed

    • 依照实际景况选取,1般景况下推荐默认sigend
    • unsigned 的注意事项

    mysql> create database dbtype ;
    Query OK, 1 row affected (0.00 sec)

    mysql> use dbtype
    Database changed

    mysql> create table t_unsigned(a int unsigned,b int unsigned);
    Query OK, 0 rows affected (0.03 sec)

    mysql> insert into t_unsigned values(1,2);
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_unsigned;
    +——+——+
    | a | b |
    +——+——+
    | 1 | 2 |
    +——+——+
    1 row in set (0.00 sec)

    mysql> select a – b from t_unsigned;
    ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in ‘(dbtype.t_unsigned.adbtype.t_unsigned.b)’ –代表超出数据范围

    mysql> set sql_mode = ‘no_unsigned_subtraction’; –设置sql_mode模式
    Query OK, 0 rows affected (0.00 sec)

    mysql> select a – b from t_unsigned;
    +——-+
    | a – b |
    +——-+
    | -1 |
    +——-+
    1 row in set (0.00 sec)

    mysql>

  • 诚如景况下接纳int时,推荐有记号数(signed)
    使用无符号数只是比原来多一倍的取值,数量级上未曾改观。

  • 假使急需取值超越十一个人以上,直接选取用BIGINT类型

drop database 数据库名

drop database 数据库名

drop database 数据库名

3. INT(N)

mysql> show create table t_unsigned \G;
*************************** 1. row ***************************
       Table: t_unsigned
Create Table: CREATE TABLE `t_unsigned` (
  `a` int(10) unsigned DEFAULT NULL,
  `b` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
  • int(N) 和 zerofill

    • int(N)中的N是显得上涨幅度,不表示储存的数字的长度的上限。
    • zerofill意味着当存款和储蓄的数字长度 < N时,用数字0填充左侧,直至补满长度N
    • 当存款和储蓄数字的长度超过N时,按照实际存储的数显

    mysql> create table t_int_num(a int(3) zerofill); — 展现上涨幅度N=3Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_int_num values(10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_int_num values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_int_num;
+------+
| a    |
+------+
|  010 |     -- 不满 N=3时,左边用0填充
|  002 |
+------+
2 rows in set (0.00 sec)

mysql> insert into t_int_num values(444444);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_int_num;
+--------+
| a      |
+--------+
|    010 |
|    002 |
| 444444 |      -- 超过N=3的长度时,是什么数字,显示什么数字
+--------+
3 rows in set (0.00 sec)


mysql> select a, HEX(a) from t_int_num\G
*************************** 1. row ***************************
     a: 010
HEX(a): A      -- 实际存储的还是十六进制A
*************************** 2. row ***************************
     a: 002
HEX(a): 2
*************************** 3. row ***************************
     a: 444444
HEX(a): 6C81C    -- 实际存储的还是十六进制444444
3 rows in set (0.00 sec)
  • int(N)中的Nzerofill非凡才有意义,且只有是显示的时候才有含义,和事实上存款和储蓄没有提到,不会去截取数字的尺寸。
  • 切换贰个数据库为当前数据库
  • 切换三个数据库为近来数据库
  • 切换三个数据库为当前数据库

4. AUTO_INCREMENT

  • 自增
  • 每张表3个
  • 不可能不是索引的一部分

    mysql> create table t_auto_inc(a int auto_increment);
    E君越ROHaval 十7伍 (4三千): Incorrect table definition; there can be only one auto column and it must be defined as a key –自增类必须为主键

    mysql> create table t_auto_inc(a int auto_increment primary key);
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into t_auto_inc values(null); –插入null值
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_auto_inc;
    +—+
    | a |
    +—+
    | 一 | — 插入NULL值,便能够让其自增,且暗中同意从1初始+—+
    一 row in set (0.00 sec)

    mysql> insert into t_auto_inc values(1);
    EHighlanderRO悍马H2 十6二 (二三千): Duplicate entry ‘一’ for key ‘P哈弗IMAEvoqueY’ –自增配置主键必须为唯一值

    mysql> insert into t_auto_inc values(2);
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_auto_inc;
    +—+
    | a |
    +—+
    | 1 |
    | 2 |
    +—+
    2 rows in set (0.00 sec)

    mysql> insert into t_auto_inc values(2二); –插入数值,固然值超越原来自增值就会用新值+壹来代表
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_auto_inc;
    +—-+
    | a |
    +—-+
    | 一 |
    | 二 |
    | 2二 | –变成自增值
    +—-+
    叁 rows in set (0.00 sec)

    mysql> insert into t_auto_inc values(3); –插入值小于自增值2贰所以如故没变
    Query OK, 一 row affected (0.00 sec)

    mysql> select * from t_auto_inc;
    +—-+
    | a |
    +—-+
    | 1 |
    | 2 |
    | 3 |
    | 2二 | –自增值不变
    +—-+
    四 rows in set (0.00 sec)

    mysql> insert into t_auto_inc values(null); –当插如null值
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_auto_inc values(null); –再一次插入null值
    Query OK, 一 row affected (0.00 sec)

    mysql> select * from t_auto_inc;
    +—-+
    | a |
    +—-+
    | 一 |
    | 二 |
    | 三 |
    | 2二 |
    | 2三 | –自增值+1| 二肆 | –自增值再+一+—-+
    6 rows in set (0.00 sec)

    mysql> insert into t_auto_inc values(20); –插入20值小于自增值贰四Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_auto_inc;
    +—-+
    | a |
    +—-+
    | 一 |
    | 二 |
    | 3 |
    | 20 | –插入到自增值前面| 22 |
    | 二3 |
    | 二四 |
    +—-+
    柒 rows in set (0.00 sec)

    mysql> insert into t_auto_inc values(-1); –插入-1
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from t_auto_inc;
    +—-+
    | a |
    +—-+
    | -1 |
    | 1 |
    | 2 |
    | 3 |
    | 20 |
    | 22 |
    | 23 |
    | 24 |
    +—-+
    8 rows in set (0.00 sec)

mysql> insert into t_auto_inc values('0');
Query OK, 1 row affected (0.00 sec)

mysql> update t_auto_inc set a =0 where a =-1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_auto_inc;
+----+
| a  |
+----+
|  0 |
|  1 |
|  2 |
|  3 |
| 20 |
| 22 |
| 23 |
| 24 |
+----+
8 rows in set (0.00 sec)

mysql> insert into t_auto_inc values(0);  --数字 0 这个值比较特殊, 插入0和插入NULL的效果是一样的,都是代表自增
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_auto_inc;
+----+
| a  |
+----+
|  0 |
|  1 |
|  2 |
|  3 |
| 20 |
| 22 |
| 23 |
| 24 |
| 26 |
+----+
9 rows in set (0.00 sec)
  • AUTO_INCREMENT是实例运行时,取当前表的最大值,然后 +1即为下次自增的值。(MAX + 1)

  • 注意: insert into tablename select NULL; 等价与
    insert into tablename values (NULL);


use 数据库名

use 数据库名

use 数据库名

2. 数字类型

mysql> use test
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)
mysql> use test
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)
mysql> use test
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

一. 数字类型的分类

  • 单精度类型:FLOAT
    • 积存空间:四 字节
    • mysql基本操作,数据类型。精确性:低
  • 双精度类型:DOUBLE
    • 占用空间:八 字节
    • 精确性:低,比FLOAT高
  • 高精度类型:DE君威L
    • 占据空间:变长
    • 精确性:非常高

专注:财务系统必须使用DECIMAL


  • 翻看字符集

    mysql> show variables like ‘char%’;
    +————————–+————————–+
    | Variablemysql基本操作,数据类型。_name | Value |
    +————————–+————————–+
    | character_set_client | gbk |
    | character_set_connection | gbk |
    | character_set_database | latin1 |
    | character_set_filesystem | binary |
    | character_set_results | gbk |
    | character_set_server | latin1 |
    | character_set_system | utf8 |
    | character_sets_dir | c:\mysql\share\charsets\ |
    +————————–+————————–+
    8 rows in set (0.00 sec)

  • 查看字符集

    mysql> show variables like ‘char%’;
    +————————–+————————–+
    | Variable_name | Value |
    +————————–+————————–+
    | character_set_client | gbk |
    | character_set_connection | gbk |
    | character_set_database | latin1 |
    | character_set_filesystem | binary |
    | character_set_results | gbk |
    | character_set_server | latin1 |
    | character_set_system | utf8 |
    | character_sets_dir | c:\mysql\share\charsets\ |
    +————————–+————————–+
    8 rows in set (0.00 sec)

  • 翻开符集

    mysql> show variables like ‘char%’;
    +————————–+————————–+
    | Variable_name | Value |
    +————————–+————————–+
    | character_set_client | gbk |
    | character_set_connection | gbk |
    | character_set_database | latin1 |
    | character_set_filesystem | binary |
    | character_set_results | gbk |
    | character_set_server | latin1 |
    | character_set_system | utf8 |
    | character_sets_dir | c:\mysql\share\charsets\ |
    +————————–+————————–+
    8 rows in set (0.00 sec)

三. 字符串类型

  • 设置连接服务器及当前客户端的字符集

    mysql> set names utf8;
    Query OK, 0 rows affected (0.00 sec)

  • 翻看当前劳动援助的字符集

    mysql> show charset;
    +———-+—————————–+———————+——–+
    | Charset | Description | Default collation | Maxlen |
    +———-+—————————–+———————+——–+
    | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
    | dec8 | DEC West European | dec8_swedish_ci | 1 |
    | cp850 | DOS West European | cp850_general_ci | 1 |
    | hp8 | HP West European | hp8_english_ci | 1 |
    | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
    | latin1 | cp1252 West European | latin1_swedish_ci | 1 |
    | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
    | swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
    | ascii | US ASCII | ascii_general_ci | 1 |
    | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
    | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
    | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
    | tis620 | TIS620 Thai | tis620_thai_ci | 1 |
    | euckr | EUC-KR Korean | euckr_korean_ci | 2 |
    | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
    | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
    | greek | ISO 8859-7 Greek | greek_general_ci | 1 |
    | cp1250 | Windows Central European | cp1250_general_ci | 1 |
    | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
    | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
    | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
    | utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
    | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
    | cp866 | DOS Russian | cp866_general_ci | 1 |
    | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
    | macce | Mac Central European | macce_general_ci | 1 |
    | macroman | Mac West European | macroman_general_ci | 1 |
    | cp852 | DOS Central European | cp852_general_ci | 1 |
    | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
    | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
    | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
    | utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
    | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
    | cp1256 | Windows Arabic | cp1256_general_ci | 1 |
    | cp1257 | Windows Baltic | cp1257_general_ci | 1 |
    | utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
    | binary | Binary pseudo charset | binary | 1 |
    | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
    | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
    | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
    +———-+—————————–+———————+——–+
    40 rows in set (0.02 sec)

  • 翻开数据库引擎

    mysql> show engines;
    +——————–+———+————————————————
    —————-+————–+——+————+
    | Engine | Support | Comment

                | Transactions | XA   | Savepoints |
    

    +——————–+———+————————————————
    —————-+————–+——+————+
    | FEDERATED | NO | Federated MySQL storage engine

                | NULL         | NULL | NULL       |
    

    | MRG_MYISAM | YES | Collection of identical MyISAM tables

                | NO           | NO   | NO         |
    

    | MyISAM | YES | MyISAM storage engine

                | NO           | NO   | NO         |
    

    | BLACKHOLE | YES | /dev/null storage engine (anything you write to
    it disappears) | NO | NO | NO |
    | CSV | YES | CSV storage engine

                | NO           | NO   | NO         |
    

    | MEMORY | YES | Hash based, stored in memory, useful for tempor
    ary tables | NO | NO | NO |
    | ARCHIVE | YES | Archive storage engine

                | NO           | NO   | NO         |
    

    | InnoDB | DEFAULT | Supports transactions, row-level locking, and f
    oreign keys | YES | YES | YES |
    | PERFORMANCE_SCHEMA | YES | Performance Schema

                | NO           | NO   | NO         |
    

    +——————–+———+————————————————
    —————-+————–+——+————+
    9 rows in set (0.01 sec)

  • 修改数据库的字符集

    mysql> alter database db character set utf8;
    Query OK, 1 row affected (0.00 sec)

  • 设置连接服务器及当前客户端的字符集

    mysql> set names utf8;
    Query OK, 0 rows affected (0.00 sec)

  • 查阅当前劳动支撑的字符集

    mysql> show charset;
    +———-+—————————–+———————+——–+
    | Charset | Description | Default collation | Maxlen |
    +———-+—————————–+———————+——–+
    | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
    | dec8 | DEC West European | dec8_swedish_ci | 1 |
    | cp850 | DOS West European | cp850_general_ci | 1 |
    | hp8 | HP West European | hp8_english_ci | 1 |
    | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
    | latin1 | cp1252 West European | latin1_swedish_ci | 1 |
    | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
    | swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
    | ascii | US ASCII | ascii_general_ci | 1 |
    | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
    | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
    | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
    | tis620 | TIS620 Thai | tis620_thai_ci | 1 |
    | euckr | EUC-KR Korean | euckr_korean_ci | 2 |
    | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
    | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
    | greek | ISO 8859-7 Greek | greek_general_ci | 1 |
    | cp1250 | Windows Central European | cp1250_general_ci | 1 |
    | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
    | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
    | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
    | utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
    | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
    | cp866 | DOS Russian | cp866_general_ci | 1 |
    | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
    | macce | Mac Central European | macce_general_ci | 1 |
    | macroman | Mac West European | macroman_general_ci | 1 |
    | cp852 | DOS Central European | cp852_general_ci | 1 |
    | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
    | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
    | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
    | utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
    | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
    | cp1256 | Windows Arabic | cp1256_general_ci | 1 |
    | cp1257 | Windows Baltic | cp1257_general_ci | 1 |
    | utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
    | binary | Binary pseudo charset | binary | 1 |
    | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
    | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
    | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
    +———-+—————————–+———————+——–+
    40 rows in set (0.02 sec)

  • 翻看数据库引擎

    mysql> show engines;
    +——————–+———+————————————————
    —————-+————–+——+————+
    | Engine | Support | Comment

                | Transactions | XA   | Savepoints |
    

    +——————–+———+————————————————
    —————-+————–+——+————+
    | FEDERATED | NO | Federated MySQL storage engine

                | NULL         | NULL | NULL       |
    

    | MRG_MYISAM | YES | Collection of identical MyISAM tables

                | NO           | NO   | NO         |
    

    | MyISAM | YES | MyISAM storage engine

                | NO           | NO   | NO         |
    

    | BLACKHOLE | YES | /dev/null storage engine (anything you write to
    it disappears) | NO | NO | NO |
    | CSV | YES | CSV storage engine

                | NO           | NO   | NO         |
    

    | MEMORY | YES | Hash based, stored in memory, useful for tempor
    ary tables | NO | NO | NO |
    | ARCHIVE | YES | Archive storage engine

                | NO           | NO   | NO         |
    

    | InnoDB | DEFAULT | Supports transactions, row-level locking, and f
    oreign keys | YES | YES | YES |
    | PERFORMANCE_SCHEMA | YES | Performance Schema

                | NO           | NO   | NO         |
    

    +——————–+———+————————————————
    —————-+————–+——+————+
    9 rows in set (0.01 sec)

  • 修改数据库的字符集

    mysql> alter database db character set utf8;
    Query OK, 1 row affected (0.00 sec)

  • 安装连接服务器及当前客户端的字符集

    mysql> set names utf8;
    Query OK, 0 rows affected (0.00 sec)

  • 翻开当前劳动支撑的字符集

    mysql> show charset;
    +———-+—————————–+———————+——–+
    | Charset | Description | Default collation | Maxlen |
    +———-+—————————–+———————+——–+
    | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
    | dec8 | DEC West European | dec8_swedish_ci | 1 |
    | cp850 | DOS West European | cp850_general_ci | 1 |
    | hp8 | HP West European | hp8_english_ci | 1 |
    | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
    | latin1 | cp1252 West European | latin1_swedish_ci | 1 |
    | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
    | swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
    | ascii | US ASCII | ascii_general_ci | 1 |
    | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
    | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
    | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
    | tis620 | TIS620 Thai | tis620_thai_ci | 1 |
    | euckr | EUC-KR Korean | euckr_korean_ci | 2 |
    | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
    | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
    | greek | ISO 8859-7 Greek | greek_general_ci | 1 |
    | cp1250 | Windows Central European | cp1250_general_ci | 1 |
    | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
    | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
    | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
    | utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
    | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
    | cp866 | DOS Russian | cp866_general_ci | 1 |
    | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
    | macce | Mac Central European | macce_general_ci | 1 |
    | macroman | Mac West European | macroman_general_ci | 1 |
    | cp852 | DOS Central European | cp852_general_ci | 1 |
    | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
    | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
    | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
    | utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
    | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
    | cp1256 | Windows Arabic | cp1256_general_ci | 1 |
    | cp1257 | Windows Baltic | cp1257_general_ci | 1 |
    | utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
    | binary | Binary pseudo charset | binary | 1 |
    | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
    | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
    | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
    +———-+—————————–+———————+——–+
    40 rows in set (0.02 sec)

一. 字符串类型介绍

类型 说明 N的含义 是否有字符集 最大长度
CHAR(N) 定长字符 字符 255
VARCHAR(N) 变长字符 字符 16384
BINARY(N) 定长二进制字节 字节 255
VARBINARY(N) 变长二进制字节 字节 16384
TINYBLOB(N) 二进制大对象 字节 256
BLOB(N) 二进制大对象 字节 16K
MEDIUMBLOB(N) 二进制大对象 字节 16M
LONGBLOB(N) 二进制大对象 字节 4G
TINYTEXT(N) 大对象 字节 256
TEXT(N) 大对象 字节 16K
MEDIUMTEXT(N) 大对象 字节 16M
LONGTEXT(N) 大对象 字节 4G

   

   

  

二. N和字符集

  • char(N)

    • 若是当前table的字符集的最大长度W,
      char(N)的最大存款和储蓄空间为
      (N X W)Byte;即使使用UTF-8,则char(十)能够相当的小存款和储蓄13个字节的字符,最大存款和储蓄叁拾2个字节的字符,其实是另一种意义上的varchar
    • 当存款和储蓄的字符数小于N时,尾部使用空格填充,并且填充最小字节的空格

    mysql> create table t_char(a char(10));
    Query OK, 0 rows affected (0.01 sec)

    mysql> show create table t_char \G;
    *** 1. row ***

       Table: t_char
    

    Create Table: CREATE TABLE t_char (
    a char(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

    mysql> insert into t_char values(‘abc’);
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_char values(‘你好哦’);
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_char values(‘你好abc’);
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_char values(‘abc你好’);
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_char values(‘很不好abc’);
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_char values(‘很不abc好’);
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_char values(‘非凡倒霉abc’);
    Query OK, 一 row affected (0.00 sec)

    mysql> select a, length(a) from test_char;
    ERROR 1146 (42S02): Table ‘dbtype.test_char’ doesn’t exist
    mysql> select a, length(a) from t_char;
    +—————–+———–+
    | a | length(a) |
    +—————–+———–+
    | abc | 叁 |
    | 你好啊 | 九 |
    | 你好abc | 玖 |
    | abc你好 | 九 |
    | 很不佳abc | 1二 |
    | 很不abc好 | 1二 |
    | 十三分不佳abc | 一伍 |
    +—————–+———–+
    7 rows in set (0.00 sec)

    mysql> select a,hex(a) from t_char;
    +—————–+——————————–+
    | a | hex(a) |
    +—————–+——————————–+
    | abc | 616二陆叁 | — 注意那里,以及上面包车型客车16进制值,一会得以对照
    | 你好哦 | E四BDA0E五A五BDE5玖三A六 |
    | 你好abc | E四BDA0E伍A伍BD616二六三 |
    | abc你好 | 616263E四BDA0E5A伍BD |
    | 很不好abc | E五BE8捌E四B88DE5A5BD616贰陆三 |
    | 很不abc好 | E5BE88E四B8八D616二六三E五A5BD |
    | 格外不好abc | E99D九EE五B八B8E四B88DE5A伍BD616二6三 |
    +—————–+——————————–+
    柒 rows in set (0.00 sec)

    mysql> select hex(‘ ‘);
    +———-+
    | hex(‘ ‘) |
    +———-+
    | 20 | — 注意空格,空格对应的1六进制数字是 20
    +———-+
    1 row in set (0.00 sec)

t_char表实际2进制存款和储蓄文件

-- shell> hexdump -C t_char.idb

0000c070  73 75 70 72 65 6d 75 6d  0a 00 00 00 10 00 24 00  |supremum......$.|
0000c080  00 00 00 12 04 00 00 40  18 07 2c b8 00 01 df 9c  |.......@..,.....|
0000c090  01 10 61 62 63 20 20 20  20 20 20 20 0a 00 00 00  |..abc       ....| --abc为61 62 63 空格为20 后面补了7个空格也就是 '61 62 63 20 20 20  20 20 20 20'
0000c0a0  18 00 24 00 00 00 00 12  05 00 00 40 18 07 2d b9  |..$........@..-.|
0000c0b0  00 01 df 9d 01 10 e4 bd  a0 e5 a5 bd e5 93 a6 20  |............... | --你好哦表示  e4 bd a0 e5 a5 bd e5 93 a6 20 后面补了空格20
0000c0c0  0a 00 00 00 20 00 24 00  00 00 00 12 06 00 00 40  |.... .$........@|
0000c0d0  18 07 32 bc 00 01 df b0  01 10 e4 bd a0 e5 a5 bd  |..2.............| --你好abc表示 e4 bd a0 e5 a5 bd 61 62 63 20 后面补了空格20
0000c0e0  61 62 63 20 0a 00 00 00  28 00 24 00 00 00 00 12  |abc ....(.$.....|
0000c0f0  07 00 00 40 18 07 33 bd  00 01 df a1 01 10 61 62  |...@..3.......ab| --abc你好表示 61 62 63 e4 bd a0 e5 a5 bd 20 后面补了空格20
0000c100  63 e4 bd a0 e5 a5 bd 20  0c 00 00 00 30 00 26 00  |c...... ....0.&.|
0000c110  00 00 00 12 08 00 00 40  18 07 3f a7 00 01 df 97  |.......@..?.....| 
0000c120  01 10 e5 be 88 e4 b8 8d  e5 a5 bd 61 62 63 0c 00  |...........abc..| --很不好abc表示 e5 be 88 e4 b8 8d e5 a5 bd 61 62 63 没有空格补
0000c130  00 00 38 00 26 00 00 00  00 12 09 00 00 40 18 07  |..8.&........@..|
0000c140  40 a8 00 01 df 8d 01 10  e5 be 88 e4 b8 8d 61 62  |@.............ab| --很不abc好表示 e5 be 88 e4 b8 8d 61 62 63 e5 a5 bd 没有空格补
0000c150  63 e5 a5 bd 0f 00 00 00  40 ff 15 00 00 00 00 12  |c.......@.......|
0000c160  0a 00 00 40 18 07 4c b2  00 01 df 81 01 10 e9 9d  |...@..L.........|
0000c170  9e e5 b8 b8 e4 b8 8d e5  a5 bd 61 62 63 00 00 00  |..........abc...|
0000c180  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
  • varchar(N)

    mysql> create table tt_varchar(a varchar(10));
    Query OK, 0 rows affected (0.12 sec)

    mysql> show create table t_varchar \G;
    *** 1. row ***

       Table: t_varchar
    

    Create Table: CREATE TABLE t_varchar (
    a varchar(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

    mysql> insert into t_varchar values(‘abc’);
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_varchar values(‘abc你好’);
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_varchar values(‘很不好abc’);
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_varchar values(‘至极倒霉abc’);
    Query OK, 一 row affected (0.00 sec)

    mysql> select a, length(a) from t_varchar;
    +—————–+———–+
    | a | length(a) |
    +—————–+———–+
    | abc | 三 |
    | abc你好 | 九 |
    | 很糟糕abc | 1二 |
    | 十三分不佳abc | 一伍 |
    +—————–+———–+
    肆 rows in set (0.00 sec)

    mysql> select a,hex(a) from t_varchar;
    +—————–+——————————–+
    | a | hex(a) |
    +—————–+——————————–+
    | abc | 6162陆三 |
    | abc你好 | 616二陆叁E4BDA0E伍A5BD |
    | 很不佳abc | E五BE88E四B88DE伍A伍BD616二陆3 |
    | 相当不佳abc | E99D九EE五B8B8E四B8捌DE伍A伍BD616二六3 |
    +—————–+——————————–+
    4 rows in set (0.00 sec)

t_varchar表实际2进制存款和储蓄文件

-- shell> hexdump -C t_char.idb
-- 和char一样观察,都没有进行空格的填充

0000c070  73 75 70 72 65 6d 75 6d  03 00 00 00 10 00 1d 00  |supremum........|
0000c080  00 00 00 12 0f 00 00 40  18 08 17 fb 00 01 df 8a  |.......@........|
0000c090  01 10 61 62 63 09 00 00  00 18 00 23 00 00 00 00  |..abc......#....|
0000c0a0  12 10 00 00 40 18 08 18  fc 00 01 df b5 01 10 61  |....@..........a|
0000c0b0  62 63 e4 bd a0 e5 a5 bd  0c 00 00 00 20 00 26 00  |bc.......... .&.|
0000c0c0  00 00 00 12 11 00 00 40  18 08 1d ff 00 01 df 8b  |.......@........|
0000c0d0  01 10 e5 be 88 e4 b8 8d  e5 a5 bd 61 62 63 0f 00  |...........abc..|
0000c0e0  00 00 28 ff 8b 00 00 00  00 12 12 00 00 40 18 08  |..(..........@..|
0000c0f0  1e 80 00 01 df b6 01 10  e9 9d 9e e5 b8 b8 e4 b8  |................|
0000c100  8d e5 a5 bd 61 62 63 00  00 00 00 00 00 00 00 00  |....abc.........|
0000c110  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
  • 插入数据尾部带空格

    mysql> insert into test_char values(‘好好好 ‘); — 后面有3个空格
    Query OK, 1 row affected (0.03 sec)

    mysql> insert into test_varchar values(‘好好好 ‘); — 后面有3个空格
    Query OK, 1 row affected (0.02 sec)

    — t_char 表

    mysql> select a, length(a) from t_char;
    +—————–+———–+
    | a | length(a) |
    +—————–+———–+
    | abc | 三 |
    | 你好啊 | 九 |
    | 你好abc | 9 |
    | abc你好 | 玖 |
    | 很不好abc | 1二 |
    | 很不abc好 | 1二 |
    | 至极不佳abc | 1伍 |
    | 开黑中 | 玖 | –八个字节
    | 你好啊 | 玖 |
    +—————–+———–+
    玖 rows in set (0.00 sec)

    mysql> select a,hex(a) from t_char;
    +—————–+——————————–+
    | a | hex(a) |
    +—————–+——————————–+
    | abc | 616二6叁 |
    | 你好哦 | E4BDA0E伍A伍BDE5九三A6 |
    | 你好abc | E4BDA0E伍A5BD616贰63 |
    | abc你好 | 616二陆三E四BDA0E伍A5BD |
    | 很倒霉abc | E5BE8八E4B88DE伍A伍BD6162陆三 |
    | 很不abc好 | E伍BE8八E肆B8八D616二陆三E5A五BD |
    | 至极不佳abc | E9玖D9EE五B八B八E肆B8八DE五A五BD616二陆三 |
    | 开黑中 | E5BC80E玖BB九一E四B8AD |
    | 你好啊 | E四BDA0E5A五BDE5958A | –无填充
    +—————–+——————————–+
    玖 rows in set (0.00 sec)

    — t_varchar表

    mysql> insert into t_varchar values(‘你好啊 ‘);
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_varchar values(‘开黑中 ‘);
    Query OK, 1 row affected (0.00 sec)

    mysql> select a, length(a) from t_varchar;
    +—————–+———–+
    | a | length(a) |
    +—————–+———–+
    | abc | 三 |
    | abc你好 | 九 |
    | 很倒霉abc | 12 |
    | 万分不好abc | 一伍 |
    | 你好啊 | 1二 | — (你好啊)九个字节 + 二个字节的空格
    | 开黑中 | 12 |
    +—————–+———–+
    陆 rows in set (0.00 sec)

    mysql> select a,hex(a) from t_varchar;
    +—————–+——————————–+
    | a | hex(a) |
    +—————–+——————————–+
    | abc | 616二陆三 |
    | abc你好 | 61626三E四BDA0E伍A5BD |
    | 很不佳abc | E伍BE8八E四B8八DE5A5BD6162陆三 |
    | 万分倒霉abc | E9九D九EE伍B8B八E4B88DE五A五BD616二⑥3 |
    | 你好啊 | E四BDA0E伍A5BDE595八A202020 | — 前面有20 20 20 ,表示二个体协会调的空格
    | 开黑中 | E伍BC80E9BB九1E四B八AD202020 |
    +—————–+——————————–+
    6 rows in set (0.00 sec)

上边的场景不可能用联合的条条框框进行表述,可是法定文书档案付出的解释是,那样的配备是为着幸免索引页的零碎

  

3.BLOB和TEXT

  • 在BLOB和TEXT上开创索引时,必须钦命索引前缀的长短

    mysql> create table t_text(a int primary key, b text, key(b));
    ERROR 1170 (42000): BLOB/TEXT column ‘b’ used in key specification without a key length

    mysql> create table t_text(a int primary key, b text, key(b(64)));
    Query OK, 0 rows affected (0.00 sec)

  • BLOB和TEXT列不可能有私下认可值

  • BLOB和TEXT列排序时只使用该列的前max_sort_length个字节

    mysql> select @@max_sort_length;
    +——————-+
    | @@max_sort_length |
    +——————-+
    | 1024 |
    +——————-+
    1 row in set (0.00 sec)

不提议在MySQL中存储大型的贰进制数据,比如歌曲,摄像


 

四. 字符集

  

一. 大面积的字符集

  • utf8
  • utf8mb4
  • gbk
  • gb18030

    mysql> show character set;
    +———-+———————————+———————+——–+
    | Charset | Description | Default collation | Maxlen |
    +———-+———————————+———————+——–+
    | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
    | dec8 | DEC West European | dec8_swedish_ci | 1 |
    | cp850 | DOS West European | cp850_general_ci | 1 |
    | hp8 | HP West European | hp8_english_ci | 1 |
    | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
    | latin1 | cp1252 West European | latin1_swedish_ci | 1 |
    | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
    | swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
    | ascii | US ASCII | ascii_general_ci | 1 |
    | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
    | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
    | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
    | tis620 | TIS620 Thai | tis620_thai_ci | 1 |
    | euckr | EUC-KR Korean | euckr_korean_ci | 2 |
    | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
    | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
    | greek | ISO 8859-7 Greek | greek_general_ci | 1 |
    | cp1250 | Windows Central European | cp1250_general_ci | 1 |
    | gbk | GBK Simplified Chinese | gbk_chinese_ci | 二 | — gbk,表示的字符有限
    | latin5 | ISO 885九-玖 Turkish | latin伍_turkish_ci | 1 |
    | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
    | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | — utf8,最长3字节
    | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
    | cp866 | DOS Russian | cp866_general_ci | 1 |
    | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
    | macce | Mac Central European | macce_general_ci | 1 |
    | macroman | Mac West European | macroman_general_ci | 1 |
    | cp852 | DOS Central European | cp852_general_ci | 1 |
    | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
    | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | — utf8 + mobile端字符
    | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
    | utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
    | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
    | cp1256 | Windows Arabic | cp1256_general_ci | 1 |
    | cp1257 | Windows Baltic | cp1257_general_ci | 1 |
    | utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
    | binary | Binary pseudo charset | binary | 1 |
    | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
    | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
    | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
    | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | — gb18030,最长4个字节
    +———-+———————————+———————+——–+
    41 rows in set (0.00 sec)

 

2. collation

collation的意思是指排序规则,ci(case insensitive)提及底的排序集是不区分轻重缓急写

mysql> select 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         1 |  -- 因为大小写无关,所以返回1
+-----------+
1 row in set (0.00 sec)

mysql> create table t_ci (a varchar(10), key(a));
Query OK, 0 rows affected (0.13 sec)

mysql> insert into t_ci values('a');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t_ci values('A');
Query OK, 1 row affected (0.03 sec)

mysql> select * from t_ci where a = 'a';
+------+
| a    |
+------+
| a    |  
| A    |  -- A也被我们查到了
+------+
2 rows in set (0.00 sec)
mysql> select * from t_ci where a ='A';
+------+
| a    |
+------+
| a    |
| A    |
+------+
2 rows in set (0.00 sec)

mysql> select 'a' ='a   ';     --插入5个空格也会被查询到,这是个坑
+-------------+
| 'a' ='a   ' |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select 'a' ='A   ';
+-------------+
| 'a' ='A   ' |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

地方的情事只要从业务的角度上看,能够很好精通,比如创设叁个用户叫做GAVIN,你是不指望再次创下立一个叫做gavin的用户

  • 修改暗中同意的collation

    mysql> set names utf8mb4 collate utf8mb4_bin; — 当前对话有效
    Query OK, 0 rows affected (0.00 sec)

    mysql> select ‘a’ = ‘A’;
    +———–+
    | ‘a’ = ‘A’ |
    +———–+
    | 0 |
    +———–+
    1 row in set (0.00 sec)

字符集的钦命,能够在成立数据库的时候钦点,也能够在创造表的时候单独内定,也得以成立列的时候进行点名


伍. 集合类型

  • 聚拢类型ENUM 和 SET
  • ENUM类型最多允许65伍四十三个值
  • SET类型最多允许陆十五个值
  • 通过sql_mode参数能够用户约束检查

一. 集合类型的排序

mysql> create table t_col (user varchar(10),sex enum('male', 'female'));   -- 虽然写的是字符串,单其实存储的整型,效率还是可以的

mysql> insert into t_col values("fanghao","male");
Query OK, 1 row affected (0.02 sec)

mysql> insert into t_col values("xuliuyan","male");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_col values("yanglaoshi","female");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_col values("banlaoshi","male");   --正常插入
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_col;
+------------+--------+
| user       | sex    |
+------------+--------+
| fanghao    | male   |
| xuliuyan   | male   |
| yanglaoshi | female |   
| banlaoshi  | male   |
+------------+--------+     
4 rows in set (0.00 sec)


mysql>  set sql_mode='';    -- 设置为sql_mode 空模式
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_col values("haozong","xmale");  --可以插入非法sql
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>  set sql_mode='strict_trans_tables';   -- 设置为严格模式
Query OK, 0 rows affected, 1 warning (0.00 sec) 

mysql> insert into t_col values("xiaoxiaohao","xmale");  --插入报错
ERROR 1406 (22001): Data too long for column 'user' at row 1

强烈提议新工作上都设置成严俊方式

二. 集聚类型的排序

mysql> create table t_col_sort(user char(10),type enum('aaa','zzz','bbb','yyy','fff'));  -- aaa=0, zzz=1, bbb=2, yyy=3, fff=4
Query OK, 0 rows affected (0.20 sec)

mysql> insert into t_col_sort values("user1","aaa");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_col_sort values("user2","bbb");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_col_sort values("user3","yyy");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_col_sort values("user4","zzz");
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_col_sort order by type asc; -- 以type作为key,进行升序排序
+-------+------+
| user  | type |
+-------+------+
| user1 | aaa  |  -- 0
| user4 | zzz  |  -- 1
| user2 | bbb  |  -- 2
| user3 | yyy  |  -- 3
+-------+------+  
4 rows in set (0.00 sec)

-- 枚举类型实际是整型数据,按照插入顺序进行排列

mysql>  select * from t_col_sort;
+-------+------+
| user  | type |
+-------+------+
| user1 | aaa  |
| user2 | bbb  |
| user3 | yyy  |
| user4 | zzz  |
+-------+------+
4 rows in set (0.00 sec)

--
-- 使用ascii排序
--
mysql> select * from t_col_sort order by cast(type as char) asc;  -- 使用cast()函数转换成某种型
+-------+------+                                                     -- 这里我们转成char型
| user  | type |                                                     -- 然后进行排序(ascii)
+-------+------+
| user1 | aaa  | -- 0
| user2 | bbb  | -- 2
| user3 | yyy  | -- 3
| user4 | zzz  | -- 1
+-------+------+
4 rows in set (0.00 sec)

-- 或者使用concat

mysql> select * from t_col_sort order by concat(type) asc;   -- concat()是连接字符串函数            
+-------+------+
| user  | type |
+-------+------+
| user1 | aaa  |  -- 0
| user2 | bbb  |  -- 2
| user3 | yyy  |  -- 3
| user4 | zzz  |  -- 1
+-------+------+
4 rows in set (0.00 sec)

mysql> select concat("abc", "大家好");
+----------------------------+
| concat("abc", "大家好")    |
+----------------------------+
| abc大家好                  |
+----------------------------+
1 row in set (0.00 sec)

陆. 日期类型

日期类型 占用空间(byte)(<5.6) 占用空间(byte)(>=5.6) 表示范围
DATETIME 8 5 + 微秒存储空间 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
DATE 3 3 1000-01-01 ~ 9999-12-31
TIMESTAMP 4 4 + 微秒存储空间 1970-01-01 00:00:00UTC ~ 2038-01-19 03:14:07UTC
YEAR 1 1 YEAR(2):1970-2070, YEAR(4):1901-2155
TIME 3 3 + 微秒存储空间 -838:59:59 ~ 838:59:59
微秒位数 所需存储空间
0 0
1, 2 1 byte
3, 4 2 bytes
5, 6 3 bytes

TIMESTAMP 带时区功能

1. TIMESTAMP和DATETIME

mysql> create table t_time(a timestamp, b datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_time values(now(),now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_time;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2017-11-29 10:48:04 | 2017-11-29 10:48:04 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM      |
+-------------+
1 row in set (0.00 sec)

mysql> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_time;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2017-11-29 02:48:04 | 2017-11-29 10:48:04 |  -- 相差8个小时,时区的差别体现出来了
+---------------------+---------------------+
1 row in set (0.00 sec)

2. 微秒

MySQL5.6.X开始,支持微秒,最大突显6位

mysql>  select now(6);
+----------------------------+
| now(6)                     |
+----------------------------+
| 2017-11-29 02:53:29.019376 |  -- (019376) 6位微秒显示
+----------------------------+
1 row in set (0.00 sec)

mysql> select now(7);
ERROR 1426 (42000): Too-big precision 7 specified for 'now'. Maximum is 6.  -- 不支持,最大到6位

mysql> create table t_time_fac (t datetime(6));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_time_fac values(now(6)); 
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_time_fac;
+----------------------------+
| t                          |
+----------------------------+
| 2017-11-29 02:59:03.945431 |  -- 由于是用了6位微秒位数,根据表格显示,实际存储的空间是 5 + 3 = 8 byte
+----------------------------+
1 row in set (0.00 sec)

三. 年华函数

  • 常用函数
函数名 函数说明 备注
NOW 返回SQL执行时的时间 如果不考虑其他因素,可以理解为写完SQL,敲下回车瞬间的时间
CURRENT_TIMESTAMP 与NOW()函数同义
SYSDATE 返回函数执行时的时间 MySQL处理你的函数时的时间,统一SQL语句中,大于NOW
DATA_ADD(date, interval expr uint) 增加时间
DATA_SUB(date, interval expr uint) 减少时间 可用ADD,然后unit给负数
DATE FORMAT 格式化时间

全体时间函数–官方文书档案

--
-- NOW和SYSDATE的区别
--
mysql> select now(),sysdate();
+---------------------+---------------------+
| now()               | sysdate()           |
+---------------------+---------------------+
| 2017-11-29 11:04:58 | 2017-11-29 11:04:58 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select now(),sysdate(),sleep(2),now(),sysdate();
+---------------------+---------------------+----------+---------------------+---------------------+
| now()               | sysdate()           | sleep(2) | now()               | sysdate()           |
+---------------------+---------------------+----------+---------------------+---------------------+
| 2017-11-29 11:05:49 | 2017-11-29 11:05:49 |        0 | 2017-11-29 11:05:49 | 2017-11-29 11:05:51 |
+---------------------+---------------------+----------+---------------------+---------------------+
1 row in set (2.00 sec)

mysql> select  now(6),sysdate(6);
+----------------------------+----------------------------+
| now(6)                     | sysdate(6)                 |
+----------------------------+----------------------------+
| 2017-11-29 11:07:44.038222 | 2017-11-29 11:07:44.038360 |
+----------------------------+----------------------------+
1 row in set (0.00 sec)


--
-- 两个now(6)都相等,因为是SQL执行时的时间(可以简单立理解为按回车的时间)
-- 两个sysdate(6)差了5秒,刚好是sleep(5)的时间
--

-----

--
-- date_add
--

mysql> select date_add(now(),interval 7 day);    -- 增加7天
+--------------------------------+
| date_add(now(),interval 7 day) |
+--------------------------------+
| 2017-12-06 11:09:28            |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval 1 month);  --增量1个月
+----------------------------------+
| date_add(now(),interval 1 month) |
+----------------------------------+
| 2017-12-29 11:10:06              |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval -2 month);  --减少2个月
+-----------------------------------+
| date_add(now(),interval -2 month) |
+-----------------------------------+
| 2017-09-29 11:10:18               |
+-----------------------------------+
1 row in set (0.00 sec)


--
-- date_format
--
mysql> SELECT DATE_FORMAT((select now(6)), '%H:%i:%s');
+------------------------------------------+
| DATE_FORMAT((select now(6)), '%H:%i:%s') |
+------------------------------------------+
| 11:11:20                               |
+------------------------------------------+
1 row in set (0.00 sec)

4. 字段更新时间

mysql> create  table t_field_update(a int(32), b timestamp not null default current_timestamp on update current_timestamp);

mysql> insert into t_field_update values(1, now(6));
Query OK, 1 row affected (0.03 sec)

mysql>select * from t_field_update;
+------+---------------------+
| a    | b                   |
+------+---------------------+
|    1 | 2017-11-29 11:17:48 |  -- 上面使用了now(6),但是这里没有微秒,是因为定义的时候就是timestamp
+------+---------------------+  -- 如果写成timestamp(6),就可以显示微秒
1 row in set (0.00 sec)

mysql> update t_field_update set a=10 where a=1;  -- 只更新a字段
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_field_update;    
+------+---------------------+
| a    | b                   |
+------+---------------------+
|   10 | 2017-11-29 11:18:39 |  -- 发现b字段跟着改变了
+------+---------------------+
1 row in set (0.00 sec)

--
-- 测试timestamp(6)
--
mysql> create table t_time_disp(a int(10),b timestamp(6) not null default current_timestamp(6) on update current_timestamp(6));   -- 定义了微妙

mysql> insert into t_time_disp values(1, now(6));      
Query OK, 1 row affected (0.02 sec)

mysql> select * from t_time_disp;
+------+----------------------------+
| a    | b                          |
+------+----------------------------+
|    1 | 2017-11-29 11:19:53.377309 |    -- 插入了now(6), 这里就显示了6位微秒
+------+----------------------------+
1 row in set (0.00 sec)

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图