如何使用 Knex raw 在 MySQL 中创建存储过程 [英] How to create a stored procedure in MySQL with Knex raw
问题描述
我正在使用 Adonis,它在引擎盖下使用 Knex.我想创建这个存储过程
<预><代码>DROP PROCEDURE IF EXISTS fill_date_dimension;分隔符//CREATE PROCEDURE fill_date_dimension(IN startdate DATE,IN stopdate DATE)开始声明当前日期;SET 当前日期 = 开始日期;当当前日期<停止日期做INSERT INTO time_dimension VALUES (YEAR(当前日期)*10000+MONTH(当前日期)*100 + DAY(当前日期),当前的日期,年(当前日期),MONTH(当前日期),DAY(当前日期),季度(当前日期),WEEKOFYEAR(当前日期),DATE_FORMAT(当前日期,'%W'),DATE_FORMAT(当前日期,'%M'),'F',CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,空值);SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);结束时;结尾//分隔符;TRUNCATE TABLE time_dimension;CALL fill_date_dimension('2018-01-01','2030-12-31');优化表 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 当前日期 = 开始日期;当当前日期
我曾尝试将我自己的 \n
标记放入 SQL,但它们被删除了.
知道让 knex.raw 运行我给它的东西,而不是去掉标签.
这应该会有所帮助.这里我写了一个 knex-migrate 期望的模块.适应您的需求应该没有问题.
const mysql = require('mysql2');常量{数据库:{联系: {主机、用户、密码、数据库、端口}}} = require('./settings.js');export.up = async() =>{const 脚本 = `CREATE DEFINER=\`<YOUR_DB_USER>\`@\`%\` PROCEDURE \`fill_date_dimension\`(IN startdate DATE,IN stopdate DATE)开始声明当前日期;SET 当前日期 = 开始日期;当当前日期<停止日期做INSERT INTO time_dimension VALUES (YEAR(当前日期)*10000+MONTH(当前日期)*100 + DAY(当前日期),当前的日期,年(当前日期),MONTH(当前日期),DAY(当前日期),季度(当前日期),WEEKOFYEAR(当前日期),DATE_FORMAT(当前日期,'%W'),DATE_FORMAT(当前日期,'%M'),'F',CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,空值);SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);结束时;结束`const 连接 = mysql.createConnection({主持人,用户,数据库,密码,港口});返回新的承诺(函数(解决,拒绝){连接.查询(脚本,功能(错误){如果(错误){返回拒绝(错误);}返回解决();});});};export.down = 异步 knex =>knex.raw('DROP PROCEDURE IF EXISTS fill_date_dimension');
I am using Adonis which uses Knex under the hood. I am want to create this stored procedure
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;
The problem is that knex raw, or may be Adonis (I don't know) strips out the line breaks. This gives me the error
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
I have tried putting my own \n
tags into the SQL, but they are stripped out.
Any idea who to make knex.raw run what I give it, rather than taking out the tags.
This should help. Here I wrote a module expected by knex-migrate. It should be no trouble to adapt to your need.
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');
这篇关于如何使用 Knex raw 在 MySQL 中创建存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!