下边是我写的代码:
DELIMITER $$
CREATE DEFINER=root
@%
PROCEDURE findHosts
(IN cluster_id VARCHAR(22), IN resource_id VARCHAR(22), IN linuxuser_id VARCHAR(22), IN service_type VARCHAR(10))
SQL SECURITY INVOKER
BEGIN
SET @cluster_id = cluster_id;
SET @resource_id = resource_id;
SET @linuxuser_id = linuxuser_id;
SET @service_type = service_type;
SET @all_ids = CONCAT('(SELECT hc.host_id id FROM host_component hc INNER JOIN service_', @service_type, ' s ON
hc.', @service_type, '_id = s.id) ');
SET @head = 'SELECT DISTINCT ms.id, ms.host_name, ms.ip_address, ms.region,
ms.machine_room, ms.rack, ms.cpu, ms.memory, ms.disk_usage,
ms.status, ms.mem_used, ms.disk_used, ms.cpu_used
FROM manager_host ms INNER JOIN host_component hc
ON ms.id = hc.host_id AND hc.cluster_id = ? ';
SET @service_condition = CONCAT('AND hc.host_id NOT IN ', @all_ids);
SET @tail = 'ORDER BY ms.host_name';
SET @resource_condition = 'AND hc.resource_id = ? ';
SET @linuxuser_condition = 'AND hc.linux_user_id = ? ';
SET @host_sql = CONCAT(@head, @service_condition);
IF (@resource_id IS NOT NULL, @resource_id <> '')
THEN SET @host_sql = CONCAT(@host_sql, @resource_condition);
END IF;
IF (@linuxuser_id IS NOT NULL, @linuxuser_id <> '')
THEN SET @host_sql = CONCAT(@host_sql, @linuxuser_condition);
END IF;
SET @host_sql = CONCAT(@host_sql, @tail);
PREPARE result FROM @host_sql;
EXECUTE result USING @cluster_id, @resource_id, @linuxuser_id;
DEALLOCATE PREPARE result;
END$$
DELIMITER ;
执行调用语句后:
call CALL findHosts('DH90GH9_FHF4GHD3DNP4CG', '', '', 'storm');
报错:
Error Code: 1241
Operand should contain 1 column(s)
当我把存储过程里边的SQL语句拿到外面直接运行的时候,是不会报错的,但是在存储过程里会报错,求各位大神指点这是什么原因