“MySQL”的版本间的差异

2017年11月8日 (三) 05:46的版本

文章

mysql性能优化

防止生产库数据被drop、truncate等误删

MYSQL命令

Invalid default value for 'create_date' timestamp field

That is because of server SQL Mode - NO_ZERO_DATE.

红鸟面试题

SELECT type_id , name FROM (
     SELECT COUNT(*) AS times, type_id, name FROM articles 
     WHERE mtime >=unix_timestamp('2015-1-1') AND mtime <= unix_timestamp('2016-8-30')  
     GROUP BY type_id
) AS list 
WHERE list.times > 3

Mysql大小写敏感的问题

设置字符集：

utf8_general_ci -- 不区分大小写

utf8_bin -- 区分大小写

MySQL(root用户)密码重置

如果MySQL正在运行，首先杀之：

killall -TERM mysqld

启动 MySQL ：

bin/mysqld_safe --skip-grant-tables &

就可以不需要密码就进入MySQL了。 然后就是：

> USE mysql
> UPDATE USER SET password=password("new_pass") WHERE USER="root";
> FLUSH privileges

重新杀MySQL，用正常方法启动MySQL。

让MySQL支持中文排序的实现方法

SELECT md.deptname, md.deptcode FROM tg_memberdept AS md WHERE md.parentcode=1 ORDER BY CONVERT(md.deptname USING gbk) ASC

查询生成表

CREATE TABLE abc AS(SELECT mo.*, m.staffname, m.deptname FROM `tg_membersoption` AS mo LEFT JOIN tg_members AS m ON (mo.staffid = m.staffid) )

跨数据库join

SELECT o.order_number, o.name, o.mobile, o.product_id, s.name AS product_name, o.memo, o.amount, o.STATUS,
    CASE
        WHEN o.STATUS =  1 THEN '待审核'
        WHEN o.STATUS =  2 THEN '待支付'
        WHEN o.STATUS =  3 THEN '处理中'
        WHEN o.STATUS =  4 THEN '完成订单'
        WHEN o.STATUS =  5 THEN '关闭'
        WHEN o.STATUS = 10 THEN '自动取消'
        WHEN o.STATUS = 11 THEN '手动取消'
    END AS status_name,
    p.payment_method_id,
    CASE
        WHEN p.payment_method_id = 1 THEN '支付宝'
        WHEN p.payment_method_id = 2 THEN '微信'
    END AS payment_method,
    FROM_UNIXTIME(o.paid_at,'%Y-%m-%d %H:%i:%s') AS paid_at,
    FROM_UNIXTIME(o.created_at,'%Y-%m-%d %H:%i:%s') AS created_at
FROM test_social.mi_product_order AS o 
    LEFT JOIN mi_product_service AS s ON (o.product_id = s.id)
    LEFT JOIN test_payment.mi_orders AS p ON (o.trade_number = p.app_order_number)

格式化时间FROM_UNIXTIME()

SELECT FROM_UNIXTIME( start_time,  '%Y-%m-%d' ) AS DATE, SUM( time_long ) /3600 AS HOUR FROM todo_lists 
WHERE start_time >= 1412114400 AND start_time <= 1414710000 GROUP BY DATE ORDER BY DATE ASC
 
SELECT p.*, FROM_UNIXTIME( addtime, '%Y-%m-%d %H:%i:%s' ) AS DATE FROM tg_pageview AS p WHERE addtime > UNIX_TIMESTAMP('2015-08-10') ORDER BY id DESC
 
SELECT LEFT(CREATED_DTM_LOC, 7) AS MONTH,
CASE
    WHEN SOURCE = 0 THEN
    'PC'
    WHEN SOURCE = 1 THEN
    '微信'
END AS SOURCE,
COUNT(SOURCE) AS COUNT FROM CTS_ORDER 
WHERE CREATED_DTM_LOC >= '2014-12-01' AND CREATED_DTM_LOC <= '2014-12-31' GROUP BY SOURCE

myisam和innodb中count(*)的区别

1. myisam保存表的总行数,因此count(*)并且无where子句,很快会返回表的总行数
2. myisam保存表的总行数,利用count(column)并且无where子句,并且此column不为null,很快会返回表的总行数
3. myisam保存表的总行数,利用count(column)并且无where子句,并且此column可以为null,mysql会对表进行全表或全索引扫描来确定行数
4. innodb查询count(*),count(column(not null)),count(column(may be null))并且无where子句,mysql会对表进行全表或全索引扫描来确定行数
5. myisam和innodb查询count(*),count(column(not null)),count(column(may be null))并且存在where子句,mysql会对表进行索引扫描(如果列上有索引),速度也比较快

五个常用MySQL图形化管理工具

phpMyAdmin（http://www.phpmyadmin.net/）

MySQLDumper（http://www.mysqldumper.de/en/）

Navicat（http://www.navicat.com/）

MySQL GUI Tools（http://dev.mysql.com/downloads/gui-tools/）

MySQL ODBC Connector（http://dev.mysql.com/downloads/connector/odbc/）

配置phpmyadmin连接远程 MySQL数据库

一、修改phpmyadmin目录中libraries文件夹下的config.default.php文件

1、查找$cfg['PmaAbsoluteUri'] ，将其值设置为http://192.168.1.1/phpmyadmin

2、查找$cfg['Servers'][$i]['host'] ， 将其值设置为192.168.1.2

3、查找$cfg['Servers'][$i]['user'] ， 将其值设置为admin

4、查找$cfg['Servers'][$i]['password'] ， 将其值设置为123456

二、配置完成，现在通过http://192.168.1.1/phpmyadmin 输入数据库用户名：admin 密码：123456 即可访问服务器192.168.1.2上面的MySQL数据库。

oracle的rownum 在MySql里用什么表示

mysql用limit，没有oracle那么麻烦。

从index 0 开始，取5条记录 select * from table limit 0,5

还可以取中间的记录：从index 5开始，连续取10条记录 select * from table limit 5,10

自增偶数id

mysql_query("SET @@auto_increment_offset=2"); 
mysql_query("SET @@auto_increment_increment=2");

手动把习题日期延后的SQL语句

UPDATE questions AS q LEFT JOIN item_type AS t 
ON (q.type_id = t.id) 
SET q.sync_state='modify', q.next_play_date = '2014-10-01'
WHERE q.uid = 1 AND t.priority = 0 AND next_play_date <= '2014-08-20'

手动取消条目编辑状态的SQL语句

UPDATE questions SET sync_state=''

重启MYSQL

/etc/inint.d/mysqld restart

mysql-bin.000001文件的来源及处理方法

/etc/my.cnf

注释如下两行，重启mysql服务即可。

log-bin=mysql-bin

binlog_format=mixed

远程连接MySQL数据库

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'pwd-xxx(注意这里填用户密码)' WITH GRANT OPTION;
FLUSH PRIVILEGES ;

仍无法访问，关闭防火墙。

service iptables stop

service iptables start

修改密码

UPDATE USER SET Password = PASSWORD('newpass') WHERE USER = 'root';
 
GRANT ALL PRIVILEGES ON ak2.* TO aike@localhost IDENTIFIED BY "123456"; 
 
FLUSH PRIVILEGES;

全文索引

SELECT * FROM app WHERE MATCH (trackName) AGAINST ('kids');

http://dev.mysql.com/doc/refman/5.1/zh/functions.html#fulltext-fine-tuning 全文索引停止词列表及修改

bookmarks

• mysql 联接与子查询的效率哪一个高? - 其他数据库开发 / MySQL/Postgresql
• mysql字符串连接函数 - 守望天使的日志 - 网易博客
• 数据库架构的升级和变更 - 飞洋过海 - 博客园

数据库开发性能设计

via CSDN博客推荐文章 by ZengMuAnSha on 1/20/12

数据库开发性能设计

鉴于开发人员一般情况下都是从头做到尾,也就是说从界面到业务逻辑开发,到SQL编写,到表的建设,数据的模拟测试. 在工期短,需求变更频繁情况下,性能就会成为了牺牲品.优化就日后再说,如同 www.12306.cn网站样!

1 分工篇

如同网站开发有美工搞CSS代码,HTML代码样,NET和JAVA程序员应该把SQL语句全部交给数据库开发工程师写.可惜这不过是理想状态而已. 很多的时候都是程序员写代码同时连带把SQL也写了.!

如何解决? 其实很简单，所有的SQL都做成视图，也就是VIEW。什么SUM() GROUP BY WHERE 语句 统统做成视图 程序员可以继续写SQL， 或许简单,或许复杂,或许糟糕。不要紧.做成了视图就行了.业务代码就从视图里获取些数据就可以了! 就一个简单的SQL 比如

select rcdate,usernuber from vw_active_user;

那个性能问题怎么办? 做成了视图,日后发现性能问题,则可以交给DBA去优化。DBA去优化视图,重写视图里的SQL语句,做起来就简单方便了。不再需要跟程序员去沟通,协调,强逼,投诉,无奈等时间成本，顶多向程序员了解下业务逻辑.。若DBA忙,也可以交给数据库开发工程师去优化重写。

2 读写分离

当个项目运行很久,发现数据量大增,用户大增.后台数据库有点吃不消。

DBA想把读取业务和写入业务分开了。等他于程序员沟通后发现其实这是件不可能完成的任务.难道很困难吗？因为业务代码都是混合行的。比如www.12306.cn 差不多是混合型的.

你查火车票,然后去下单. 那么简单的业务.包含了读取,然后下单,写入业务.从代码来看就是SELECT和UPDATE 两类操作最多.那怕 ORACLE RAC集群上百台机器去堆,才会有些性能提高，只不过是杯水车薪。因为ORACLE RAC 是机器内存融合在一起的，互相之间内部通讯,协调开销猛增.

其实很简单,就开始稍微麻烦点 项目开发的时候,后台数据库搞两帐号一个是只读帐号,另外个是只写帐号. 两帐号的表分别独立. 两个帐号的数据同步就由DBA或者是数据库开发师负责。这样一来，做集群,做机器分布就容易多了!

3 数据驱动

所谓数据驱动啦! 就是业务增加某个东西的时候,不需要改JAVA代码,也不需要改SQL.

比如说一个网站项目涉及全国31省的数据.如果没有把省作为驱动地话，或者认为我们国家就31个省。

如果有一天,台湾回归了,成为了第32个省,香港被提升为省级,第33个省，这都是有可能的。

这时候我们发现有些程序要改代码. 因为程序里面包含类似的代码：for int i=1 to 31

像这样隐藏的代码估计很多，要从汪洋般代码里找出来修改。修改的人力和时间成本高，

而且还没有成就感，另外要进行大量地测试来保证质量，还有提心吊胆地上线，发现某个异常白发又长一根。

比如 代码和SQL里的尽量不要有 字符，数字等内嵌的。这些由用户从页面上输入，或者由后台维护进某个表。除了一般的字典表外，应该建立个下面格式的表

Select * from t_Active_user

where recdate=:bind_date

and provcode=(select value from t_dirction where id = 100086)

代码里只有一个10086 内嵌的数字

4 业务代码放哪里?

按照JAVA流行趋势,业务代码应该放在中间件中,由JAVA代码完成.从而使得业务独立于数据库。不过JAVA流行以来，用纯JAVA写业务代码，感觉运行缓慢。记得05年初在一家电力设备厂，为电力网开发项目，JAVA组和NET组发生了冲突。老板会上说了JAVA不是，因为代码跑得慢，害得客户购买IBM机器还是那么不尽人意！ 据说国内某个大的ERP软件，鼓吹JAVA中间件如何如何的好。不知发现新版ERP系统还有很多业务放在数据库端的存储过程。业界视乎存在这样的看法要么把业务放在中间件上，要么放在数据库端的存储过程上。 JAVA那 HIBRATE 把数据库当作数据存放的地方,把数据全取到中间服务器上,然后一级,二级缓冲.再搞个HQB的查询语言来优化性能.. 这就是JAVA慢的原因所在

在下认为，业务代码放在中间服务器上是必须的，那数据库端也要有存储过程， 这是为了性能！

数据库端放的是数据代码,以前C/S架构的业务代码和数据代码混合在一起,都放在数据库端,由SQL语句去实现.那么到了B/S架构应用,就该把业务代码分离出去,数据库端就存放数据代码,由存储过程的SQL实现.

什么是数据代码呢? 数据从表里取出来后,要做过滤,汇总等不涉及业务规则的代码。比如拿火车购票网来说，它什么时候把票放出来是业务代码。查询某个时间，某站到某站的火车列表，席位，票数，以及票价，这是数据代码。一张身份证只能购买同天同一车次的一张票，这也是业务代码。登录时间从6点到23点，付款时间是45分钟，一个人一次购买5张火车票。

只要分清了业务代码和数据代码，就容易了。业务放在中间服务器上，数据代码放在数据库服务器上。业务规则独立和性能都得到均衡！

JAVA做面向对象设计时，做出来的实体对象，其属性要存储，不能直接转换成物理表。 转换成物理表要交给数据库专家设计，数据库专家拿到实体对象表根据3N方式和经验转换成逻辑表。DBA专家拿着逻辑表转换成物理表。

一个团队组成：项目经理，需求专家，业务设计专家，业务开发工程师，数据库专家，数据库开发工程师，开发DBA，测试工程师。

很多项目组是凑不了那么多人手，而且为了追求开发速度，基本构成 项目经理，开发工程师，测试工程师。这样性能就得不可能很好！采用上面4个策略，可以说发现了性能问题，改起来就容易多了。

Got a packet bigger than max_allowed_packet

这个问题可以有2个解决方法：

1. 临时修改：

   mysql>set global max_allowed_packet=524288000;    #修改 512M

2. 修改my.cnf，需重启mysql。在 [MySQLd] 部分添加一句（如果存在，调整其值就可以）

   max_allowed_packet = 256M （根据实际情况调整数值）

三种东西永远不要放到数据库里

英文原文：Three things you should never put in your database

我已经在很多演讲里说过，改进你的系统的最好的方法是先避免做“蠢事”。我并不是说你或你开发的东西“蠢”，只是有些决定很容易被人们忽略掉其暗含的牵连，认识不到这样做对系统维护尤其是系统升级带来多大的麻烦。作为一个顾问，像这样的事情我到处都能见到，我还从来没有见过做出这样的决定的人有过好的结果的。

1、图片，文件，二进制数据

既然数据库支持 BLOB 类型的数据，把文件塞进 BLOB 字段里一定没有错了！？错，不是这样的！别的先不提，在很多数据库语言里，处理大字段都不是很容易。

把文件存放在数据库里有很多问题：

1. 对数据库的读/写的速度永远都赶不上文件系统处理的速度
2. 数据库备份变的巨大，越来越耗时间
3. 对文件的访问需要穿越你的应用层和数据库层

这后两个是真正的杀手。把图片缩略图存到数据库里？很好，那你就不能使用 nginx 或其它类型的轻量级服务器来处理它们了。

给自己行个方便吧，在数据库里只简单的存放一个磁盘上你的文件的相对路径，或者使用 S3 或 CDN 之类的服务。

2、短生命期数据

使用情况统计数据，测量数据，GPS 定位数据，session 数据，任何只是短时间内对你有用，或经常变化的数据。如果你发现自己正在使用定时任务从某个表里删除有效期只有一小时，一天或数周的数据，那说明你没有找对正确的做事情的方法。使用 redis，statsd/graphite， Riak，它们都是干这种事情更合适的工具。这建议也适用于对于收集那些短生命期的数据。

当然，用挖土机在后花园里种土豆也是可行的，但相比起从储物间里拿出一把铲子，你预约一台挖土机、等它赶到你的园子里挖坑，这显然更慢。你要选择合适的工具来处理手头上的事。

3、日志文件

把日志数据存放到数据库里，表面上看起来似乎不错，而且“将来也许我需要对这些数据进行复杂的查询”，这样的话很得人心。这样做并不是一个特别差的做法，但如果你把日志数据和你的产品数据存放到一个数据库里就非常不好了。

也许你的日志记录做的很保守，每次 web 请求只产生一条日志。对于整个网站的每个事件来说，这仍然会产生大量的数据库插入操作，争夺你用户需要的数据库资源。如果你的日志级别设置为 verbose 或 debug，那等着看你的数据库着火吧。

你应该使用一些比如 Splunk Loggly 或纯文本文件来存放你的日志数据。这样去查看它们也许会不方便，但这样的时候不多，甚至有时候你需要写出一些代码来分析出你想要的答案，但总的来说是值得的。

可是稍等一下，你是那片不一样的雪花，你遇到的问题会如此的不同，所以，如果你把上面提到的三种东西中的某一种放到了数据库里也不会有问题。不，你错了，不，你不特殊。相信我。