目录
3 mysql Packet for query is too large异常解决
1 数据库字符集选择
1.1 utf8和utfmb4区别
一般选择utf8.下面介绍一下utf8与utfmb4的区别
utf8mb4兼容utf8,且比utf8能表示更多的字符。至于什么时候用,看你的做什么项目了,unicode编码区从1 ~ 126就属于传统utf8区,当然utf8mb4也兼容这个区,126行以下就是utf8mb4扩充区,什么时候需要存储那些字符,才用utf8mb4,否则会浪费空间。
1.2 排序检验规则
1.2.1 COLLATE介绍
COLLATE 通常是和数据编码(CHARSET)相关的 ,一般来说每种 CHARSET 都有多种它所支持的 COLLATE,并且每种 CHARSET 都指定一种 COLLATE 为默认值,utf8mb4 编码的默认值为 utf8mb4_general_ci(从 mysql 8.0 开始 utf8mb4默认的 COLLATE 也改为了 utf8mb4_0900_ai_ci)
COLLATE,校验规则, 会影响到 ORDER BY 语句的顺序,会影响到 WHERE 条件中大于小于号筛选出来的结果,会影响DISTINCT、GROUP BY、HAVING语句的查询结果。
另外,mysql 建索引的时候,如果索引列是字符类型,也会影响索引创建,只不过这种影响我们感知不到。总之,凡是涉及到字符类型比较或排序的地方,都会和 COLLATE 有关
使用命令SHOW COLLATION来查看数据库支持的校对规则
可以在sql语句中强制指定校对规则进行比较
select * from tbl
where col_b COLLATE latin1_danish_ci
= col_c COLLATE latin1_danish_ci;
字段col_b 指定校验规则latin1_danish_ci
字段col_c 指定校验规则latin1_danish_ci
1.2.2 常见排序示例
排序一般分为两种:utf_bin和utf_general_ci
bin 是二进制, a 和 A 会别区别对待.
例如你运行:
select * from table where a = 'a';
那么在utf8_bin中你就找不到 a = 'A' 的那一行, 而 utf8_general_ci 则可以.
utf8_general_ci 不区分大小写,这个你在注册用户名和邮箱的时候就要使用。
utf8_general_cs 区分大小写,如果用户名和邮箱用这个 就会照成不良后果
utf8_bin:字符串每个字符串用二进制数据编译存储。 区分大小写,而且可以存二进制的内容
utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。
utf8_general_ci校对速度快,但准确度稍差。(准确度够用,一般建库选择这个)
utf8_unicode_ci准确度高,但校对速度稍慢。
所以一般选择如下:
1.2.3 创建表时使用字符集和校验规则
示例1:表和列定义
CREATE TABLE t1
(
c1 CHAR(10) CHARACTER SET latin1 COLLATE latin1_german1_ci
) DEFAULT CHARACTER SET latin2 COLLATE latin2_bin;
在这里我们有一个列使用latin1字符集和latin1_german1_ci校对规则。是显式的定义,因此简单明了。需要注意的是,在一个latin2表中存储一个latin1列不会存在问题。
示例2:表和列定义
CREATE TABLE t1
(
c1 CHAR(10) CHARACTER SET latin1
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;
这次我们有一个列使用latin1字符集和一个默认校对规则。尽管它显得自然,默认校对规则却不是表级。相反,因为latin1的默认校对规则总是latin1_swedish_ci,列c1有一个校对规则latin1_swedish_ci(而不是latin1_danish_ci)。
示例3:表和列定义
CREATE TABLE t1
(
c1 CHAR(10)
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;
我们有一个列使用一个默认字符集和一个默认校对规则。在这种情况下,MySQL查找表级别来确定列字符集和校对规则。因此,列c1的字符集是latin1,它的校对规则是latin1_danish_ci。
示例4:数据库、表和列定义
CREATE DATABASE d1
DEFAULT CHARACTER SET latin2 COLLATE latin2_czech_ci;
USE d1;
CREATE TABLE t1
(
c1 CHAR(10)
);
我们创建了一个没有指定字符集和校对规则的列。我们也没有指定表级字符集和校对规则。在这种情况下,MySQL查找数据库级的相关设置。(数据库的设置变为表的设置,其后变为列的设置。)因此,列c1的字符集为是latin2,它的 校对规则是latin2_czech_ci。
1.2.4 在SQL语句中使用COLLATE
使用COLLATE子句,能够为一个比较覆盖任何默认校对规则。COLLATE可以用于多种SQL语句中。下面是一些例子:
使用ORDER BY:
SELECT k FROM t1 ORDER BY k COLLATE latin1_german2_ci;
使用AS:
SELECT k COLLATE latin1_german2_ci AS k1
FROM t1 ORDER BY k1;
使用GROUP BY:
SELECT k FROM t1
GROUP BY k COLLATE latin1_german2_ci;
使用聚合函数:
SELECT MAX(k COLLATE latin1_german2_ci)
FROM t1;
使用DISTINCT:
SELECT DISTINCT k COLLATE latin1_german2_ci
FROM t1;
使用WHERE:
SELECT * FROM t1
WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k;
SELECT * FROM t1
WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci;
使用HAVING:
SELECT k FROM t1 GROUP BY k
HAVING k = _latin1 'Müller' COLLATE latin1_german2_ci;
1.2.5 COLLATE子句优先
COLLATE子句有较高的优先级(高于||),因此下面两个表达式是等价的:
x || y COLLATE z
x || (y COLLATE z)
1.3 更改相关编码
1.3.1 更改编码SQL
- 创建库时指定编码:create database testdb default charset GBK
- 修改库的编码: ALTER DATABASE `testtable` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin
- 修改表的编码:ALTER TABLE `testtable` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin 4
- 修改字段的编码: ALTER TABLE `tablename` CHANGE `fieldname` `fieldname` VARCHAR( 45 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL (注意:此处字段就是要重复写两次)
- 查看字段编码:SHOW FULL COLUMNS FROM `tablename`
- 修改一张表的所有字段的编码格式:alter table `tablename` convert to character set utf8;
1.3.2 中文乱码
查看数据库的编码方式:show variables like 'character%';如下所示,可知数据库的编码为latin1,需要修改为gbk或者是utf8;其中,character_set_client为客户端编码方式;character_set_connection为建立连接使用的编码;character_set_database数据库的编码;character_set_results结果集的编码;character_set_server数据库服务器的编码;只要保证以上四个采用的编码方式一样,就不会出现乱码问题。
show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
修改MySQL的配置文件/etc/my.cnf文件统一编码
找到客户端配置[client] 在下面添加
default-character-set=utf8 默认字符集为utf8
在找到[mysqld] 添加
default-character-set=utf8 默认字符集为utf8
init_connect='SET NAMES utf8' (设定连接mysql数据库时使用utf8编码,以让mysql数据库为utf8运行)
当这几个变量的值不一致或者显示乱码时,还可以通过如下命令来修改:
mysql> SET character_set_client = utf8;
mysql> SET character_set_results = utf8;
mysql> SET character_set_connection = utf8;
使用jdbc/odbc时指定编码格式
// Load the JDBC-ODBC bridge driver
Class.forName(sun.jdbc.odbc.JdbcOdbcDriver) ;
// setup the properties
java.util.Properties prop = new java.util.Properties();
prop.put( " charSet " , " UTF8" );
prop.put( " user " , username);
prop.put( " password " , password);
// Connect to the database
con = DriverManager.getConnection(url, prop);
2 大小写报错
2.1 报错现象
有时候发现在自己本机Windows上开发和测试过程中一直没有问题,临近项目交付的时候,部署到Linux服务器上后,发现有报错,日志信息大概是:
MySQLSyntaxErrorException: Table ‘mes_db.student’ doesn’t exist
2.2 处理过程
在本机Window和Linux分布查看如下:
mysql> show variables like ‘%case%’;
windows下如图所示
linux下如图
在Linux上数据库中显示的student,不过s是小写;查看代码发现代码中还真把表名写成Student,就一个s写成大写S了。
问题找到了,原来是不小心写SQL的时候没有写对表名,改一下表名就搞定了,功能也一切正常了
从上面的结果已经可以看出不同了
当 lower_case_table_names 为 0 时表示区分大小写,为 1 时表示不区分大小写
在Windows上,默认值为1;在macOS上,默认值为2;在Linux上不支持值2;服务器强制该值为0
- 0 --大小写敏感。(Unix,Linux默认)
- 1–大小写不敏感。(Windows默认)
- 2 --大小写不敏感(macOS默认)
并且官网也提示说:如果在数据目录驻留在不区分大小写的文件系统(例如Windows或macOS)上的系统上运行MySQL,则不应将lower_case_table_names设置为0。
在window10环境尝试设置lower_case_table_names为0的时候,MySQL的服务怎么也启动不能,启动服务报错,因为windows系统对大小写不敏感
而Linux则是区分大小写的,因此,建议在开发测试环境下就严格控制代码大小写敏感,
提高代码的兼容和严谨
注意:如果报错Variable 'lower_case_table_names' is a read only variable,那么需要在配置文件中修改,然后重启mysql服务
3 mysql Packet for query is too large异常解决
最近新搭建了一个mysql,运行查询语句语句时报出错误:com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1550606 > 1048576). You can change this value on the server by setting the max_allowed_packet variable.
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1550606 > 1048576). You can change this value on the server by setting the max_allowed_
packet' variable.
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3915)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2598)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2825)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)
at com.hq.sql.Mysql.insert(Mysql.java:152)
at com.hq.sql.SqlExcution.query2Insert(SqlExcution.java:90)
at com.hq.schedule.Schedule.main(Schedule.java:27)
产生的主要原因就是,查询返回的数据量比较大,而允许返回的max_allowed_packet太小,导致以上错误。在linux系统下,找到/etc/my.cnf文件,修改max_allowed_packet=50M,重启mysql,问题解决。
在linux登陆mysql,运行命令 show tables;出现错误:
mysql> show tables;
Ignoring query to other database
经查,原来是由于登陆的时候,用-root,没有用-uroot。
/usr/bin/mysql -root -ppwd
为/usr/bin/mysql -uroot -ppwd问题解决。在登陆mysql,在命令行执行sql文件的命令为:source /home/sql.sql