Что будет если перепутать тип таблицы в MySQL

Материал из poiuty wiki
Перейти к: навигация, поиск

И так почему же важно указывать правильный тип столбца в структуре таблицы.
Наглядный пример почему не надо делать столбцы (varchar), особенно если этот столбец задуман как (int)eger.
describe с колонкой varchar.

describe SELECT `referers`.* 
    ->  FROM `referers` 
    ->  left join ref_params on ref_params.referer_id = referers.id and ref_params.key = "cost_calc" 
    ->  left join ref_params as rp2 on rp2.referer_id = referers.id and rp2.key = "utm_banner" 
    ->  WHERE ref_params.val is null and rp2.key is not null and platform_id = 'yandexsearch' and site_id = '2747' and referers.id > 13893505 and rp2.referer_id > 13893505 and ref_params.referer_id > 13893505; 
+------+-------------+------------+--------+----------------------------------------+---------+---------+-----------------------------+---------+------------------------------------+ 
| id   | select_type | table      | type   | possible_keys                          | key     | key_len | ref                         | rows    | Extra                              | 
+------+-------------+------------+--------+----------------------------------------+---------+---------+-----------------------------+---------+------------------------------------+ 
|    1 | SIMPLE      | ref_params | ref    | qwe,referer_id                         | qwe     | 767     | const                       |   93468 | Using index condition; Using where | 
|    1 | SIMPLE      | referers   | eq_ref | PRIMARY,pid,sid,site_plat_stat,site_id | PRIMARY | 4       | stats.ref_params.referer_id |       1 | Using where                        | 
|    1 | SIMPLE      | rp2        | ref    | qwe,referer_id                         | qwe     | 767     | const                       | 1158388 | Using where; Using index           | 
+------+-------------+------------+--------+----------------------------------------+---------+---------+-----------------------------+---------+------------------------------------+ 
3 rows in set (0.00 sec)


describe с колонкой integer

MariaDB [stats]>  describe SELECT `referers`.*
    ->  FROM `referers`
    ->  left join ref_params on ref_params.referer_id = referers.id and ref_params.key = "cost_calc"
    ->  left join ref_params as rp2 on rp2.referer_id = referers.id and rp2.key = "utm_banner"
    ->  WHERE ref_params.val is null and rp2.key is not null and platform_id = 'yandexsearch' and site_id = '2747' and referers.id > 13893505;
+------+-------------+------------+--------+----------------------------------------+------+---------+-------------------------+------+------------------------------------+
| id   | select_type | table      | type   | possible_keys                          | key  | key_len | ref                     | rows | Extra                              |
+------+-------------+------------+--------+----------------------------------------+------+---------+-------------------------+------+------------------------------------+
|    1 | SIMPLE      | referers   | range  | PRIMARY,pid,sid,site_plat_stat,site_id | pid  | 771     | NULL                    | 1003 | Using index condition; Using where |
|    1 | SIMPLE      | ref_params | eq_ref | qwe,referer_id                         | qwe  | 771     | const,stats.referers.id |    1 | Using where; Not exists            |
|    1 | SIMPLE      | rp2        | eq_ref | qwe,referer_id                         | qwe  | 771     | const,stats.referers.id |    1 | Using where; Using index           |
+------+-------------+------------+--------+----------------------------------------+------+---------+-------------------------+------+------------------------------------+


мунин до и после исправления
5fc0811399c21f5873f82c7dbc53.png