阅读944 返回首页    go 阿里云 go 技术社区[云栖]

[MySQL 5.6 ]count 不出来数据，如何通过函数进行转换呢？

1、表结构：



    

  1. mysql>show create table temp3daysdata\G
    2. 

  2. *************************** 1. row ***************************
    3. 

  3.  Table: temp3daysdata
    4. 

  4. Create Table: CREATE TABLE `temp3daysdata` (
    5. 

  5.  `id` int(11) NOT NULL AUTO_INCREMENT,
    6. 

  6.  `borrow_id` int(11) DEFAULT NULL,
    7. 

  7.  `borrow_bill_id` int(11) NOT NULL,
    8. 

  8.  `paid_amount` decimal(10,2) NOT NULL,
    9. 

  9.  `deduct_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
    10. 

  10.  `alipay_order_no` varchar(45) DEFAULT NULL,
    11. 

  11.  `deduct_info` varchar(120) DEFAULT NULL,
    12. 

  12.  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    13. 

  13.  `created_by` varchar(45) NOT NULL,
    14. 

  14.  PRIMARY KEY (`id`),
    15. 

  15.  KEY `fk_borrow_bill_paid_history_borrow_bill1_idx_temp` (`borrow_bill_id`) USING BTREE,
    16. 

  16.  KEY `fk_borrow_idx_temp` (`borrow_id`) USING BTREE,
    17. 

  17.  KEY `idx_alipay_order_no_temp` (`alipay_order_no`) USING BTREE
    18. 

  18. ) ENGINE=InnoDB AUTO_INCREMENT=2539968 DEFAULT CHARSET=utf8
    19. 

  19. 共返回 1 行记录,花费 5 ms.
    20. 

  20. mysql>show create table temp0706\G
    21. 

  21. *************************** 1. row ***************************
    22. 

  22.  Table: temp0706
    23. 

  23. Create Table: CREATE TABLE `temp0706` (
    24. 

  24.  `id` bigint(20) NOT NULL,
    25. 

  25.  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    26. 

  26.  `actual_repayment_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    27. 

  27.  `paid_amount` decimal(10,2) DEFAULT NULL,
    28. 

  28.  PRIMARY KEY (`id`)
    29. 

  29. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    30. 

  30. 共返回 1 行记录,花费 5 ms.
    31. 





2、count








    

  1. mysql>select count(*) from temp0706;
    2. 

  2. +--------------------+
    3. 

  3. | count(*) |
    4. 


    5. 

  5. +--------------------+
    6. 

  6. | 10078 |
    7. 

  7. +--------------------+
    8. 

  8. 共返回 1 行记录,花费 6 ms.
    9. 

  9. mysql>select count(*) from temp3daysdata;
    10. 

  10. +--------------------+
    11. 

  11. | count(*) |
    12. 

  12. +--------------------+
    13. 

  13. | 63639 |
    14. 

  14. +--------------------+
    15. 

  15. 共返回 1 行记录,花费 13 ms.
    16. 
















    

  1. dba_jingjing@3306>[rds_test]>explain extended SELECT count(*) from `temp0706`a LEFT JOIN `temp3daysdata` b on a.`id` = b.`alipay_order_no` ;
    2. 

  2. +----+-------------+-------+-------+--------------------------+---------+---------+------+-------+----------+------------------------------------------------+
    3. 

  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    4. 

  4. +----+-------------+-------+-------+--------------------------+---------+---------+------+-------+----------+------------------------------------------------+
    5. 

  5. | 1 | SIMPLE | a | index | NULL | PRIMARY | 8 | NULL | 10048 | 100.00 | Using index |
    6. 

  6. | 1 | SIMPLE | b | ALL | idx_alipay_order_no_temp | NULL | NULL | NULL | 53721 | 100.00 | Range checked for each record (index map: 0x8) |
    7. 

  7. +----+-------------+-------+-------+--------------------------+---------+---------+------+-------+----------+------------------------------------------------+
    8. 

  8. 2 rows in set, 2 warnings (0.03 sec)
    9. 

    10. 

  10. dba_jingjing@3306>[rds_test]>show warnings\G
    11. 

  11. *************************** 1. row ***************************
    12. 

  12.  Level: Warning
    13. 

  13.  Code: 1739
    14. 

  14. Message: Cannot use ref access on index 'idx_alipay_order_no_temp' due to type or collation conversion on field 'alipay_order_no'
    15. 

  15. *************************** 2. row ***************************
    16. 

  16.  Level: Note
    17. 

  17.  Code: 1003
    18. 

  18. Message: /* select#1 */ select count(0) AS `count(*)` from `rds_test`.`temp0706` `a` left join `rds_test`.`temp3daysdata` `b` on((`rds_test`.`a`.`id` = `rds_test`.`b`.`alipay_order_no`)) where 1
    19. 

  19. 2 rows in set (0.03 sec)
    20. 

    21. 

  21. dba_jingjing@3306>[rds_test]>explain extended SELECT count(*) from `temp0706`a LEFT JOIN `temp3daysdata` b on a.`id` = b.`alipay_order_no` WHERE a.id=b.`alipay_order_no`;
    22. 

  22. +----+-------------+-------+--------+--------------------------+--------------------------+---------+----------------------------+-------+----------+--------------------------+
    23. 

  23. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    24. 

  24. +----+-------------+-------+--------+--------------------------+--------------------------+---------+----------------------------+-------+----------+--------------------------+
    25. 

  25. | 1 | SIMPLE | b | index | idx_alipay_order_no_temp | idx_alipay_order_no_temp | 138 | NULL | 53721 | 100.00 | Using where; Using index |
    26. 

  26. | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 8 | rds_test.b.alipay_order_no | 1 | 100.00 | Using where; Using index |
    27. 

  27. +----+-------------+-------+--------+--------------------------+--------------------------+---------+----------------------------+-------+----------+--------------------------+
    28. 

  28. 2 rows in set, 3 warnings (0.03 sec)
    29. 

    30. 

  30. dba_jingjing@3306>[rds_test]>show warnings\G
    31. 

  31. *************************** 1. row ***************************
    32. 

  32.  Level: Warning
    33. 

  33.  Code: 1739
    34. 

  34. Message: Cannot use ref access on index 'idx_alipay_order_no_temp' due to type or collation conversion on field 'alipay_order_no'
    35. 

  35. *************************** 2. row ***************************
    36. 

  36.  Level: Warning
    37. 

  37.  Code: 1739
    38. 

  38. Message: Cannot use ref access on index 'idx_alipay_order_no_temp' due to type or collation conversion on field 'alipay_order_no'
    39. 

  39. *************************** 3. row ***************************
    40. 

  40.  Level: Note
    41. 

  41.  Code: 1003
    42. 

  42. Message: /* select#1 */ select count(0) AS `count(*)` from `rds_test`.`temp0706` `a` join `rds_test`.`temp3daysdata` `b` where ((`rds_test`.`a`.`id` = `rds_test`.`b`.`alipay_order_no`) and (`rds_test`.`a`.`id` = `rds_test`.`b`.`alipay_order_no`))
    43. 

  43. 3 rows in set (0.03 sec)
    44. 










55996452.png








56192312.png






mysql>show warnings\G


*************************** 1. row ***************************


  Level: Note


   Code: 1003


Message: /* select#1 */ select count(0) AS `count(*)` from `bi`.`temp0706` `a` left join `bi`.`temp3daysdata` `b` on((cast(`bi`.`a`.`id` as char charset utf8mb4) = convert(`bi`.`b`.`alipay_order_no` using utf8mb4))) where 1


共返回 1 行记录,花费 5 ms.












SELECT  count(*)


  from `temp0706`a


  LEFT JOIN `temp3daysdata` b on CAST( a.`id` AS CHAR charset utf8 ) = b.`alipay_order_no`






56222395.png










    

  1. mysql>explain extended
    2. 

  2. SELECT count(*)
    3. 

  3.  from `temp0706`a
    4. 

  4.  LEFT JOIN `temp3daysdata` b on CAST( a.`id` AS CHAR) = b.`alipay_order_no`;
    5. 

  5. +--------------+-----------------------+-----------------+----------------+-------------------------+--------------------------+-------------------+---------------+----------------+--------------------+-----------------------------------------------------------------+
    6. 

  6. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    7. 

  7. +--------------+-----------------------+-----------------+----------------+-------------------------+--------------------------+-------------------+---------------+----------------+--------------------+-----------------------------------------------------------------+
    8. 

  8. | 1 | SIMPLE | a | index | | PRIMARY | 8 | | 10236 | 100 | Using index |
    9. 

  9. | 1 | SIMPLE | b | index | | idx_alipay_order_no_temp | 138 | | 63361 | 100 | Using where; Using index; Using join buffer (Block Nested Loop) |
    10. 

  10. +--------------+-----------------------+-----------------+----------------+-------------------------+--------------------------+-------------------+---------------+----------------+--------------------+-----------------------------------------------------------------+
    11. 

  11. 共返回 2 行记录,花费 5 ms.
    12. 

  12. mysql>show warnings\G
    13. 

  13. *************************** 1. row ***************************
    14. 

  14.  Level: Note
    15. 

  15.  Code: 1003
    16. 

  16. Message: /* select#1 */ select count(0) AS `count(*)` from `bi`.`temp0706` `a` left join `bi`.`temp3daysdata` `b` on((cast(`bi`.`a`.`id` as char charset utf8mb4) = convert(`bi`.`b`.`alipay_order_no` using utf8mb4))) where 1
    17. 

  17. 共返回 1 行记录,花费 5 ms.
    18. 

  18. mysql>explain 
    19. 

  19. SELECT count(*)
    20. 

  20.  from `temp0706`a
    21. 

  21.  LEFT JOIN `temp3daysdata` b on CAST( a.`id` AS CHAR charset utf8 ) = b.`alipay_order_no`
    22. 

  22. +--------------+-----------------------+-----------------+----------------+--------------------------+--------------------------+-------------------+---------------+----------------+--------------------------+
    23. 

  23. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    24. 

  24. +--------------+-----------------------+-----------------+----------------+--------------------------+--------------------------+-------------------+---------------+----------------+--------------------------+
    25. 

  25. | 1 | SIMPLE | a | index | | PRIMARY | 8 | | 10236 | Using index |
    26. 

  26. | 1 | SIMPLE | b | ref | idx_alipay_order_no_temp | idx_alipay_order_no_temp | 138 | func | 1 | Using where; Using index |
    27. 

  27. +--------------+-----------------------+-----------------+----------------+--------------------------+--------------------------+-------------------+---------------+----------------+--------------------------+
    28. 

  28. 共返回 2 行记录,花费 5 ms.
    29. 

  29. mysql>SELECT count(*)
    30. 

  30.  from `temp0706`a
    31. 

  31.  LEFT JOIN `temp3daysdata` b on CAST( a.`id` AS CHAR charset utf8 ) = b.`alipay_order_no`
    32. 

  32. +--------------------+
    33. 

  33. | count(*) |
    34. 

  34. +--------------------+
    35. 

  35. | 10078 |
    36. 

  36. +--------------------+
    37. 

  37. 共返回 1 行记录,花费 39 ms.
    38. 












dba_jingjing@3306>[rds_test]>explain extended


    -> SELECT  count(*)


    ->   from `temp0706`a


    ->   LEFT JOIN `temp3daysdata` b on CAST( a.`id` AS CHAR  ) = b.`alipay_order_no`


    ->


    -> ;


+----+-------------+-------+-------+--------------------------+--------------------------+---------+------+-------+----------+--------------------------+


| id | select_type | table | type  | possible_keys            | key                      | key_len | ref  | rows  | filtered | Extra                    |


+----+-------------+-------+-------+--------------------------+--------------------------+---------+------+-------+----------+--------------------------+


|  1 | SIMPLE      | a     | index | NULL                     | PRIMARY                  | 8       | NULL | 10048 |   100.00 | Using index              |


|  1 | SIMPLE      | b     | ref   | idx_alipay_order_no_temp | idx_alipay_order_no_temp | 138     | func |     1 |   100.00 | Using where; Using index |


+----+-------------+-------+-------+--------------------------+--------------------------+---------+------+-------+----------+--------------------------+


2 rows in set, 1 warning (0.04 sec)





dba_jingjing@3306>[rds_test]>show warnings\G


*************************** 1. row ***************************


  Level: Note


   Code: 1003


Message: /* select#1 */ select count(0) AS `count(*)` from `rds_test`.`temp0706` `a` left join `rds_test`.`temp3daysdata` `b` on((convert(cast(`rds_test`.`a`.`id` as char charset latin1) using utf8) = `rds_test`.`b`.`alipay_order_no`)) where 1


1 row in set (0.04 sec)





dba_jingjing@3306>[rds_test]>  SELECT  count(*)   from `temp0706`a   LEFT JOIN `temp3daysdata` b on CAST( a.`id` AS CHAR  ) = b.`alipay_order_no`;


+----------+


| count(*) |


+----------+


|    10000 |


+----------+


1 row in set (0.07 sec)





dba_jingjing@3306>[rds_test]>  SELECT  count(*)   from `temp0706`a   LEFT JOIN `temp3daysdata` b on CAST( a.`id` AS CHAR  charset utf8  ) = b.`alipay_order_no`;


+----------+


| count(*) |


+----------+


|    10000 |


+----------+


1 row in set (0.06 sec)






最后更新：2017-08-13 22:35:15
  上一篇：go  云市场合作伙伴目睹获近亿元融资，开启1元企业活动直播试用！
  下一篇：go  国外通过3D打印技术设计微型装置，让我们再也不用担心用水安全