【踩坑】存储过程入参为NULL 报错

mmlik8878

于 2019-01-24 15:19:44 发布

阅读量2.1k

点赞数

分类专栏： JAVA MySQL

本文链接：https://blog.youkuaiyun.com/mmlik8878/article/details/86627239

版权

在使用存储过程时遇到SQL语法错误，报错提示在'NULL'附近有语法错误。经过排查，问题出在存储过程内部使用CONCAT函数处理包含NULL参数的情况。CONCAT函数无法接受NULL，将NULL替换为空字符串后问题解决。总结了解决NULL参数导致的CONCAT函数错误的三种情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

自己写了个存储过程代码如下

CREATE DEFINER=`pan`@`%` PROCEDURE `get_app_page_by_condition`(
	IN `key_word` VARCHAR ( 20 ),
	IN `category_name_id` BIGINT,
	IN `developer_id` BIGINT,
	IN `is_enable` TINYINT,
	IN `is_inverted` TINYINT,
	IN `tag` VARCHAR ( 20 ),
	IN `start_num` INT,
	IN `page_size` INT 
	)
BEGIN#Routine body goes here...
	DECLARE
		cate_lv INT;
	SELECT
		lv INTO cate_lv 
	FROM
		category_name 
	WHERE
		id = category_name_id;
	
	select developer_id;
	
		SET @keyword_condition = NULL;
		SET @category_condition = NULL;
		SET @developer_id_condition = NULL;
		SET @is_enable_condition = NULL;
		SET @is_inverted_condition = NULL;
		SET @tag_condition = NULL;
		SET @page_condition = NULL;
	
	SET @base_result_sql = '	SELECT   app.id,
            app_name,

            av.id as app_version_id,

最低0.47元/天解锁文章