其实是mysql ;(分号的问题)。
可以采用delimiter //解决问题
image.png
然后回车，那么MySQL将立即执行该语句。
但有时候，不希望MySQL这么做。在为可能输入较多的语句，且语句中包含有分号。比如说我想创建一个存储过程
CREATE PROCEDURE `count_orthopedic`(in column_id VARCHAR(20))
begin
declare sample_count int default 0;
declare count_pre_t int default 0;
declare count_tn int default 0;
declare count_fn int default 0;
declare count_fp int default 0;
declare count_tp int default 0;
select count(1) from model_orthopedic_column_log m where m.column_id=column_id into sample_count;
select count(1) from model_log where model_type = '10' and score ='1' into count_pre_t;
select count(1) from model_log ml inner join model_orthopedic_column_log m on ml.id = m.model_id where m.column_id=column_id and m.res = 0 and ml.model_type = '10' and ml.score ='0' into count_tn;
select count(1) from model_log ml inner join model_orthopedic_column_log m on ml.id = m.model_id where m.column_id=column_id and m.res = 1 and ml.model_type = '10' and ml.score ='0' into count_fn;
select count(1) from model_log ml inner join model_orthopedic_column_log m on ml.id = m.model_id where m.column_id=column_id and m.res = 0 and ml.model_type = '10' and ml.score ='1' into count_fp;
select count(1) from model_log ml inner join model_orthopedic_column_log m on ml.id = m.model_id where m.column_id=column_id and m.res = 1 and ml.model_type = '10' and ml.score ='1' into count_tp;
select 1 'id',sample_count, count_pre_t,count_tn,count_fn,count_fp,count_tp;
end
这时候就一定会报错了，以为mysql检索到分号，就会执行sql语句，这里我们来告诉mysql，检索到//再执行，也就是修改成以下,在sql执行的末尾，加上"//"。
delimiter //
CREATE PROCEDURE `count_orthopedic`(in column_id VARCHAR(20))
begin
declare sample_count int default 0;#样本数
declare count_pre_t int default 0;#预测好转数
declare count_tn int default 0;
declare count_fn int default 0;
declare count_fp int default 0;
declare count_tp int default 0;
select count(1) from model_orthopedic_column_log m where m.column_id=column_id into sample_count;
select count(1) from model_log where model_type = '10' and score ='1' into count_pre_t;
select count(1) from model_log ml inner join model_orthopedic_column_log m on ml.id = m.model_id where m.column_id=column_id and m.res = 0 and ml.model_type = '10' and ml.score ='0' into count_tn;
select count(1) from model_log ml inner join model_orthopedic_column_log m on ml.id = m.model_id where m.column_id=column_id and m.res = 1 and ml.model_type = '10' and ml.score ='0' into count_fn;
select count(1) from model_log ml inner join model_orthopedic_column_log m on ml.id = m.model_id where m.column_id=column_id and m.res = 0 and ml.model_type = '10' and ml.score ='1' into count_fp;
select count(1) from model_log ml inner join model_orthopedic_column_log m on ml.id = m.model_id where m.column_id=column_id and m.res = 1 and ml.model_type = '10' and ml.score ='1' into count_tp;
select 1 'id',sample_count, count_pre_t,count_tn,count_fn,count_fp,count_tp;
end //
网友评论