Mysql-数据类型隐式转换 关键字问题描述解决问题思路问题总结 关键字 数据类型不同导致隐式转换 问题描述 sql 一: select id,sku,weight,image_
数据类型不同导致隐式转换
sql 一:
select id,sku,weight,image_path,state,brand_name,name,product_area,upc,sale_unit,cateGory,ele_gift,introduction,param from XXX where sku=8345903;
一条很简单的 SQL 每次执行需要 1-2 秒,经过查询 sku 字段也建有索引,那么原因何在呢?
SQL二:
delete from `otter_online`.`dest` where `id` = 2346532305031424 ; -- 把数据库CPU打满。
SQL 一: 我们查看表结构语句
mysql> show create table XXX\G*************************** 1. row *************************** Table: XXXCreate Table: CREATE TABLE `XXX` ( `id` int(11) NOT NULL AUTO_INCREMENT, `sku` varchar(45) DEFAULT NULL, `weight` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`), KEY `i_sku` (`sku`)) ENGINE=InnoDB AUTO_INCREMENT=1262482 DEFAULT CHARSET=utf81 row in set (0.00 sec)
可见 sku 字段虽然建有索引,但是为 varchar(45) 类型,但是我们 SQL 条件 where sku=8345903 以数字形式传值,导致的 数据库类型转换,用不到所建索引。
SQL 二: 查看建表语句
(:.cig:)[otter_online]> show create table dest\G*************************** 1. row *************************** Table: destCreate Table: CREATE TABLE `dest` ( `id` varchar(50) DEFAULT NULL, KEY `i_id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)id 为varchar类型,但是 SQL条件传入了数字型,导致无法使用索引,修改方法,在SQL条件上添加引号。(:.cig:)[otter_online]> select count(*) from dest where id = '1958826268315942';+----------+| count(*) |+----------+| 0 |+----------+1 row in set (0.00 sec)+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+| 1 | SIMPLE | dest | NULL | ref | i_id | i_id | 153 | const | 1 | 100.00 | Using index |+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+(:.cig:)[otter_online]> select count(*) from dest where id = 1958826268315942;+----------+| count(*) |+----------+| 0 |+----------+1 row in set, 65535 warnings (56.62 sec)+----+-------------+-------+------------+-------+---------------+------+---------+------+----------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+------+---------+------+----------+----------+--------------------------+| 1 | SIMPLE | dest | NULL | index | i_id | i_id | 153 | NULL | 39218874 | 10.00 | Using where; Using index |+----+-------------+-------+------------+-------+---------------+------+---------+------+----------+----------+--------------------------+
代码修改条件 where sku=‘8345903’; 解决。
如果字段 sku int(11), where 条件 where sku='8345903’不存在隐式转换。
数据库类型操作要一致。
来源地址:https://blog.csdn.net/u013820054/article/details/131392875
--结束END--
本文标题: MySQL-数据类型隐式转换
本文链接: https://www.lsjlt.com/news/399772.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-04-28
2024-04-28
2024-04-28
2024-04-28
2024-04-28
2024-04-28
2024-04-28
2024-04-28
2024-04-28
2024-04-28
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0