gpt4 book ai didi

mysql - 如何使用 Knex raw 在 MySQL 中创建存储过程

转载 作者:行者123 更新时间:2023-11-29 09:40:40 25 4
gpt4 key购买 nike

我正在使用 Adonis,它在底层使用 Knex。我想创建这个存储过程


DROP PROCEDURE IF EXISTS fill_date_dimension;
DELIMITER //
CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)
BEGIN
DECLARE currentdate DATE;
SET currentdate = startdate;
WHILE currentdate < stopdate DO
INSERT INTO time_dimension VALUES (
YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
currentdate,
YEAR(currentdate),
MONTH(currentdate),
DAY(currentdate),
QUARTER(currentdate),
WEEKOFYEAR(currentdate),
DATE_FORMAT(currentdate,'%W'),
DATE_FORMAT(currentdate,'%M'),
'f',
CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,
NULL);
SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
END WHILE;
END
//
DELIMITER ;

TRUNCATE TABLE time_dimension;

CALL fill_date_dimension('2018-01-01','2030-12-31');
OPTIMIZE TABLE time_dimension;

问题是 knex raw,或者可能是 Adonis(我不知道)去掉了换行符。这给了我错误

DROP PROCEDURE IF EXISTS fill_date_dimension; DELIMITER // CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE) BEGIN DECLARE currentdate DATE; SET currentdate = startdate; WHILE currentdate < stopdate DO INSERT INTO dates VALUES ( YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate), currentdate, YEAR(currentdate), MONTH(currentdate), DAY(currentdate), QUARTER(currentdate), WEEKOFYEAR(currentdate), DATE_FORMAT(currentdate,'%W'), DATE_FORMAT(currentdate,'%M'), 'f', CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END, NULL); SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY); END WHILE; END // DELIMITER ; TRUNCATE TABLE dates; CALL fill_date_dimension('2018-01-01','2030-12-31'); OPTIMIZE TABLE time_dimension; - ER_PARSE_ERROR: 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 'DELIMITER // CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN sto' at line 4

我尝试将自己的 \n 标记放入 SQL 中,但它们被删除了。

知道谁让 knex.raw 运行我给它的内容,而不是取出标签。

最佳答案

这应该有帮助。这里我写了一个knex-migrate期望的模块。适应您的需求应该不成问题。

const mysql = require('mysql2');

const {
database: {
connection: {
host, user, password, database, port
}
}
} = require('./settings.js');

exports.up = async () => {
const script = `
CREATE DEFINER=\`<YOUR_DB_USER>\`@\`%\` PROCEDURE \`fill_date_dimension\`(IN startdate DATE,IN stopdate DATE)
BEGIN
DECLARE currentdate DATE;
SET currentdate = startdate;
WHILE currentdate < stopdate DO
INSERT INTO time_dimension VALUES (
YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
currentdate,
YEAR(currentdate),
MONTH(currentdate),
DAY(currentdate),
QUARTER(currentdate),
WEEKOFYEAR(currentdate),
DATE_FORMAT(currentdate,'%W'),
DATE_FORMAT(currentdate,'%M'),
'f',
CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,
NULL);
SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
END WHILE;
END`

const connection = mysql.createConnection({
host,
user,
database,
password,
port
});

return new Promise(function (resolve, reject) {
connection.query(
script,
function (err) {
if (err) {
return reject(err);
}

return resolve();
}
);
});
};

exports.down = async knex => knex.raw('DROP PROCEDURE IF EXISTS fill_date_dimension');

关于mysql - 如何使用 Knex raw 在 MySQL 中创建存储过程,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/56714846/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com