mysql 相关笔记

1.mysql 中limit的使用
　　1.1 例子:select * from tb_user order by createdTime limit 0,15;
　　　　从第0行开始返回,返回15条数据;
　　　　下一页,应该是从第15条开始返回,返回15条数据:select * from tb_user order by createdTime limit 15,15;
　　　　而不是select * from tb_user order by createdTime limit 15,30;
　　　　第一个参数是从第几条开始返回,后一个参数是返回多少条数据
　　1.2 mysql也支持使用这种写法:select * from tb_user order by createdTime limit :limit offset :offset;
　　　　和postgresSQL一样的写法,但是传值和1.1中的不一样,具体的还需要实验
2.mysql中blob类型java程序中接收,先转换为byte[]

    byte[] b = (byte[])result.getContent();
    response.setContent(new String(b,"utf-8"));

3.mysql中拼接字符串

update tb_user set user_name=CONCAT('username:',user_name) -- 把username列的值改为username:用户名

4.mysql分区, 前提(表中有两个主键,一个ID自增,一个日期)

select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_name='tb_obd_trajectory'; // 查询分区alter table tb_obd_trajectory
partition by range(TO_DAYS (recive_time))(PARTITION p20160706 VALUES LESS THAN (TO_DAYS('2016-07-06')),
PARTITION p20160707 VALUES LESS THAN (TO_DAYS('2016-07-07')),
PARTITION p20160708 VALUES LESS THAN (TO_DAYS('2016-07-08')),
PARTITION p20160709 VALUES LESS THAN (TO_DAYS('2016-07-09')),
PARTITION p20160710 VALUES LESS THAN (TO_DAYS('2016-07-10')),
PARTITION p20160711 VALUES LESS THAN (TO_DAYS('2016-07-11')),
PARTITION p20160712 VALUES LESS THAN (TO_DAYS('2016-07-12')),
PARTITION p2019 VALUES LESS ThAN  MAXVALUE
);//修改分区

 5.报错[Err] 1140 - In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'radiate.a.id'; this is incompatible with sql_mode=only_full_group_by
sql版本5.7.10, 免安装版
这个问题是因为sql语句不太规范, 解决办法是修改sql_mode
sql_mode查询语句:
　　　　select @@sql_mode;
查出来的结果,里面包含着ONLY_FULL_GROUP_BY, 想要去掉这句, 需要在mysql的安装目录下找到my.ini修改里面的sql_mode
一般情况下, 改成
　　　　sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

修改完成后, 重启mysql服务, 再执行sql, 就不再提示这个错误了

6. 修改mysql表引擎为InnoDB

alter table tb_archive_basic_info ENGINE=InnoDB;