閱讀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打印技術設計微型裝置，讓我們再也不用擔心用水安全