åè¨
对
select into
è¯å¥æå ´è¶£æ¯å 为çäºé¡¹ç®ä¸çä¸ä¸ªåå¨è¿ç¨å¼èµ·çï¼å¨ç¨åºè¿è¡ä¹åçäºåå¨è¿ç¨çé»è¾ï¼æ¬ä»¥ä¸ºæ²¡ææ°æ®æ¶ä¼æ¥éï¼ç»æç¨åºå´æ£å¸¸è¿è¡ï¼è¿è¯´ææ对
select into
è¯å¥ç解çé®é¢ï¼åæ¶ä¹æ´é²äºä¸ä¸ªç¥è¯ç²ç¹ï¼æ以åäºä¸ªå°ä¾åæµè¯ä¸ä¸ï¼å¹¶ææµè¯çè¿ç¨è®°å½æ¹ä¾¿æ¥åæ¥æ¾ã
å建æµè¯è¡¨æ ¼
为äºæ´æ¸ æ¥ç表æé®é¢ï¼æ们å建çè¡¨æ ¼å°½å¯è½çç®åï¼åæ¶ä¸ºäºæµè¯ç©ºå¼çæ åµï¼æ°æ®åæ们ä¸è®¾ç½®é»è®¤å¼ï¼è¡¨æ ¼å½å为âintotestâï¼å建è¯å¥å¦ä¸ï¼
CREATE TABLE `intotest` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`number` int(4),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=DYNAMIC;
æå ¥æµè¯æ°æ®
è¡¨æ ¼å»ºç«å®æå¯ä»¥ä½¿ç¨å¯è§åå·¥å ·æè insertè¯å¥æå ¥æµè¯æ°æ®ï¼æå ¥æµè¯æ°æ®åæ¥è¯¢ç»æå¦ä¸ï¼
mysql> select * from intotest;
+----+--------+
| id | number |
+----+--------+
| 1 | 1 |
| 2 | 2 |
| 3 | NULL |
+----+--------+
3 rows in set (0.00 sec)
建ç«ä¸ä¸ªåå¨è¿ç¨
æ们建ç«ä¸ä¸ªç¨äºæµè¯çåå¨è¿ç¨ï¼ä¸»è¦çé»è¾å°±æ¯ççå½
select into
è¯å¥æ¾ä¸å°å¹é è®°å½æ¶ï¼è¢«èµå¼çåéä¼æä¹æ ·ï¼å»ºç«åå¨è¿ç¨ç代ç å¦ä¸ï¼
CREATE PROCEDURE `select_into_value2`()
BEGIN
DECLARE _value INT DEFAULT 0;
SELECT number FROM intotest WHERE id=1 INTO _value;
SELECT _value;
END
è¿ä¸ªåå¨è¿ç¨è¿è¡æ£å¸¸ï¼é ååææ们æå ¥è¡¨æ ¼çè®°å½å¯ä»¥ç¥éï¼è¿è¡åçç»æ为1:
mysql> call select_into_value();
+--------+
| _value |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
æµè¯è¿ç¨
- å½æ¥è¯¢ç»æä¸ä¸åå¨ç¬¦åæ¡ä»¶çè®°å½æ¶ä¼ææ ·ï¼ä¿®æ¹åå¨è¿ç¨å®ä¹ï¼ç¶åæ¥çè¿è¡ç»æï¼
CREATE PROCEDURE `select_into_value2`()
BEGIN
DECLARE _value INT DEFAULT 0;
SELECT number FROM intotest WHERE id=5 INTO _value;
SELECT _value;
END
mysql> call select_into_value();
+--------+
| _value |
+--------+
| 0 |
+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ç»æ为0ï¼ä¹å°±æ¯è¯´å½æ¥ä¸å°å¹é ç»ææ¶ï¼ä¸ä¼æ§è¡
select into
çèµå¼ææã
- å½å¹é å°æ¥è¯¢ç»æä½æ¯æ¥è¯¢åºæ¥çæ°å¼ä¸ºnullä¼ææ ·ï¼ä¿®æ¹åå¨è¿ç¨å®ä¹ï¼ç¶åæ¥çè¿è¡ç»æï¼
CREATE PROCEDURE `select_into_value2`()
BEGIN
DECLARE _value INT DEFAULT 0;
SELECT number FROM intotest WHERE id=3 INTO _value;
SELECT _value;
END
mysql> call select_into_value();
+--------+
| _value |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ç»æ为NULLï¼ä¹å°±æ¯è¯´å½æ¥å°å¹é ç»ææ¶ï¼ä¸ç®¡ç»ææ¶ä»ä¹é½ä¼èµå¼å°æå®çåéä¸ï¼ç±»åä¸å¹é çsqlé误é¤å¤ï¼ã
- å½è¿ç»æ¥è¯¢èµå¼ä¸é´åºç°ä¸å¹é ä¼ææ ·ï¼ä¿®æ¹åå¨è¿ç¨å®ä¹ï¼ç¶åæ¥çè¿è¡ç»æï¼
CREATE PROCEDURE `select_into_value2`()
BEGIN
DECLARE _value INT DEFAULT 0;
SELECT number FROM intotest WHERE id=2 INTO _value;
SELECT number FROM intotest WHERE id=5 INTO _value;
SELECT _value;
END
mysql> call select_into_value();
+--------+
| _value |
+--------+
| 2 |
+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
æ³å¿ æç½äºå两ç§æ åµï¼è¿ç¬¬ä¸ç§ä¹åºè¯¥æç½äºï¼ä¸¤æ¡è¯å¥é¡ºåºæ§è¡ï¼æ¾å°å¹é çå°±èµå¼ï¼æ¾ä¸å°å°±æ¾å¼æä½ï¼ç»æå°±ä¿çäºä¸ä¸æ¬¡æåèµå¼çç»æã
æ»ç»
- å
³äº
è¯å¥èµå¼çè§åå°±ä¸å¥è¯ï¼æ¾å°äºç¬¦åæ¡ä»¶çè®°å½å°±èµå¼ï¼æ¾ä¸å°å°±ç®äºãselect into
- å¨æ¾å°è®°å½çåæä¸ï¼å¦æç±»åä¸å¹é ä¼å¯¼è´èµå¼å¤±è´¥å¹¶æ¥éï¼æ¯å¦æ¥è¯¢å°å符串èµå¼ç»æ´ååéã