一、数据库问题

1、MySQL max_allowed_packet设置及问题

1、mysql根据配置文件会限制server接受的数据包大小。
有时候大的插入和更新会被max_allowed_packet 参数限制掉，导致失败。

查看目前配置
show VARIABLES like ‘%max_allowed_packet%’;

设置配置

方法1
可以编辑my.cnf来修改（windows下my.ini）,在[mysqld]段或者mysql的server配置段进行修改。
max_allowed_packet = 20M

方法2

set global max_allowed_packet = 2*1024*1024*10

说明：方法2的设置如果mysql服务重启，则会还原到初始状态

设置完毕后当前链接不会升效，只有新的链接才会升效

数据库所在服务器内存不够用,mysql也会重置max_allowed_packet

2、group_concat函数

1、用了group_concat后，select里如果使用了limit是不起作用的

2、用group_concat连接字段的时候是有长度限制的，默认1024

修改默认字符大小

1).在MySQL配置文件中加上

group_concat_max_len = 102400 #你要的最大长度

2).可以简单一点，执行语句,可以设置作用范围

SET GLOBAL group_concat_max_len=102400;

SET SESSION group_concat_max_len=102400;

3、系统默认的分隔符是逗号

1）修改默认的分隔符

select group_concat(f_a separator ‘_‘) from t_one group by f_b;

separator 是一个关键字，后面跟着要进行分隔的字符

3、mysql 遇到utf8mb4插入异常

1、mysql 支持的 utf8 编码最大字符长度为 3 字节，三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，Emoji 表情（Emoji 是一种特殊的 Unicode 编码，常见于 iOS 和 Android 手机上），和很多不常用的汉字，常常为四个字节，这时就需要utf8的扩展编码utf8mb4，utf8 编码最大字符长度为 4 字节

1）–修改数据库字符集
ALTER DATABASE test CHARACTER SET = utf8mb4;
2）–修改表字符集
alter table test convert to character set utf8mb4;
3）–修改字符字符集
ALTER TABLE test CHANGE COLUMN name name varchar(12) CHARACTER SET utf8mb4;

4、多表更新

update table A

left join table B

on A.id = B.aId

Set B.updateTime = ‘2017-05-31 12”12”12’,

A.updateTime = ‘2017-05-31 12”12”12’

Where A.id =1

update table A,table B

set B.updateTime = ‘2017-05-31 12”12”12’,

A.updateTime = ‘2017-05-31 12”12”12’

where A.id = B.aId and A.id =1

5、多表删除

delete A,B from A

left join B ON A.id=B.AId

where a.id=1

delete A, B from A, B

where A.id=B.aId and a.id=1

注意此处的delete A, B from 中的A, B 不能是别名

6、中文按字母排序

1、如果存储姓名的字段采用的是 utf8字符集，需要在排序的时候对字段进行转码排序；

select name from stu_user

order by convert(name using gbk) asc;

7、状态字段设计

1、有些标识状态的字段，在数据库设计的时候进入加工能减少代码处理

如上表：请假状态(1未审批，2同意，4销假,3驳回）

有一个int 类型的标识字段，也有一个String的描述字段，用以减少页面的展示判断

2、

每个二维码为从获取30天失效，

后台要统计出失效与未失效的二维码，

我们在获取时将效时间插入数据库，统计时传入日期与失效日期比较，统计出列表

8、数据查询

1、关联查询

1）

select * from table A

left join table B on A.id=b.Aid and B.type=1

where a.createTime=’2017-05-31’

2）

select * from table A

left join table B on A.id=b.Aid

where a.createTime=’2017-05-31’ and B.type=1

2、where与having的区别

1、where对分组前的属性进行筛选

2、having对分组后的每个组的整体属性进行筛选，用聚合函数体现

使用说明，在条件允许的情况下尽量使用where

3、left join 与right join 、inner join的区别

1、left join 以左表数据为基准，right join以右表数据为期准，inner以两表共同存在数据为基准

2、使用原则：

1）在条件允许的情况优先使用 inner join

2）在条件允许的情况下，优先使用表数据少的为基表

4、group by 与 with rollup

select * from teacher_askleave_attendance

group by schoolId with rollup

1、group by 按件件分组

2、With rollup 表明是否对分类聚合后的结果进行再汇总

9、数据库引擎

MyISAM是MySQL的默认存储引擎，基于传统的ISAM类型，支持全文搜索，但不是事务安全的，而且不支持外键。

InnoDB是事务型引擎，支持回滚、崩溃恢复能力、多版本并发控制、ACID事务，支持行级锁定

10、外键约束

InnoDB支持５种方式,分列如下

cascade方式在父表上update/delete记录时，同步update/delete掉子表的匹配记录

set null方式在父表上update/delete记录时，将子表上匹配记录的列设为null 要注意子表的外键列不能为not null On delete set null

No action方式如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作

Restrict方式同no action,都是立即检查外键约束

11、索引

1、普通索引

这是最基本的索引，它没有任何限制

CREATE INDEX indexName ON mytable(username(length));

二、唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

CREATE UNIQUE INDEX indexName ON mytable(username(length))

-- 修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

-- 创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

三、主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

四、组合索引

为了形象地对比单列索引和组合索引，为表添加多个字段：

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );

12、数据库大小写敏感问题

数据库和表名在 Windows 中是大小写不敏感的，而在大多数类型的 Unix 系统中是大小写敏感的

13、数据库定时器

二、开发过程中的问题

1、StringEscapeUtils类的转义与反转义方法

String sql=”1’ or ‘1’=’1”;

System.out.println(“防SQL注入:”+StringEscapeUtils.escapeSql(sql)); //防SQL注入

    System.out.println("转义HTML,注意汉字:"+StringEscapeUtils.escapeHtml("<font>chen磊  xing</font>"));    //转义HTML,注意汉字  
System.out.println("反转义HTML:"+StringEscapeUtils.unescapeHtml("<font>chen磊  xing</font>"));  //反转义HTML  
      System.out.println("转成Unicode编码："+StringEscapeUtils.escapeJava("陈磊兴"));     //转义成Unicode编码    
    System.out.println("转义XML："+StringEscapeUtils.escapeXml("<name>陈磊兴</name>"));   //转义xml  
    System.out.println("反转义XML："+StringEscapeUtils.unescapeXml("<name>陈磊兴</name>"));    //转义xml