Mysql存储过程语法

Question

我正在尝试编写简单的 mysql 存储过程，似乎我无法正确执行，到目前为止我已经做到了，

    delimiter //
    create procedure addRecord(_login varchar(15),_artist varchar(50),_record varchar(50))
    begin
    declare dbArtist varchar(50);
    delcare dbRecord varchar(50);

    set dbArtist = (select artistname from artists where lower(artistname) = lower(_artist));

    set dbRecord=(select recordname from records where lower(recordname)=lower(_record));
    if not exists (select * from Artists where lower(artistname)=lower(_artist)) then
    begin
      INSERT INTO `Artists`(`ArtistName`) VALUES (_artist);
      set dbArtist=_artist;
    end

    if not exists (select * from Records as R inner join Artists as A on R.ArtistId=A.ArtistId where lower(R.RecordName)=lower(_record) and A.ArtistName=dbArtist) then
    begin
      INSERT INTO `Records`(`ArtistId`, `RecordName`) VALUES ((select artistid from artists where artistname=dbArtist),_record);
      set dbRecord=_record;
    end

    end

但我在第 4 行中收到语法错误：

#1064 - 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 'dbRecord varchar(50);
set dbArtist = (select artistname from artists where lowe' at line 4

此消息错误由 phpMyAdmin 返回给我，任何人都可以告诉我为什么这样做我收到错误？

编辑：修改后的版本，现在仍然不好

delimiter //
create procedure addRecord(_login varchar(15),_artist varchar(50),_record varchar(50))
begin
declare dbArtist varchar(50);
declare dbRecord varchar(50);

set dbArtist = (select artistname from artists where lower(artistname) = lower(_artist));
set dbRecord=(select recordname from records where lower(recordname)=lower(_record));
if not exists (select * from Artists where lower(artistname)=lower(_artist)) then
begin
  INSERT INTO `Artists`(`ArtistName`) VALUES (_artist);
  set dbArtist=_artist;
end

if not exists 
(select * from Records as R inner join Artists as A on R.ArtistId = A.ArtistId where     lower(R.RecordName)=lower(_record) and A.ArtistName=dbArtist) 
then
begin
  INSERT INTO `Records`(`ArtistId`, `RecordName`) VALUES ( (select artistid from artists where artistname=dbArtist) ,_record);
  set dbRecord=_record;
end

end

，第 14 行出现错误并消息：

#1064 - 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 not exists (select * from Records as R inner join Artists as A on R.ArtistId' at line 14

Answer 1

问题是您拼写错误DECLARE：

delcare dbRecord varchar(50);

更新：对于您的下一个错误，问题是您非法使用NOT EXISTS。

在存储过程中，正确的方法是对现有行进行计数，然后在计数为 0 时有条件地插入一个值。

如下所示：

SELECT COUNT(*)
INTO @v_row_count
FROM Artists 
WHERE LOWER(artistname)=LOWER(_artist);

IF (@v_row_count = 0)
THEN
  INSERT INTO `Artists`(`ArtistName`) VALUES (_artist);
  set dbArtist=_artist;
END IF;

PS 为了避免 select 查询性能不佳，您应该考虑使用不属于的排序规则区分大小写，因此您无需将 LOWER() 函数应用于artistname 列。