I created a Spatial table Points using SQL Editor in MySQL workbench. To fill this table, the following is the code I am using.
我在MySQL工作台中使用SQL编辑器创建了一个Spatial表。要填写此表,以下是我正在使用的代码。
CREATE PROCEDURE fill_points(
IN size INT(10)
)
BEGIN
DECLARE i DOUBLE(10,1) DEFAULT size;
DECLARE lon FLOAT(7,4);
DECLARE lat FLOAT(6,4);
DECLARE position VARCHAR(100);
-- Deleting all.
DELETE FROM Points;
WHILE i > 0 DO
SET lon = RAND() * 360 - 180;
SET lat = RAND() * 180 - 90;
SET position = CONCAT( 'POINT(', lon, ' ', lat, ')' );
INSERT INTO Points(name, location) VALUES ( CONCAT('name_', i), GeomFromText(position) );
SET i = i - 1;
END WHILE;
END
when I executed it, it shows the error
当我执行它时,它显示错误
Error Code: 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 'END' at line 1
错误代码:1064。您的SQL语法有错误;检查与MySQL服务器版本对应的手册,以便在第1行的“END”附近使用正确的语法
Executing the statement
执行声明
CALL fill_points(1000);
shows the same error
显示相同的错误
I even don't know whether the way I proceed is correct or not.
我甚至不知道我的行进方式是否正确。
Can anybody help me...
有谁能够帮助我...
1 个解决方案
#1
12
Have you ended the entire query? Try setting a delimiter, and use it after the END so the server knows you finished the command.
你结束了整个查询吗?尝试设置分隔符,并在END之后使用它,以便服务器知道您完成了命令。
delimiter //
CREATE PROCEDURE fill_points(
IN size INT(10)
)
BEGIN
DECLARE i DOUBLE(10,1) DEFAULT size;
DECLARE lon FLOAT(7,4);
DECLARE lat FLOAT(6,4);
DECLARE position VARCHAR(100);
-- Deleting all.
DELETE FROM Points;
WHILE i > 0 DO
SET lon = RAND() * 360 - 180;
SET lat = RAND() * 180 - 90;
SET position = CONCAT( 'POINT(', lon, ' ', lat, ')' );
INSERT INTO Points(name, location) VALUES ( CONCAT('name_', i), GeomFromText(position) );
SET i = i - 1;
END WHILE;
END //
delimiter ;
Also, by the by
While DELETE FROM TABLE does remove all data from the table, TRUNCATE table does so faster. Unless you have good reasons to use DELETE (they exist), TRUNCATE might be what you want.
虽然DELETE FROM TABLE确实从表中删除了所有数据,但TRUNCATE表的执行速度更快。除非你有充分的理由使用DELETE(它们存在),否则TRUNCATE可能就是你想要的。
#1
12
Have you ended the entire query? Try setting a delimiter, and use it after the END so the server knows you finished the command.
你结束了整个查询吗?尝试设置分隔符,并在END之后使用它,以便服务器知道您完成了命令。
delimiter //
CREATE PROCEDURE fill_points(
IN size INT(10)
)
BEGIN
DECLARE i DOUBLE(10,1) DEFAULT size;
DECLARE lon FLOAT(7,4);
DECLARE lat FLOAT(6,4);
DECLARE position VARCHAR(100);
-- Deleting all.
DELETE FROM Points;
WHILE i > 0 DO
SET lon = RAND() * 360 - 180;
SET lat = RAND() * 180 - 90;
SET position = CONCAT( 'POINT(', lon, ' ', lat, ')' );
INSERT INTO Points(name, location) VALUES ( CONCAT('name_', i), GeomFromText(position) );
SET i = i - 1;
END WHILE;
END //
delimiter ;
Also, by the by
While DELETE FROM TABLE does remove all data from the table, TRUNCATE table does so faster. Unless you have good reasons to use DELETE (they exist), TRUNCATE might be what you want.
虽然DELETE FROM TABLE确实从表中删除了所有数据,但TRUNCATE表的执行速度更快。除非你有充分的理由使用DELETE(它们存在),否则TRUNCATE可能就是你想要的。