IF EXISTS(SELECT 1 FROM sometable WHERE somefield=somevalue) then SELECT 1 ; ELSE SELECT 2; end if ;
IF EXISTS(SELECT 1 FROM sometable WHERE somefield=somevalue) then SELECT 1 ; ELSE SELECT 2; end if ;
mysql> delimiter // mysql> create procedure xuancy() -> begin -> IF EXISTS(SELECT 1 ) -> then -> SELECT 2 ; -> ELSE -> SELECT 3; -> end if ; -> end -> // Query OK, 0 rows affected (0.00 sec)mysql> call xuancy(); -> // +---+ | 2 | +---+ | 2 | +---+ 1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)
IF EXISTS(SELECT 1 FROM pre_common_member WHERE uid=29264) THEN SELECT 1; ELSE SELECT 2; END IF; 痛苦,报错如下:SQL execution error # 1064. Response from the database:You have a error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS(SELECT 1FROM pre_common_member WHERE uid=29264) THEN SELECT 1; ELS' at line1
mysql> create table xuancy(uid int); Query OK, 0 rows affected (1.20 sec)mysql> insert into xuancy values (1),(2),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0mysql> delimiter // mysql> create procedure xuancy(in id int) -> begin -> if exists(select 1 from xuancy where uid=id ) -> then -> select 2 ; -> else -> select 3; -> end if ; -> end -> // Query OK, 0 rows affected (0.00 sec)mysql> delimiter ; mysql> call xuancy(1); +---+ | 2 | +---+ | 2 | +---+ 1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> call xuancy(5); +---+ | 3 | +---+ | 3 | +---+ 1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)
按你上面的代码是正常的,不用存储过程,单另写一条语句怎么写? 我试了下报错如下:mysql> if exists(select 1 from xuancy where uid=1) -> then -> select 2; -> end if; -> // ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if ex ists(select 1 from xuancy where uid=1) then select 2; end if' at line 1
你上面那个等于这个哇 select 2 from xuancy where exists(select 1 from xuancy where uid=1)
then
SELECT 1 ;
ELSE
SELECT 2;
end if ;
then
SELECT 1 ;
ELSE
SELECT 2;
end if ;
mysql> create procedure xuancy()
-> begin
-> IF EXISTS(SELECT 1 )
-> then
-> SELECT 2 ;
-> ELSE
-> SELECT 3;
-> end if ;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)mysql> call xuancy();
-> //
+---+
| 2 |
+---+
| 2 |
+---+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)
IF EXISTS(SELECT 1 FROM pre_common_member WHERE uid=29264)
THEN
SELECT 1;
ELSE
SELECT 2;
END IF;
痛苦,报错如下:SQL execution error # 1064. Response from the database:You have a error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS(SELECT 1FROM pre_common_member WHERE uid=29264)
THEN
SELECT 1;
ELS' at line1
mysql> create table xuancy(uid int);
Query OK, 0 rows affected (1.20 sec)mysql> insert into xuancy values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> delimiter //
mysql> create procedure xuancy(in id int)
-> begin
-> if exists(select 1 from xuancy where uid=id )
-> then
-> select 2 ;
-> else
-> select 3;
-> end if ;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql> call xuancy(1);
+---+
| 2 |
+---+
| 2 |
+---+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> call xuancy(5);
+---+
| 3 |
+---+
| 3 |
+---+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)
我试了下报错如下:mysql> if exists(select 1 from xuancy where uid=1)
-> then
-> select 2;
-> end if;
-> //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'if ex
ists(select 1 from xuancy where uid=1)
then
select 2;
end if' at line 1