Mysql 杂记

使用navicat进行数据库管理的时候，在查看表对象的时候会发现，每次刷新，数据表的记录数不断变化，尤其是大表。

对于100万的数据经常会显示九十几万，通过count(*)出来的数据是正确的。

和存储引擎有关。官方说明：

The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

在myisam这种不支持事务的引擎中，这个值是精确的，在innodb这种支持事务的引擎中，是估算的。

https://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

Mysql 二进制日志在 mysql/var/目录中

彻底关掉二进制日志的方法是注释 /etc/my.cnf log-bin=mysql-bin 重启服务器

SET FOREIGN_KEY_CHECKS=0;//只对当前连接有效

DROP TABLE `ABC`;

Mysql默认root账户不能远程登陆且空用户可以登陆（不输入用户名）

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

创建用户只能远程登陆，无法本地登录问题

GRANT ALL ON *.* TO haogp@”%” IDENTIFIED BY “haogp”时会在mysql.user表产生一条记录

原因分析（来源网络）

其中两个账户有相同的用户名monty和密码some_pass。两个账户均为超级用户账户，具有完全的权限可以做任何事情。一个账户 ('monty'@'localhost')只用于从本机连接时。另一个账户('monty'@'%')可用于从其它主机连接。请注意monty的两个账户必须能从任何主机以monty连接。没有localhost账户，当monty从本机连接时，mysql_install_db创建的localhost的匿名用户账户将占先。结果是，monty将被视为匿名用户。原因是匿名用户账户的Host列值比'monty'@'%'账户更具体，这样在user表排序顺序中排在前面。

解决办法

一删除空用户的记录（来源网络，为验证）

二给授权用户增加host为localhost的记录

数据表字段类型为`DATETIME`时，插入可以用now()//DOS下

设计表一个字段想表示这个记录的创建时间

CREATE TABALE `tablename` (

...

`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

)

或者

ALTER TABLE `tablename` ADD `add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

向mysql 中插入UNIX时间戳

连表查询

表1一条记录某字段值是表2某条记录的某字段

自己写的方法，不确定是否最优

select c1.`name` as name1,c2.`name` as name2,c3.`name` as name3

from shop_product p

inner join shop_class c1 on p.cid=c1.id

inner join shop_class c2 on p.cpid=c2.id

inner join shop_class c3 on p.cyid=c3.id

where p.id=29

表1一条记录某字段值是表2某条记录的某字段

select brand from shop_brand

inner join shop_product on shop_brand.id=shop_product.bid

where shop_product.id=29

SQL积累

SELECT sum(b.goods_num*b.member_goods_price) as goods_amount,sum(a.shipping_price) as shipping_amount,sum(b.goods_num*b.cost_price) as cost_price,sum(a.coupon_price) as coupon_amount,FROM_UNIXTIME(a.add_time,'%Y-%m') as gap

FROM __PREFIX__order a left join __PREFIX__order_goods b on a.order_id=b.order_id

WHERE a.add_time>$this->begin and a.add_time<$this->end AND a.pay_status=1 and a.shipping_status=1 and b.is_send=1

GROUP BY gap

ORDER BY a.add_time

创建某字段为插入数据时的系统时间

CURRENT_TIME timestamp not null default current_timestamp,

当一个进程开启事务修改某值时，另一进程修改会进等待状态。进程1未提交未回滚直接退出当前进程，该事务会自动结束，所有修改都会丢弃。

创建某个表的时候报错：

Incorrect table definition; there can be only one auto column and it must be defined as a key

`id` CHAR ( 32 ) NOT NULL COMMENT '主键',

`sn` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '',

原因是除了主键外，另一个键用了auto_increment

因为MySQL将自动增长的字段看作主键，导致表里就有两个主键

windows 命令行mysql服务

查看mysqld的命令行帮助：mysqld --verbose --help

mysql5.5

win

mysqld --console

如果省略--console选项，服务器向数据目录(默认为C:Program FilesMySQLMySQL Server 5.1data)中的错误日志写入诊断输出。错误日志文件的扩展名为.err。

mysqladmin -u root shutdown

需求记录所有的sql查询

win

修改配置文件

mysql.ini

log="D:/phpStudy/PHPTutorial/MySQL/sql_log.log"

重启mysql服务端就能看到了

Linux

navicate 是个好工具

通过工具查询可以看到很多细节，而不用自己手工去查

可以看到背后查了status相关的，通过日志查看具体实现

171224 15:42:00        1 Query    SET PROFILING = 1
            1 Query    SHOW STATUS
            1 Query    SHOW STATUS
            1 Query    SELECT * FROM `bank`
            2 Init DB    gp_practice
            2 Query    SELECT * FROM `gp_practice`.`bank` LIMIT 0
            2 Init DB    gp_practice
            2 Query    SHOW COLUMNS FROM `gp_practice`.`bank`
            1 Query    SHOW STATUS
            1 Query    SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID
            1 Query    SELECT STATE AS `Status`, ROUND(SUM(DURATION),7) AS `Duration`, CONCAT(ROUND(SUM(DURATION)/0.000986*100,3), '') AS `Percentage` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=5 GROUP BY SEQ, STATE ORDER BY SEQ

查询某挂单的最后成交价格，group by + order by 不能用

SELECT symbol_id,price FROM coin_deal_orders_match WHERE id IN ( SELECT max( id ) FROM coin_deal_orders_match GROUP BY symbol_id )

SQL积累

表中字段数值存储的是varchar类型，开始的时候用的是程序去循环然后做汇总，速度不够理想，改用数据库做汇总，速度有明显提高

SELECT
    SUM(
    CAST( filled_amount AS DECIMAL ( 18,8 ) 
    ) ) `sum`
FROM
    coin_deal_orders_match 
WHERE
    create_time > 123 
    AND order_uid = 123
    AND direction = 0 
    AND symbol_id IN ( SELECT id FROM coin_symbol WHERE gcoin = 'ETH' );

连接两个独立的表，没个有独立的主键，union后pk有重复，需要产生一个新的自增主键

SELECT
    `w_time`,
    CONVERT ( amount, DECIMAL ) amount,
    `user_name`,
    type,
    @rownum := @rownum + 1 AS nid 
FROM
    `mcct`,
    ( SELECT @rownum := 0 ) r UNION
SELECT
    w_time,
    number AS amount,
    username AS user_name,
    `type`,
    @rownum := @rownum + 1 
FROM
    score_log,
    ( SELECT @rownum := @rownum + 1 ) r2 
WHERE
    is_del = 0

今天填了个坑

CREATE TABLE `a` (
`id` tinyint(4) NOT NULL AUTO_INCREMENT,
`number` varchar(50) NOT NULL DEFAULT '0' COMMENT '变化',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `a` (`name`) VALUES ('b')

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '127' for key 'PRIMARY'

总是报主键重复，有点懵逼，还好最后发现了id 是个tinyint