mysql 关联查询 索引失效

接手一个新项目数据导出需求 大致sql如下 select b.* from wms_sku_uuids a inner JOIN wms_entity_operate_log b on a.uuid=b.entity_code where a.uuid="00617531" and a.`status`=9 附表大约650w条记录 explain 发现附表每次都是全量查找 怀疑是不是sql语句的问题 于是换成where in 子查询的方式附表还是全量 show create table   查询都覆盖到的索引 百度后发现数据类型不同或相同类型字符编码不同会造成不走索引于是 SHOW FULL COLUMNS FROM wms_sku_uuids; 1556009056879 发现字符编码的确不同  字符编码同步后 1556009283207  

mysql 批量建表 存储过程

delimiter // CREATE procedure create_table() BEGIN DECLARE `@i` int(11); DECLARE `@sqlstr` varchar(65000); SET `@i`=0; WHILE `@i` < 256 DO SET @sqlstr = CONCAT( "CREATE TABLE box_item_pool_", `@i`, "( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `_unique` varchar(64) NOT NULL DEFAULT '' COMMENT '分表后唯一标示', `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户ID', `box_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '盒子ID', `prod_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商品ID, 目前是product_master.id', `prod_code` varchar(32) NOT NULL DEFAULT '' COMMENT '商品CODE, 目前是product_master.code', `product_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'product.id', `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, `deleted_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `_unique` (`_unique`) USING BTREE, KEY `user_box` (`user_id`,`box_id`), KEY `box` (`box_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;" ); prepare stmt from @sqlstr; execute stmt; SET `@i` = `@i` + 1; END WHILE; END; call create_table(); drop procedure create_table; //

mysql Incorrect datetime value

从本地 迁移数据库到测试服 发现sql报错 MySQL []> INSERT INTO `table` VALUES (1, 1, 1, 1, 0, 0, 0, 0, 0, '0000-00-00 00:00:00', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '2017-08-31 11:41:22', '2017-08-31 11:41:26', 0); ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'rental_days_at' at row 1 MySQL [nparis]> select @@sql_mode; +-------------------------------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.41 sec) MySQL []> set @@sql_mode='no_engine_substitution'; Query OK, 0 rows affected, 1 warning (0.07 sec) MySQL []> INSERT INTO `table` VALUES (1, 1, 1, 1, 0, 0, 0, 0, 0, '0000-00-00 00:00:00', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '2017-08-31 11:41:22', '2017-08-31 11:41:26', 0); Query OK, 1 row affected (0.56 sec) 出自 https://dba.stackexchange.com/questions/48704/mysql-5-6-datetime-incorrect-datetime-value-2013-08-25t1700000000-with-er

ubuntu 16.10 重置mysql5.7密码

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf [mysqld]下加入"skip-grant-tables" sudo /etc/init.d/mysql restart mysql -u root update mysql.user set authentication_string=password('123456') where user='root';(mysql5.7取消了password字段) update user set plugin="mysql_native_password"; flush privileges; exit; 注释 [mysqld]下加入"skip-grant-tables" sudo /etc/init.d/mysql restart mysql -u root -p123456

mysql分组取每组前几条记录(排序)

  -- ---------------------------- -- Table structure for mygoods -- ---------------------------- DROP TABLE IF EXISTS `mygoods`; CREATE TABLE `mygoods` ( `goods_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `cat_id` int(11) NOT NULL DEFAULT '0', `price` tinyint(3) NOT NULL DEFAULT '0', `status` tinyint(3) DEFAULT '1', PRIMARY KEY (`goods_id`), KEY `icatid` (`cat_id`) ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of mygoods -- ---------------------------- INSERT INTO `mygoods` VALUES ('1', '101', '90', '0'); INSERT INTO `mygoods` VALUES ('2', '101', '99', '1'); INSERT INTO `mygoods` VALUES ('3', '102', '98', '0'); INSERT INTO `mygoods` VALUES ('4', '103', '96', '0'); INSERT INTO `mygoods` VALUES ('5', '102', '95', '0'); INSERT INTO `mygoods` VALUES ('6', '102', '94', '1'); INSERT INTO `mygoods` VALUES ('7', '102', '93', '1'); INSERT INTO `mygoods` VALUES ('8', '103', '99', '1'); INSERT INTO `mygoods` VALUES ('9', '103', '98', '1'); INSERT INTO `mygoods` VALUES ('10', '103', '97', '1'); INSERT INTO `mygoods` VALUES ('11', '104', '96', '1'); INSERT INTO `mygoods` VALUES ('12', '104', '95', '1'); INSERT INTO `mygoods` VALUES ('13', '104', '94', '1'); INSERT INTO `mygoods` VALUES ('15', '101', '92', '1'); INSERT INTO `mygoods` VALUES ('16', '101', '93', '1'); INSERT INTO `mygoods` VALUES ('17', '101', '94', '0'); INSERT INTO `mygoods` VALUES ('18', '102', '99', '1'); INSERT INTO `mygoods` VALUES ('19', '105', '85', '1'); INSERT INTO `mygoods` VALUES ('20', '105', '89', '0'); INSERT INTO `mygoods` VALUES ('21', '105', '99', '1');
说明: 表mygoods为商品表,cat_id为分类id,goods_id为商品id,status为商品当前的状态位(1:有效,0:无效)。 需求:每个分类下,找出两个价格最高的有效的商品。 1.每个分类找出价格最高的两个商品
mysql> select a.*   
    -> from mygoods a   
    -> where (select count(*) 
    -> from mygoods 
    -> where cat_id = a.cat_id and price > a.price  ) <2 
    -> order by a.cat_id,a.price desc;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
|        2 |    101 |    99 |      1 |
|       17 |    101 |    94 |      0 |
|       18 |    102 |    99 |      1 |
|        3 |    102 |    98 |      0 |
|        8 |    103 |    99 |      1 |
|        9 |    103 |    98 |      1 |
|       11 |    104 |    96 |      1 |
|       12 |    104 |    95 |      1 |
|       21 |    105 |    99 |      1 |
|       20 |    105 |    89 |      0 |
+----------+--------+-------+--------+
10 rows in set (0.00 sec)
2.每个分类找出价格最高的有效的两个商品(正确)
mysql> select a.* 
    -> from mygoods a 
    -> where (select count(*) from mygoods 
    -> where cat_id = a.cat_id and price > a.price and status=1  ) <2 
    -> and status=1 
    -> order by a.cat_id,a.price desc ;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
|        2 |    101 |    99 |      1 |
|       16 |    101 |    93 |      1 |
|       18 |    102 |    99 |      1 |
|        6 |    102 |    94 |      1 |
|        8 |    103 |    99 |      1 |
|        9 |    103 |    98 |      1 |
|       11 |    104 |    96 |      1 |
|       12 |    104 |    95 |      1 |
|       21 |    105 |    99 |      1 |
|       19 |    105 |    85 |      1 |
+----------+--------+-------+--------+
10 rows in set (0.00 sec)
3.每个分类找出价格最高的有效的两个商品(正确)
mysql> select a.* 
    -> from mygoods a 
    -> left join mygoods b 
    -> on a.cat_id = b.cat_id and a.price < b.price and b.status=1
    -> where a.status=1
    -> group by a.goods_id,a.cat_id,a.price
    -> having count(b.goods_id) < 2
    -> order by a.cat_id,a.price desc;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
|        2 |    101 |    99 |      1 | 
|       16 |    101 |    93 |      1 | 
|       18 |    102 |    99 |      1 | 
|        6 |    102 |    94 |      1 | 
|        8 |    103 |    99 |      1 | 
|        9 |    103 |    98 |      1 | 
|       11 |    104 |    96 |      1 | 
|       12 |    104 |    95 |      1 | 
|       21 |    105 |    99 |      1 | 
|       19 |    105 |    85 |      1 | 
+----------+--------+-------+--------+
10 rows in set (0.00 sec)
4.每个分类找出价格最高的有效的两个商品(错误)
mysql> select a.* 
    -> from mygoods a 
    -> where (select count(*) from mygoods 
    -> where cat_id = a.cat_id and price > a.price  ) <2 and status=1 
    -> order by a.cat_id,a.price desc;
+----------+--------+-------+--------+
| goods_id | cat_id | price | status |
+----------+--------+-------+--------+
|        2 |    101 |    99 |      1 |
|       18 |    102 |    99 |      1 |
|        8 |    103 |    99 |      1 |
|        9 |    103 |    98 |      1 |
|       11 |    104 |    96 |      1 |
|       12 |    104 |    95 |      1 |
|       21 |    105 |    99 |      1 |
+----------+--------+-------+--------+
7 rows in set (0.00 sec)
由上可知,如果需要增加条件的话,需要在两处增加条件。
可以将每个分组下的goods_id合并。
mysql> select cat_id,GROUP_CONCAT(goods_id) from mygoods group by cat_id;
+--------+------------------------+
| cat_id | GROUP_CONCAT(goods_id) |
+--------+------------------------+
|    101 | 1,2,15,16,17           |
|    102 | 3,5,6,7,18             |
|    103 | 4,8,9,10               |
|    104 | 11,12,13               |
|    105 | 19,20,21               |
+--------+------------------------+
5 rows in set (0.00 sec)
转自 http://blog.sina.com.cn/s/blog_4c197d420101e408.html

MySQL concat函数的使用

MySQL concat函数是MySQL数据库中众多的函数之一,下文将对MySQL concat函数的语法和使用进行说明,供您参考和学习。 MySQL concat函数使用方法: CONCAT(str1,str2,…) 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NULL。 注意: 如果所有参数均为非二进制字符串,则结果为非二进制字符串。 如果自变量中含有任一二进制字符串,则结果为一个二进制字符串。 一个数字参数被转化为与之相等的二进制字符串格式;若要避免这种情况,可使用显式类型 cast, 例如: SELECT CONCAT(CAST(int_col AS CHAR), char_col) MySQL concat函数可以连接一个或者多个字符串,如
  1. mysql> select concat('10');
  2. +--------------+
  3. | concat('10') |
  4. +--------------+
  5. | 10   |
  6. +--------------+
  7. 1 row in set (0.00 sec)
  8. mysql> select concat('11','22','33');
  9. +------------------------+
  10. | concat('11','22','33') |
  11. +------------------------+
  12. | 112233 |
  13. +------------------------+
  14. 1 row in set (0.00 sec)
MySQL的concat函数在连接字符串的时候,只要其中一个是NULL,那么将返回NULL
  1. mysql> select concat('11','22',null);
  2. +------------------------+
  3. | concat('11','22',null) |
  4. +------------------------+
  5. | NULL   |
  6. +------------------------+
  7. 1 row in set (0.00 sec)

MYSQL IFNULL函数的使用

下文对MYSQL IFNULL函数的使用进行了详细的叙述,供您参考学习,如果您在MYSQL IFNULL函数使用方面遇到过类似的问题,不妨一看。 MYSQL IFNULL(expr1,expr2) 如果expr1不是NULL,IFNULL()返回expr1,否则它返回expr2。IFNULL()返回一个数字或字符串值,取决于它被使用的上下文环境。 mysql> select IFNULL(1,0); -> 1 mysql> select IFNULL(0,10); -> 0 mysql> select IFNULL(1/0,10); -> 10 mysql> select IFNULL(1/0,yes); -> yes IF(expr1,expr2,expr3) 如果expr1是TRUE(expr1<>0且expr1<>NULL),那么IF()返回expr2,否则它返回expr3。IF()返回一个数字或字符串值,取决于它被使用的上下文。 mysql> select IF(1>2,2,3); -> 3 mysql> select IF(1<2,yes,no); -> yes mysql> select IF(strcmp(test,test1),yes,no); -> no expr1作为整数值被计算,它意味着如果你正在测试浮点或字符串值,你应该使用一个比较操作来做。 mysql> select IF(0.1,1,0); -> 0 mysql> select IF(0.1<>0,1,0); -> 1 在上面的第一种情况中,IF(0.1)返回0,因为0.1被变换到整数值, 导致测试IF(0)。这可能不是你期望的。在第二种情况中,比较测试原来的浮点值看它是否是非零,比较的结果被用作一个整数。 CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END 第一个版本返回result,其中value=compare-value。第二个版本中如果第一个条件为真,返回result。如果没有匹配的result值,那么结果在ELSE后的result被返回。如果没有ELSE部分,那么NULL被返回。 mysql> SELECT CASE 1 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END; -> "one" mysql> SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END; -> "true" mysql> SELECT CASE BINARY "B" when "a" then 1 when "b" then 2 END; -> NULL