mysql float字段类型数据查询为空问题

作者:matrix 发布时间:2021-12-28 分类:mysql PHP

结论

不要用float、double类型存储浮点数。改用decimal字段类型

过程

之前是知道浮点数最好不要用float类型做存储,手上遇到老项目使用就正好是float字段存储的体重数据,比如51.6这种。

普通的查询没问题,个别数据就出现查询为空的问题。后来发现都是浮点类型数据,排查框架的sql日志到PDO的参数绑定找遍了都没找到根源。还以为是PDO扩展的data_type出错,因为内部sql执行时浮点数的参数绑定是使用PDO::PARAM_STR

$this->PDOStatement->bindValue(':ThinkBind_1_', 51.6, PDO::PARAM_STR)

虽然字段设置了精度float(10,2),但是依然有查询为空出现。这就是float精度导致的问题。

吐槽

TP5.1 sql日志输出不准确,和实际执行的不一致!

sql输出日志为where wi=51.6,实际上执行是where wi="51.6",这也增加了排查的难度。

办法

  1. 浮点数查询使用like
  2. 使用函数比如oncat(wi)=51.6,或者format(wi,2) = format(51.6 ,2)
  3. 使用decimal字段类型

参考:

https://www.cnblogs.com/powerwu/articles/8465031.html

https://blog.csdn.net/luccs624061082/article/details/84286253

存储MongoDb的经纬度数据实现空间索引

作者:matrix 发布时间:2018-11-26 分类:零零星星

数据存储

mongoDb自带了地理空间索引,使用2dsphere索引类型 可以存储GeoJSON格式数据 指定点、线和多边形。
mongodb数据

{
    "_id" : ObjectId("5bf04829c6671064f9039dd0"), 
    "order_info" : {
        "survey_coordinate" : {
            "type" : "Point", 
            "coordinates" : [
                105.431, 
                42.009
            ],
            "name":"XXXXX街道办"
        }
    }
}

order_info.survey_coordinate字段为需要存储的数据
type:Point 点
coordinates 经纬度信息: [longitude, latitude]代表[经度, 纬度]
name为附加自定的存储名称字段

参考:
http://geojson.org/
mongodb官方文档:https://docs.mongodb.com/manual/reference/geojson/

添加索引

  1. 手动创建
    db.order.ensureIndex( {"order_info.survey_coordinate" : "2dsphere"} )
    //联合索引 如:  db.order.createIndex( {"order_info.survey_coordinate" : "2dsphere","category" : -1, name: 1 } )
    // 指定索引名称:db.order.createIndex( {"order_info.survey_coordinate" : "2dsphere"},{name:"locc"} ) 
    

    参考:
    http://www.runoob.com/mongodb/mongodb-indexing.html

  2. 代码创建

    后来专门去找了下php下代码的操作,翻看各种TP文档无果,还是Google靠谱。php官方文档中
    ThinkPHP5.0 参考

            $mongodb;//mongodb对象为 think\db\Connection  https://www.kancloud.cn/manual/thinkphp5/167865 
            //创建索引
            $command = new \MongoDB\Driver\Command([
                'createIndexes' => 'order',//给哪个集合创建索引
                'indexes' => [[
                    'name' => 'order_info_loc_2dsphere',//索引名称
                    'key'  => ['order_info.survey_coordinate' => '2dsphere']//索引的字段  索引类型
                ]]
            ]);
           $result =  $mongodb->command($command);
    
           //首次创建返回数据
    //        /vagrant/saasSystem/thinkphp/library/think/Debug.php:193:
    //array (size=1)
    //  0 =>
    //    array (size=4)
    //      'createdCollectionAutomatically' => boolean false
    //      'numIndexesBefore' => int 1
    //      'numIndexesAfter' => int 2
    //      'ok' => float 1
    //0.322759s ShowPageTrace
    
           //重复创建返回数据
    //        /vagrant/saasSystem/thinkphp/library/think/Debug.php:193:
    //array (size=1)
    //  0 =>
    //    array (size=5)
    //      'createdCollectionAutomatically' => boolean false
    //      'numIndexesBefore' => int 2
    //      'numIndexesAfter' => int 2
    //      'note' => string 'all indexes already exist' (length=25)
    //      'ok' => float 1
           halt($result);
    

    参考:
    http://php.net/manual/en/mongocollection.createindex.php#119584

数据查询

  1. runCommand执行

    db.runCommand( {
       geoNear: "order" , //存在2dsphere索引的集合名称
       near: { type: "Point" , coordinates: [118.783799, 31.979234] } ,
       spherical: true,
       limit:1,
    }) 
    
  2. find
    db.order.find({"order_info.survey_coordinate" : { "$near" : { "$geometry" : { "type" : "Point", "coordinates" : [118.783799, 31.979234] } } } })
    

参考:
https://blog.csdn.net/u014230597/article/details/52635190
https://blog.csdn.net/medea_yang/article/details/53436460

xiaomi开源SQL优化建议工具 - soar

作者:matrix 发布时间:2018-10-29 分类:零零星星

SOAR -- sql Optimizer And Rewriter 由小米运维 DBA 团队开发的SQL 智能优化与改写工具20181021宣布开源。
github:https://github.com/xiaomi/soar

安装说明:https://github.com/xiaomi/soar/blob/master/doc/install.md

测试环境:ubuntu 16.04

安装GO

apt-get安装失败改用
源码下载:https://GOlang.GOogle.cn/dl/
配置环境变量

解压:
> sudo tar -C /usr/local -xzf go1.11.1.linux-amd64.tar.gz

全局用户的环境变量:
> sudo vi /etc/profile

末尾添加:
export PATH=$PATH:/usr/local/go/bin

go版本查看:

> go version

source更新环境变量:

source /etc/profile

还需要配置GOPATH环境变量:表示go的工作目录 USER_NAME 为用户名
export GOPATH="/home/USER_NAME/go"

安装soar

> go get -d github.com/XiaoMi/soar
> cd ${GOPATH}/src/github.com/XiaoMi/soar && make

若安装顺利,最终会显示success。否则 build error

我前几次安装都失败

go build github.com/pingcap/tidb/parser: /usr/local/go/pkg/tool/linux_amd64/compile: signal: killed 
Makefile:69: recipe for target 'build' failed

之后google找到帖子 有人说是vps内存太低导致的,遂重启了下Ubuntu 重新make。bingo~

安装成功之后会发现~/go/src/github.com/XiaoMi/soar多出一个 soar文件。
执行测试:

> cd ~/go/src/github.com/XiaoMi/soar
> echo 'select * from film' | ./soar

图片4152-xiaomi开源SQL优化建议工具 - soar

使用soar

常用命令:https://github.com/XiaoMi/soar/blob/master/doc/cheatsheet.md

打印所有的启发式规则1

$ soar -list-heuristic-rules

打印支持的报告格式

$ soar -list-report-types

以指定格式输出报告

$ soar -report-type json

语法检查工具

$ echo "select * from tb" | soar -only-syntax-check
$ echo $?
0

$ echo "select * fromtb" | soar -only-syntax-check
At SQL 0 : syntax error at position 16 near 'fromtb'
$ echo $?
1

慢日志进行分析示例

$ pt-query-digest slow.log > slow.log.digest
# parse pt-query-digest's output which example script
$ python2.7 doc/example/digest_pt.py slow.log.digest > slow.md

SQL指纹

$ echo "select * from film where col='abc'" | soar -report-type=fingerprint

输出

select * from film where col=?

将UPDATE/DELETE/INSERT语法转为SELECT

$ echo "update film set title = 'abc'" | soar -rewrite-rules dml2select,delimiter  -report-type rewrite

输出

select * from film;

合并多条ALTER语句

$ echo "alter table tb add column a int; alter table tb add column b int;" | soar -report-type rewrite -rewrite-rules mergealter

输出

ALTER TABLE `tb` add column a int, add column b int ;

SQL美化

$ echo "select * from tbl where col = 'val'" | ./soar -report-type=pretty

输出

SELECT
  *
FROM
  tbl
WHERE
  col  = 'val';

EXPLAIN信息分析报告

$ soar -report-type explain-digest << EOF
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL | 1131 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
EOF
##  Explain信息

| id | select\_type | table | partitions | type | possible_keys | key | key\_len | ref | rows | filtered | scalability | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1  | SIMPLE | *film* | NULL | ALL | NULL | NULL | NULL | NULL | 0 | 0.00% | &#x2620;&#xfe0f; **O(n)** |  |


### Explain信息解读

#### SelectType信息解读

* **SIMPLE**: 简单SELECT(不使用UNION或子查询等).

#### Type信息解读

* &#x2620;&#xfe0f; **ALL**: 最坏的情况, 从头到尾全表扫描.

markdown转HTML

通过指定-report-css, -report-javascript, -markdown-extensions, -markdown-html-flags这些参数,你还可以控制HTML的显示格式。

$ cat test.md | soar -report-type md2html > test.html

PEACE~

参考:
https://github.com/beego/wetalk/issues/32
https://www.oschina.net/news/101034/xiaomi-opensource-soar
https://juejin.im/entry/5bbf21fde51d450e61605d99