#头æ¡åä½ææèµ#
helloï¼å¤§å®¶å¥½ï¼ææ¯å¼ å¼ ï¼ãæ¶æç²¾è¿ä¹è·¯ãå ¬å·ä½è ã
æ¬æçé®é¢èæ¯ä¸ºè¿æå·¥ä½ä¸åäºéå°çä¸ä¸ªçå®é®é¢ï¼å¨æ¤ç¨ä½æ´çåæ»ç»å享ç»å¤§å®¶ï¼ä»¥ä¾å¦ä¹ åèï¼è°¨é²å ¥å~
1ãé®é¢å¼ç¯
ä¸å¼ ç¨æ·è¡¨ `users` ï¼å ¶ä¸å段 `phone` æ·»å äºæ®éç´¢å¼ã
CREATE TABLE users (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主é®ID',
name varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'å称',
phone varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'ææº',
created_at timestamp NOT NULL DEFAULT '1970-01-01 16:00:00' COMMENT 'å建æ¶é´',
updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'æ´æ°æ¶é´',
PRIMARY KEY (id),
KEY idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ç¨æ·è¡¨';
åå«æ§è¡ä»¥ä¸SQLï¼
1ï¼å符串类åæ¥è¯¢
EXPLAIN SELECT * FROM users WHERE phone = '2';
æ§è¡è®¡åå¦ä¸ï¼
2ï¼æ°å¼åæ¥è¯¢
EXPLAIN SELECT * FROM users WHERE phone = 2;
æ§è¡è®¡åå¦ä¸ï¼
åç°é®é¢ï¼
å½ç´¢å¼å段 `phone` 为å符串类åæ¶ï¼å符串æ¥è¯¢æ¶å使ç¨äºç´¢å¼`idx_phone`ï¼èæ°å¼ç±»åæ¥è¯¢æ¶åç«æ æ³ä½¿ç¨ç´¢å¼`idx_phone`ã
2ãé®é¢å¼ç³
åå¦ç´¢å¼å段为æ´åçè¯ï¼é£ç¨å符串æ¥è¯¢æ¶ä¼ä¸ä¼èµ°ç´¢å¼å¢ï¼
å®è·µåºçç¥ï¼æ们æ¥éªè¯ä¸ä¸ã
åæ ·å¦ä¸è¡¨ï¼ä¿®æ¹å段 `phone` ç±»åç± varchar åæ´ä¸º bigintï¼
ALTER TABLE users MODIFY COLUMN phone bigint(16) NOT NULL COMMENT 'ææº';
ç¶åï¼åå«æ§è¡ä»¥ä¸SQLï¼
1ï¼å符串类åæ¥è¯¢
EXPLAIN SELECT * FROM users WHERE phone = '2';
æ§è¡è®¡åå¦ä¸ï¼
2ï¼æ°å¼åæ¥è¯¢
EXPLAIN SELECT * FROM users WHERE phone = 2;
æ§è¡è®¡åå¦ä¸ï¼
æ§è¡ååç°ï¼æ 论æ¯ä»¥å符串æ¥è¯¢è¿æ¯ä»¥æ°å¼åæ¥è¯¢é½ä¼ç¨å°ç´¢å¼ã
å°ç»ï¼
- å½ç´¢å¼å段æ¯æ°å¼ç±»åæ¶ï¼æ°å¼åæè å符åæ¥è¯¢é½ä¸å½±åç´¢å¼ç使ç¨ã
- å½ç´¢å¼å段æ¯å符类åæ¶ï¼æ°å¼åæ¥è¯¢æ æ³ä½¿ç¨ç´¢å¼ï¼å符åæ¥è¯¢å¯æ£å¸¸ä½¿ç¨ç´¢å¼ã
3ãè·è¿æ¢ç©¶
为ä»ä¹ä¼æ¯è¿æ ·å¢ï¼å ¶æ ¹æºå°±æ¯MySQLçéå¼ç±»å转æ¢ã
3.1 ä»ä¹æ¯éå¼ç±»å转æ¢ï¼
å¨MySQLä¸ï¼å½æä½ç¬¦ä¸ä¸åç±»åçæä½æ°ä¸èµ·ä½¿ç¨æ¶ï¼ä¼åçç±»å转æ¢ä»¥ä½¿æä½æ°å ¼å®¹ï¼åä¼åçéå¼ç±»å转æ¢ã
å³ MySQLä¼æ ¹æ®éè¦èªå¨å°æ°å转æ¢ä¸ºå符串ï¼æè å°å符串转æ¢ä¸ºæ°åã
mysql> SELECT 1+'1';
-> 2
mysql> SELECT CONCAT(2,' test');
-> '2 test'
å¾ææ¾ï¼ä¸é¢çSQLè¯å¥çæ§è¡è¿ç¨ä¸å°±åºç°äºéå¼è½¬åã
ä»ç»ææ们å¯ä»¥å¤å®ï¼SQL1ä¸å°å符串çâ1â转æ¢ä¸ºæ°å1ï¼èå¨SQL2 ä¸ï¼å°æ°å2转æ¢ä¸ºå符串â2âã
3.2 å¦ä½é¿å éå¼ç±»å转æ¢ï¼
3.2.1 æ¸ æ¥è½¬æ¢è§å
åªæå½æ¸ æ¥çç¥ééå¼ç±»å转æ¢çè§åï¼æè½ä»æ ¹æ¬ä¸é¿å 产çéå¼ç±»å转æ¢ã
åèMySQLææ¡£ç¸å ³æè¿°ï¼ç¡®å®éå¼ç±»å转æ¢è§åï¼
1ã两个åæ°è³å°æä¸ä¸ªæ¯ NULL æ¶ï¼æ¯è¾çç»æä¹æ¯ NULLï¼ä¾å¤æ¯ä½¿ç¨ <=> 对两个 NULL åæ¯è¾æ¶ä¼è¿å 1ï¼è¿ä¸¤ç§æ
åµé½ä¸éè¦åç±»å转æ¢
2ã两个åæ°é½æ¯å符串ï¼ä¼æç
§å符串æ¥æ¯è¾ï¼ä¸åç±»å转æ¢
3ã两个åæ°é½æ¯æ´æ°ï¼æç
§æ´æ°æ¥æ¯è¾ï¼ä¸åç±»å转æ¢
4ãåå
è¿å¶çå¼åéæ°ååæ¯è¾æ¶ï¼ä¼è¢«å½åäºè¿å¶ä¸²
5ãæä¸ä¸ªåæ°æ¯ TIMESTAMP æ DATETIMEï¼å¹¶ä¸å¦å¤ä¸ä¸ªåæ°æ¯å¸¸éï¼å¸¸éä¼è¢«è½¬æ¢ä¸º timestamp
6ãæä¸ä¸ªåæ°æ¯ decimal ç±»åï¼å¦æå¦å¤ä¸ä¸ªåæ°æ¯ decimal æè
æ´æ°ï¼ä¼å°æ´æ°è½¬æ¢ä¸º decimal åè¿è¡æ¯è¾ï¼å¦æå¦å¤ä¸ä¸ªåæ°æ¯æµ®ç¹æ°ï¼åä¼æ decimal 转æ¢ä¸ºæµ®ç¹æ°è¿è¡æ¯è¾
7ãææå
¶ä»æ
åµä¸ï¼ä¸¤ä¸ªåæ°é½ä¼è¢«è½¬æ¢ä¸ºæµ®ç¹æ°åè¿è¡æ¯è¾
éªè¯ç¤ºä¾ï¼
mysql> SELECT 'aa' + 1;
-> '1'
mysql> show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'aa' |
+---------+------+----------------------------------------+
ä¸è¿°ç¤ºä¾ä¸ï¼å°å符串 'aa' å1è¿è¡æ±åï¼å 为 'aa' åæ°å1çç±»åä¸åï¼éè¿ä¸è¿°è½¬æ¢è§å并ä¸ç»æ¥çwarningså¯ä»¥ç¡®è®¤ï¼éå¼ç±»å转åå°åç¬¦ä¸²è½¬ä¸ºäº double ç±»åã
ç±äºå符串æ¯éæ°ååçï¼æ以就ä¼è¢«è½¬æ¢ä¸º0ï¼å æ¤è®¡ç®ç»æï¼0+1=1
3.2.2 使ç¨å ç½®å½æ°æ¾ç¤ºè½¬æ¢
MySQL对æ°æ®è¿è¡ç±»å转æ¢ï¼æä¾äºcast() å convert()ã
ç¸åç¹ï¼ä¸¤è é½æ¯è¿è¡æ°æ®ç±»å转æ¢ï¼å®ç°çåè½åºæ¬çå
ä¸åç¹ï¼ä¸¤è çè¯æ³ä¸åï¼cast(value as type) ã convert(value,type)
å°æ°å¼å转æ¢ä¸ºå符串åï¼åºç¨ç¤ºä¾å¦ä¸ï¼
mysql> SELECT CAST(123 as char);
-> '123'
mysql> SELECT CONVERT(123, char);
-> '123'
åå¦åºç¨å¨å¼ç¯æè¿°é®é¢çæ¥è¯¢ä¸ï¼åå¦ä¸æ示ï¼
EXPLAIN SELECT * FROM users WHERE phone = CAST(123 AS CHAR);
æ§è¡è®¡åæ示ï¼
ç»ææ¾ç¤ºååºç¨å符串类ååæ°ä¸æ ·ï¼å¯ä½¿ç¨ç´¢å¼`idx_phone`ã
3.2.3 ç±»åä¿æä¸è´
æç®åçä¸ç§ï¼ä¿è¯æ¥è¯¢åºç¨è§èï¼SQLåæ°ç±»åä¸æ°æ®åºä¸å段类åä¿æä¸è´å³å¯ã
3.3 å符类å转æ¢
å¦å¤ï¼å ³äºå符串类å转æ¢çä¸äºè¡¥å ï¼
mysql> select '1a2b3c' = 1;
-> 1
mysql> select 'a1b2c3' = 0;
-> 1
ä»ä¸é¢çä¾åå¯ä»¥å¾åºï¼
- å¦æå符串ç第ä¸ä¸ªå符就æ¯éæ°åçå符ï¼é£ä¹è½¬æ¢ä¸ºæ°åå°±æ¯0ï¼
- å¦æå符串以æ°åå¼å¤´ï¼é£è½¬æ¢çæ°åå°±æ¯å¼å¤´çé£äºæ°å对åºçå¼ï¼ç´å°éå°éæ°åå符æç»æã
4ãæ»ç»
æ¬æ主è¦ä»é®é¢å ¥æï¼ç»§èè¿è¡é®é¢å¼ç³ï¼æç»ææåºé®é¢æ ¹æºï¼MySQLéå¼ç±»å转æ¢ã
åæ¶ä¹å诫æ们æ¥å¸¸å¨åSQLæ¶ä¸å®è¦æ£æ¥åæ°ç±»åä¸æ°æ®åºå段类åæ¯å¦ä¸è´ï¼å¦åå¯è½é æéå¼ç±»å转æ¢ï¼ä¸è½æ£å¸¸åºç¨ç´¢å¼ï¼é ææ ¢æ¥è¯¢ï¼çè³æå®æ´ä¸ªæ°æ®åºæå¡é群ã
å¦æåæ°ä¸ä¸è´ï¼ä¹å¯ä»¥èè使ç¨CASTå½æ°æ¾æ§è½¬æ¢æä¸è´ç±»åã
æ°æ®è¡¨è®¾è®¡ååºç¨ç»éæäºï¼éè¦èèçå ç´ å¤ªå¤äºï¼å¤§å®¶åºç¨è¿ç¨æ³¨æä¿ææ¬çå¿ã
å¸æä»å¤©ç讲解对大家ææ帮å©ï¼è°¢è°¢ï¼
Thanks for reading!
ä½è ï¼æ¶æç²¾è¿ä¹è·¯ï¼åå¹´ç åé£é¨è·¯ï¼å¤§åæ¶æå¸ï¼CSDN å客ä¸å®¶ï¼ä¸æ³¨æ¶æææ¯æ²æ·å¦ä¹ åå享ï¼èä¸ä¸è®¤ç¥å级ï¼åæå享æ¥å°æ°å¿ç干货æç« ï¼æå¾ ä¸ä½ ä¸èµ·æé¿ã
å ³æ³¨å¹¶ç§ä¿¡æåå¤â01âï¼éä½ ä¸ä»½ç¨åºåæé¿è¿é¶å¤§ç¤¼å ï¼æ¬¢è¿å¾æã