MySQL声明PHPMyADMIN

Question

Im want to run queries with declaraions in PHPMYADMIN.

Here my querycode

declare @shopdomain varchar(30);
SET @shopdomain = 'newdomain.com';
UPDATE trans SET tval=REPLACE(name,'olddomain.de', @shopdomain ) WHERE name LIKE 'olddomain.de';
UPDATE settings SET tval=REPLACE(name,'olddomain.de', @shopdomain ) WHERE name LIKE 'olddomain.de';
UPDATE...

PHPMYADMIN shows this error

#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 'declare @shopdomain varchar(30)' at line 1 

What im doing wrong?

Answer 1

1. DECLARE available only in a context of a stored routine (procedure, function, trigger, event)

2. You're mixing local variables (without @ in front of their names) with user(session) variables

That being said if you want to go with local variables you do something like this

DELIMITER $$
CREATE PROCEDURE my_proc()
BEGIN
  DECLARE shopdomain VARCHAR(30);
  SET shopdomain = 'newdomain.com';
  UPDATE trans SET tval = REPLACE(name,'olddomain.de', shopdomain ) WHERE name LIKE 'olddomain.de';
  UPDATE settings SET tval=REPLACE(name,'olddomain.de', shopdomain ) WHERE name LIKE 'olddomain.de';
  UPDATE ...
END$$
DELIMITER ;

And then call your procedure

CALL my_proc();

If you go with session variables then you can execute it right away without creating a procedure in the following manner

SET @shopdomain = 'newdomain.com';
UPDATE trans SET tval = REPLACE(name,'olddomain.de', @shopdomain ) WHERE name LIKE 'olddomain.de';
UPDATE settings SET tval=REPLACE(name,'olddomain.de', @shopdomain ) WHERE name LIKE 'olddomain.de';
UPDATE ...