澳门金沙vip 4

五、MySQL常用功能实践

运行软件后,点NEXT进入以下界面:

3.2.Number类型

澳门金沙vip 1

图片来自网络

  • FLOAT:单精度类型
    存储空间:4 字节
    精确性:低

  • DOUBLE:双精度类型
    占用空间:8 字节
    精确性:低,比FLOAT高

  • DECIMAL:高精度类型
    占用空间:变长
    精确性:非常高

mysql> create table t_numbers (c1 float(9,3), c2 DOUBLE(9,3), c3 DECIMAL(9,3));
Query OK, 0 rows affected (0.01 sec)

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

mysql> select * from t_numbers;
+------------+------------+------------+
| c1         | c2         | c3         |
+------------+------------+------------+
| 123456.797 | 123456.800 | 123456.800 |
+------------+------------+------------+
1 row in set (0.00 sec)


mysql> insert into t_numbers values (999.0009,999.0009,999.0009);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t_numbers;
+------------+------------+------------+
| c1         | c2         | c3         |
+------------+------------+------------+
| 123456.797 | 123456.800 | 123456.800 |
|    999.001 |    999.001 |    999.001 |
+------------+------------+------------+
2 rows in set (0.00 sec)

mysql> insert into t_numbers values (1234567.89,1234567.89,1234567.89);
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'c1' at row 1 |
| Warning | 1264 | Out of range value for column 'c2' at row 1 |
| Warning | 1264 | Out of range value for column 'c3' at row 1 |
+---------+------+---------------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from t_numbers;
+-------------+------------+------------+
| c1          | c2         | c3         |
+-------------+------------+------------+
|  123456.797 | 123456.800 | 123456.800 |
|     999.001 |    999.001 |    999.001 |
| 1000000.000 | 999999.999 | 999999.999 |
+-------------+------------+------------+
3 rows in set (0.00 sec)

继续NEXT,如图所示进入此界面,此处设定字符编码,非常关键。

3.4.1.ENUM类型
mysql> create table employees (user char(10), kpi_level enum('A','2A','B','C'));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into employees values ('Tom','A');
Query OK, 1 row affected (0.00 sec)

mysql> insert into employees values ('Jack','B');
Query OK, 1 row affected (0.00 sec)

mysql> insert into employees values ('Eirc','2A');
Query OK, 1 row affected (0.00 sec)

mysql> insert into employees values ('bug','D');
ERROR 1265 (01000): Data truncated for column 'kpi_level' at row 1

mysql> #排序测试
mysql> select * from employees order by kpi_level ; 
+------+-----------+
| user | kpi_level |
+------+-----------+
| Tom  | A         |
| Eirc | 2A        |
| Jack | B         |
+------+-----------+
3 rows in set (0.01 sec)

mysql> 使用ascii排序
mysql> select * from employees order by cast(kpi_level as char) asc;
+------+-----------+
| user | kpi_level |
+------+-----------+
| Eirc | 2A        |
| Tom  | A         |
| Jack | B         |
+------+-----------+

选择MS SQL SERVER,按提示填写,Connection String为:

3.5.4.时间更新(5.6新特性)

TIMESTAMP在MySQL5.5中的行为

  • 第一个未设置默认值的TIMESTAMP NOT NULL字段隐式默认值:
    CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  • 后面未设置默认值的TIMESTAMP NOT NULL字段隐式默认值:0000-00-00
    00:00:00
    TIMESTAMP NOT NULL字段插入NULL时,会使用隐式默认值:
    CURRENT_TIMESTAMP
  • 不支持多个CURRENT_TIMESTAMP 默认值

TIMESTAMP在MySQL5.6中的行为

  • 支持多个CURRENT_TIMESTAMP 默认值
  • 可以兼容5.5的行为,支持隐性默认值
    可以去掉隐性默认值:explicit_defaults_for_timestamp=1
    如果不加,5.6默认启动时,服务器会给出一个警告。
    [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
    Please use –explicit_defaults_for_timestamp server option (see
    documentation for more details).

mysql> create table t_timestamp (t1 timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,t2 timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t_timestamp \G
*************************** 1. row ***************************
       Table: t_timestamp
Create Table: CREATE TABLE `t_timestamp` (
  `t1` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `t2` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

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

mysql> select * from t_timestamp;
+---------------------+---------------------+
| t1                  | t2                  |
+---------------------+---------------------+
| 2017-08-13 04:53:30 | 2017-08-13 04:53:30 |
+---------------------+---------------------+
1 row in set (0.00 sec)
  • 时间范围示例

创建一张表,出现以下错误:ERROR 1067 (42000): Invalid default value for 'M_CONFIRM_TIME'

mysql> CREATE TABLE `SETTLE_RECV` (
    ->   `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    ->   `CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间',
    ->   `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
    ->   `M_CONFIRM_TIME` timestamp NOT NULL DEFAULT '1970-01-01 01:00:00' COMMENT '商户确认时间',
    ->   `VERIFY_TIME` timestamp NOT NULL DEFAULT '1970-01-01 01:00:00' COMMENT '销账时间',
    ->   `SETTLE_STATUS` int(11) NOT NULL DEFAULT '0' COMMENT '结算单状态',
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB CHARSET=utf8 COMMENT='xxx详情表';
ERROR 1067 (42000): Invalid default value for 'M_CONFIRM_TIME'

问题分析:为什么会提示无效的默认值呢,尝试改一成'0000-00-00 00:00:00'试试。
问题虽然能够得到解决,但是此方法会造成应用程序错误,如:JAVA支持最早的时间是:'1970-01-01 00:00:00'
为什么几乎所有的语言最早支持的时间是:'1970-01-01 00:00:00'呢?
1970年正式UNIX诞生;所以计算机时间也就是从那一年开始的;

mysql> CREATE TABLE `SETTLE_RECV` (
    ->   `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    ->   `CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间',
    ->   `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
    ->   `M_CONFIRM_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '商户确认时间',
    ->   `VERIFY_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '销账时间',
    ->   `SETTLE_STATUS` int(11) NOT NULL DEFAULT '0' COMMENT '结算单状态',
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB CHARSET=utf8 COMMENT='xxx详情表';
Query OK, 0 rows affected (0.01 sec)

mysql> insert into SETTLE_RECV (SETTLE_STATUS) values ('1');
Query OK, 1 row affected (0.01 sec)

mysql> select * from SETTLE_RECV;
+----+---------------------+---------------------+---------------------+---------------------+---------------+
| ID | CREATE_TIME         | UPDATE_TIME         | M_CONFIRM_TIME      | VERIFY_TIME         | SETTLE_STATUS |
+----+---------------------+---------------------+---------------------+---------------------+---------------+
|  1 | 2017-08-30 10:28:22 | 2017-08-30 10:28:22 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |             1 |
+----+---------------------+---------------------+---------------------+---------------------+---------------+
1 row in set (0.00 sec)

问题解决:MySQL的timestamp范围是UTC的’1970-01-01
00:00:01’开始,为什么MySQL还创建异常呢?MySQL BUG? No No
No,仔细的同学看到了是UTC时间,所以我们要加+时区的position,完美解决。

mysql> show global variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> system date -R
Wed, 30 Aug 2017 10:40:26 +0800

mysql> CREATE TABLE `SETTLE_RECV` (
    ->   `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    ->   `CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间',
    ->   `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
    ->   `M_CONFIRM_TIME` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01' COMMENT '商户确认时间',
    ->   `VERIFY_TIME` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01' COMMENT '销账时间',
    ->   `SETTLE_STATUS` int(11) NOT NULL DEFAULT '0' COMMENT '结算单状态',
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB CHARSET=utf8 COMMENT='xxx详情表';
Query OK, 0 rows affected (0.02 sec)

或者修改数据库时区

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

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

mysql> CREATE TABLE `SETTLE_RECV` (
    ->   `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    ->   `CREATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '生成时间',
    ->   `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
    ->   `M_CONFIRM_TIME` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01' COMMENT '商户确认时间',
    ->   `VERIFY_TIME` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01' COMMENT '销账时间',
    ->   `SETTLE_STATUS` int(11) NOT NULL DEFAULT '0' COMMENT '结算单状态',
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB CHARSET=utf8 COMMENT='xxx详情表';
Query OK, 0 rows affected (0.00 sec)
jdbc:jtds:sqlserver://IP地址:端口(默认1433)/数据库教程名;user=用户名;password=密码;charset=gb2312;domain=
3.1.2.Integer类型特性
  • int(n)和zerofill
    int(n):
    n=任何数,n仅代表显示宽度,不表示存储的数字的长度的上限。
    zerofill:表示当存储的数字长度 <
    N时,用数字0填充左边,直至补满长度N;当存储数字的长度超过N时,按照实际存储的数字显示。
    注意:int(n)中的N和zerofill配合才有意义,且仅仅是显示的时候才有意义,和实际存储没有关系,不会去截取数字的长度。

mysql> create  table int_n (a int(3) zerofill);
Query OK, 0 rows affected (0.03 sec)

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

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

mysql> select * from int_n;
+------+
| a    |
+------+
|  002 |
| 2017 |
+------+
2 rows in set (0.00 sec)

mysql> select CAST(0x7E1 AS UNSIGNED);
+-------------------------+
| CAST(0x7E1 AS UNSIGNED) |
+-------------------------+
|                    2017 |
+-------------------------+
  • auto_increment(自动增长)
    auto_increment属性必须和唯一索引一起才能使用,用于约束写入的数必须没有重复性。
    一张表中只能有一个auto_increment属性,并且auto_increment作用域仅仅是表。
    Innodb
    engine的表中,最好每一个表都带有一个auto_increment属性,用于防止PAGE分裂。

mysql> create table t_auto_incrememnt (a int auto_increment);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> create table t_auto_increment (a int auto_increment, unique key(a));
Query OK, 0 rows affected (0.03 sec)
mysql> create table t_auto_increment (a int auto_increment,b int auto_increment ,unique key(a), primary key(b));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

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

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

mysql> insert into t_auto_increment values(-1);
Query OK, 1 row affected (0.04 sec)

mysql> select * from t_auto_increment;
+----+
| a  |
+----+
| -1 |
|  1 |
|  2 |
+----+

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

mysql> select * from t_auto_increment;
+----+
| a  |
+----+
| -1 |
|  1 |
|  2 |
|  3 |
+----+
4 rows in set (0.00 sec)

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

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

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

mysql> select * from t_auto_increment;
+----+
| a  |
+----+
| -1 |
|  1 |
|  2 |
|  3 |
| 10 |
| 11 |
+----+
6 rows in set (0.01 sec)
  • unsigned or signed(有字符或无字符)
    unsigned和signed最大的区别一个是int范围不一致,还有个是存在正负数区别;既然允许正负数那么在运算时就需要注意。

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

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

mysql> select a-b from t_unsigned;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test_db`.`t_unsigned`.`a` - `test_db`.`t_unsigned`.`b`)

mysql> set session sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t_unsigned values (-10,-10);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t_unsigned;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    0 |  -10 |
+------+------+
2 rows in set (0.00 sec)

mysql> set sql_mode = 'no_unsigned_subtraction';
mysql> select * from t_unsigned;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    0 |  -10 |
+------+------+
2 rows in set (0.00 sec)
jdbc:mysql://IP地址:端口(默认3306)/?user=用户名&password=密码&useServerPrepStmts=false&characterEncoding=UTF-8

3.5.日期类型

下一步为MySQL的信息,Connection String为:

3.1.1.Integer类型分类

澳门金沙vip 2

图片来自MySQL官档

之后一路按提示进行。到此界面选择要忽略的表。

2.2.MySQL字符集特性

澳门金沙vip 3

图片来自原创

  • MySQL字符集范围

服务器层(server) > 数据库成(database) > 数据表(table) >
字段(column) > 连接(connection) | 结果集(result)

  • MySQL字符集优先级

连接(connection) | 结果集(result) > 字段(column) > 数据表(table)
> 数据库成(database) > 服务器层(server)

  • MySQL字符集继承关系

server:server > database > tables > column
client:connection > result

  • character_set_system是什么鬼?

The character set used by the server for storing identifiers. The value
is always utf8.

mysql> show global variables like '%char%';
+--------------------------+-------------------------------------+
| Variable_name            | Value                               |
+--------------------------+-------------------------------------+
| character_set_client     | utf8                                |
| character_set_connection | utf8                                |
| character_set_database   | utf8                                |
| character_set_filesystem | binary                              |
| character_set_results    | utf8                                |
| character_set_server     | utf8                                |
| character_set_system     | utf8                                |
| character_sets_dir       | /usr/share/percona-server/charsets/ |
+--------------------------+-------------------------------------+

mssql 导入 mysql 不乱码 MySQLMigrationTool MySQL GUI Tools 5.0 包含了
MySQLMigrationTool 这个工具可以完成我们的任务 此工具需要JRE update 8
以上的支持,可以去sun.com下载,速度很快。 _CN/download/manual.jsp
有的同学运行后导入mysql的结果是中文乱码 ,
那是由于在一路next的中间有一个 选择编码的步骤, 此处务必选 user defined
,填上 charset=gbk,coll…tion的等于 gbk_chinese_ci ,
就是把latin,和swidish的修改下,改成中文相应的。总之这里要看好
否则乱码就怪不了别人了。 然后一路next就是了。。。。

2.4.MySQL校验规则

  • 刚才我们看到支持字符集后面有一个default
    collation这是什么呢?我们先做一个实验

mysql> create table t_collation (emp_name varchar(10));
Query OK, 0 rows affected (0.01 sec)

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

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

默认MySQL查询是也是不区分大小写,而MySQL返回的值确实比较宽泛。这就是默认校验规则是不区分大小写造成的

mysql> select * from t_collation where emp_name = 'tom';
+----------+
| emp_name |
+----------+
| Tom      |
| tom      |
+----------+
2 rows in set (0.00 sec)

修改校验规则

mysql> show global variables like '%collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql> alter table t_collation change emp_name emp_name varchar(10) DEFAULT NULL collate utf8mb4_bin;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_collation where emp_name = 'tom';
+----------+
| emp_name |
+----------+
| tom      |
+----------+
1 row in set (0.00 sec)

mysql> #或者
mysql> select * from t_collation where emp_name = ('tom' collate utf8_bin); 

临时生效

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

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

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

使用软件:MySQLMigrationTool
提示数据过大,无法导入。修改my.cnf文件的max_allowed_packet =
100M以上。在windows操作系统中,my.cnf有可能显示不出来,需要用编辑器直接输入地址:盘符:\目录mysql教程binmy.cnf打开。
“there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in
DEFAULT or ON UPDATE
clause”,MSSQL一个表中有两个以上的日期时间字段使用了getdate()取默认值,去掉即可。
“BLOB/TEXT column ‘表名’ can’t have a default
value”,此字段不允许使用默认值,去掉即可。 “The maximum row size for the
used table type, not counting BLOBs, is 65535. You have to change some
columns to TEXT or
BLOBs”,此表字段占用空间太大,适当减少其字段数量或长度。

2.MySQL字符集

gbk/gb2312
  • 采用双字节字符集,不论中、英文字符均使用双字符来表示,为了区分中文,将其最高位都设定成1
  • gb2312是gbk的子集,gbk是gb18030的子集,gb2312仅能存储简体中文字符
  • gbk包括中日韩字符的大字符集
  • 通常使用bgk字符集足够
  • 国际通用性比utf8差,不过utf8占用的数据库比gbk大(utf8是三字节字符集)

4.2.权限介绍

4.4.proxy user

MySQL 5.7以后也有Role的概念,即为多个数据库用户授权一个Role,5.7称之为
Proxy user

mysql.proxies_priv仅仅是对Role的模拟,还没有Oracle那么完善。

mysql> create user dba@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> grant super on *.* to dba@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create user john@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> create user eric@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'dba'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant proxy on dba@'localhost' to john@'localhost';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show grants for john@'localhost';
+--------------------------------------------------------+
| Grants for john@localhost                              |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'john'@'localhost'               |
| GRANT PROXY ON 'dba'@'localhost' TO 'john'@'localhost' |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

1.字符集

2.6.1.查看字符集设置

操作系统字符集:

# locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"

系统支持所有的字符集

# locale -a
aa_DJ
aa_DJ.iso88591
aa_DJ.utf8
aa_ER
...... 省略 ......

修改字符集

# cat /etc/sysconfig/i18n 
LANG="en_US.UTF-8"
SYSFONT="latarcyrheb-sun16"

# export LANG="en_US.UTF-8"

数据库字符集:

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.6.30-76.3, for Linux (x86_64) using  6.0

Connection id:      13
Current database:   
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.6.30-76.3-log Percona Server (GPL), Release 76.3, Revision 3850db5
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /var/wd/db10059/my10059.sock
Uptime:         55 min 28 sec

Threads: 1  Questions: 87  Slow queries: 0  Opens: 70  Flush tables: 1  Open tables: 63  Queries per second avg: 0.026
--------------

mysql> show global variables like '%char%';
+--------------------------+-------------------------------------+
| Variable_name            | Value                               |
+--------------------------+-------------------------------------+
| character_set_client     | utf8                                |
| character_set_connection | utf8                                |
| character_set_database   | utf8                                |
| character_set_filesystem | binary                              |
| character_set_results    | utf8                                |
| character_set_server     | utf8                                |
| character_set_system     | utf8                                |
| character_sets_dir       | /usr/share/percona-server/charsets/ |
+--------------------------+-------------------------------------+
8 rows in set (0.01 sec)

修改数据库字符集
临时生效

mysql> set character_set = 'gbk';
mysql> set character_set_client = 'gbk';

全局生效

mysql> set global character_set_client = 'gbk';
Query OK, 0 rows affected (0.00 sec)

永久生效

# vim /etc/my.cnf 
character-set-server=utf8

客户端字符集:
客户端可以是MySQL终端、也可以是program,我们分别以xshell和python语言为例:

programs

#!/bin/env python2.7
# _*_coding:utf-8 _*_
def get_otter_delay(*args):
    try:
        conn = MySQLdb.connect(host=args[0], port=args[1], db=args[2], user=args[3], passwd=args[4], charset='utf8')
        cur = conn.cursor()
        sql = 'select DELAY_TIME from otter_mgr.DELAY_STAT where GMT_CREATE = (select max(GMT_CREATE) from otter_mgr.DELAY_STAT)'
        cur.execute(sql)
        result_info = cur.fetchone()
        return result_info
        cur.close()
        conn.close()
    except Exception as e:
        print e

xshell

Encoding --> Unocode(utf-8)

3.1.Integer类型

3.4.集合类型

  • 集合类型ENUM 和 SET
  • ENUM类型最多允许65536个值
  • SET类型最多允许64个值
  • 通过sql_mode参数可以用户约束检查
latin1
  • 是8bit (1 bytes)字符集,但不能覆盖亚洲、非洲语言
  • unidoce是latin1的扩展,增加了亚洲、非洲常规语言支持,但仍不
    支持全部语言,且ASCII用unidoce来表示效率不高(小字符集转换成大字符集,往往便随的就是字符的丢失)
  • utf8是unicode的扩展
  • gbk、gb2312等字符集与utf8之间都必须通过Unicode编码才能相互转换

讨论:char(30),在不同的字符集中最多能存放多少个字母、汉字,以及占用空间情况

文/Bruce.Liu1

3.3.3.varcahr类型

  • varchar(N):N代表的是字符个数澳门金沙vip,(也叫字符长度)、而非bytes
  • varchar(N):是变长存储,仅使用必要的存储空间.
  • 存储空间:varchar(N)类型的存储空间和字符集有关系,结合刚才字符集的知识点,一个中文在utf8字符集中占用3个bytes、gbk统一占用2个bytes、数字和字符一个字符表示。
  • 存储机制:varchar(N)字段存储实际是从第二个字节开始存储,然后用1到2个字节表示实际长度,剩下的才是可以存储数据的范围,因此最大可用存储范围是65535-3=65532字节;第一个字节标识是否为空.(长度小于255字节,使用一个字节来表示长度;大于255字节使用两个字节来表示长度)

mysql> create table t_varchar (c1 varchar(10));
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into t_varchar values ('2数据库');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_varchar values ('mysql数据库  ');
Query OK, 1 row affected (0.00 sec)

mysql> select c1,hex(c1) from t_varchar;
+------------------+----------------------------------+
| c1               | hex(c1)                          |
+------------------+----------------------------------+
| 123              | 313233                           |
| 2数据库          | 32E695B0E68DAEE5BA93             |
| mysql数据库      | 6D7973716CE695B0E68DAEE5BA932020 |        <--20是十六进制表的空格
+------------------+----------------------------------+
3 rows in set (0.00 sec)

mysql> select concat(c1,"+") from t_varchar;
+-------------------+
| concat(c1,"+")    |
+-------------------+
| 123+              |
| 2数据库+          |
| mysql数据库  +    |
+-------------------+
3 rows in set (0.00 sec)

mysql> select hex(' ') from dual;
+----------+
| hex(' ') |
+----------+
| 20       |
+----------+
1 row in set (0.00 sec)

# hexdump -C t_varchar.ibd 
00000000  8f 14 14 4c 00 00 00 00  00 00 00 00 00 00 00 00  |...L............|
00000010  00 00 00 00 00 1c 23 72  00 08 00 00 00 00 00 00  |......#r........|
00000020  00 00 00 00 00 17 00 00  00 17 00 00 00 00 00 00  |................|
00000030  00 06 00 00 00 40 00 00  00 00 00 00 00 04 00 00  |.....@..........|
00000040  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 00 00  |................|
00000050  00 01 00 00 00 00 00 9e  00 00 00 00 00 9e 00 00  |................|
00000060  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 00 00  |................|
00000070  00 00 00 00 00 03 00 00  00 00 ff ff ff ff 00 00  |................|
00000080  ff ff ff ff 00 00 00 00  00 01 00 00 00 02 00 26  |...............&|
00000090  00 00 00 02 00 26 00 00  00 00 00 00 00 00 ff ff  |.....&..........|
000000a0  ff ff 00 00 ff ff ff ff  00 00 00 00 00 02 aa ff  |................|
000000b0  ff ff ff ff ff ff ff ff  ff ff ff ff ff ff 00 00  |................|
000000c0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00003ff0  00 00 00 00 00 00 00 00  b4 c7 1d c0 00 1c 23 72  |..............#r|
00004000  78 3a 46 36 00 00 00 01  00 00 00 00 00 00 00 00  |x:F6............|
00004010  00 00 00 00 00 1c 17 fb  00 05 00 00 00 00 00 00  |................|
00004020  00 00 00 00 00 17 00 00  00 00 00 00 00 00 00 00  |................|
00004030  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00007ff0  00 00 00 00 00 00 00 00  78 83 2a 58 00 1c 17 fb  |........x.*X....|
00008000  64 08 d6 ea 00 00 00 02  00 00 00 00 00 00 00 00  |d...............|
00008010  00 00 00 00 00 1c 23 72  00 03 00 00 00 00 00 00  |......#r........|
00008020  00 00 00 00 00 17 ff ff  ff ff 00 00 ff ff ff ff  |................|
00008030  00 00 00 00 00 00 00 00  00 01 00 00 00 00 00 00  |................|
00008040  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 00 00  |................|
*
00008060  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 05 d6  |................|
00008070  69 d2 00 00 00 03 ff ff  ff ff ff ff ff ff ff ff  |i...............|
00008080  ff ff ff ff ff ff ff ff  ff ff ff ff ff ff ff ff  |................|
*
000080f0  ff ff 00 00 00 00 00 00  00 02 00 00 00 00 00 00  |................|
00008100  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 00 00  |................|
*
00008120  00 00 ff ff ff ff 00 00  ff ff ff ff 00 00 05 d6  |................|
00008130  69 d2 ff ff ff ff ff ff  ff ff ff ff ff ff ff ff  |i...............|
00008140  ff ff ff ff ff ff ff ff  ff ff ff ff ff ff ff ff  |................|
*
000081b0  ff ff 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
000081c0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
0000bff0  00 00 00 00 00 00 00 00  37 83 3a e8 00 1c 23 72  |........7.:...#r|
0000c000  f7 53 51 49 00 00 00 03  ff ff ff ff ff ff ff ff  |.SQI............|
0000c010  00 00 00 00 00 1c 33 b0  45 bf 00 00 00 00 00 00  |......3.E.......|
0000c020  00 00 00 00 00 17 00 02  00 e3 80 05 00 00 00 00  |................|
0000c030  00 c0 00 02 00 02 00 03  00 00 00 00 00 00 00 00  |................|
0000c040  00 00 00 00 00 00 00 00  00 24 00 00 00 17 00 00  |.........$......|
0000c050  00 02 00 f2 00 00 00 17  00 00 00 02 00 32 01 00  |.............2..|
0000c060  02 00 1c 69 6e 66 69 6d  75 6d 00 04 00 0b 00 00  |...infimum......|
0000c070  73 75 70 72 65 6d 75 6d  03 00 00 00 10 00 1d 00  |supremum........|
0000c080  00 00 00 03 1f 00 00 00  00 0d e0 99 00 00 01 70  |...............p|
0000c090  00 84 31 32 33 0a 00 00  00 18 00 24 00 00 00 00  |..123......$....|
0000c0a0  03 20 00 00 00 00 0d e1  9a 00 00 01 be 00 84 32  |. .............2|
0000c0b0  e6 95 b0 e6 8d ae e5 ba  93 10 00 00 00 20 ff b0  |............. ..|
0000c0c0  00 00 00 00 03 21 00 00  00 00 0d e6 9d 00 00 01  |.....!..........|
0000c0d0  bf 00 84 6d 79 73 71 6c  e6 95 b0 e6 8d ae e5 ba  |...mysql........|
0000c0e0  93 20 20 00 00 00 00 00  00 00 00 00 00 00 00 00  |.  .............|
0000c0f0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
0000fff0  00 00 00 00 00 70 00 63  96 b2 56 aa 00 1c 33 b0  |.....p.c..V...3.|
00010000  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00018000
  • 范围限制

a) 存储限制
varchar
字段是将实际内容单独存储在聚簇索引之外,内容开头用1到2个字节表示实际长度(长度超过255时需要2个字节),因此每行最多存储最多一共存储65535个字节。

b) 编码长度限制
varchar中,第一个字节不能用,后面1~2字节表示变长长度。
字符类型若为gbk,每个字符最多占2个字节,最大长度不能超过(65525-1-2) / 2
= 32766;
字符类型若为utf8,每个字符最多占3个字节,最大长度不能超过(65525-1-2) / 3
= 21845;

c) 行长度限制
导致实际应用中varchar长度限制的是一个行定义的长度。
MySQL要求一个行的定义长度不能超过65535。若定义的表长度超过这个值,则提示:
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs。

  • 实际长度计算示例:

a) 若一个表只有一个varchar类型,如定义为create table t1(c varchar(n))
charset=gbk;
则此处n的最大值为(65535-1-2)/2= 32766
减1的原因是实际行存储从第二个字节开始;
减2的原因是varchar头部的2个字节表示长度;
除2的原因是字符编码是gbk

b) 若一个表定义为create table t2(c int, c2 char(30), c3 varchar(n))
charset=utf8;
则此处N的最大值为(65535-1-2-4-30*3)/3=21812
减1和减2与上例相同;
减4的原因是int类型的c占4个字节;
减30*3的原因是char(30)占用90个字节,编码是utf8

  • varchar(10)和varchar(100)有区别吗?

对于VARCHAR数据类型来说,硬盘上的存储空间虽然都是根据实际字符长度来分配存储空间的,但是对于内存来说,则不是.内存使用固定大小的内存块来保存值.简单的说,就是使用字符类型中定义的长度,即100个字符空间.显然,这对于排序或者临时表(这些内容都需要通过内存来实现)作业会产生比较大的不利影响.

2.6.字符集最佳实践

2.6.2.常见乱码处理
  • 一般来说工作中由于字符集不统一造成的乱码或者潜在风险是需要将已乱码的数据进行转码的。
  • 处理乱码原则都是小字符集转换大字符集
  • latin1 –> utf8
  • gbk –> utf8
  • latin1 –> gbk
  • lation1字符集环境:LANG=US.ISO_8859-1
  • gbk字符集环境:LANG=en_US.GBK | LANG=zh_CN.GBK
  • utf8字符集环境:LANG=en_US.UTF-8

2.5.字符集建议

  • 非常肯定只有中文终端用户时,可选择gbk / gb2312
  • 为了方便数据迁移、以及多种终端展示,最好是utf8
  • 字符无需区分大小写时,采用默认的xx_ci校验集可以,否则选择xx_bin校验集(生产环境中,尽量不要修改校验集)
  • 5.6之前默认字符集是latin1,该字符集存放汉字是分开存放,以至于检索结果时不够精确,好处就是节省空间,不推荐使用

3.3.String类型

4.2.1.授权信息表

These mysql database tables contain grant information:

user: User accounts, global privileges, and other non-privilege
columns
db: Database-level privileges
tables_priv: Table-level privileges
columns_priv: Column-level privileges
procs_priv: Stored procedure and function privileges
proxies_priv: Proxy-user privileges

4.3.用户管理

1.3.分析乱码过程

为什么会出现乱码?
简单的说乱码的出现是因为:编码和解码时用了不同或者不兼容的字符集。对应到真实生活中,就好比是一个英国人为了表示祝福在纸上写了bless(编
码过程)。而一个法国人拿到了这张纸,由于在法语中bless表示受伤的意思,所以认为他想表达的是受伤(解码过程)。这个就是一个现实生活中的乱码情
况。在计算机科学中一样,一个用UTF-8编码后的字符,用GBK去解码。由于两个字符集的字库表不一样,同一个汉字在两个字符表的位置也不同,最终就会
出现乱码。

乱码的过程:

  • 编码
    假设我们在页面上看到浜屽搱这样的乱码,而又得知我们的浏览器当前使用GBK编码。那么第一步我们就能先通过GBK把乱码编码成二进制表达式。当然查表编码效率很低,我们也可以用以下SQL语句直接通过MySQL客户端做编码工作:

mysql> select hex(convert('浜屽搱' using gbk));
+-------------------------------------+
| hex(convert('浜屽搱' using gbk))    |
+-------------------------------------+
| E4BA8CE59388                        |
+-------------------------------------+
1 row in set (0.01 sec)
  • 识别
    现在我们得到了解码后的二进制字符串E4BA8CE59388。然后我们将它按字节拆开。就不难发现这6个字节的数据符合UTF-8编码规则。如果整个数据流都符合这个规则的话,我们就能大胆假设乱码之前的编码字符集是UTF-8

澳门金沙vip 4

图片来自原创

  • 解码
    然后我们就能拿着E4BA8CE59388用UTF-8解码,查看乱码前的文字了。当然我们可以不查表直接通过SQL获得结果:

mysql> select convert(0xE4BA8CE59388 using utf8);
+------------------------------------+
| convert(0xE4BA8CE59388 using utf8) |
+------------------------------------+
| 二哈                               |
+------------------------------------+
1 row in set (0.00 sec)