How to concatenate the return of a query to a string from another mysql

Asked

Viewed 129 times

0

I have the following query:

 SELECT 
    id   AS CODIGO,
    nome AS NOME,
    versao AS VERSAO,
    build  AS BUILD,
    data   AS DATA
        FROM sistema 
            INTO OUTFILE 'D:/servicos/export/exportaVersao.csv'
            FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\n';

I would like to change the export version to some random number, example:

'D:/servicos/export/256589.csv'

I got this query that generates this number:

(SELECT FLOOR(10000 + (RAND() * 99999)))

How can I concatenate with each other ?

If it’s not possible, instead this random number could be: 170704 (data = aa/mm/dd)

Attempt to concatenate:

 SELECT  
    id   AS CODIGO,
    nome AS NOME,
    versao AS VERSAO,
    build  AS BUILD,
    data   AS DATA
        FROM sistema 
            INTO OUTFILE concat(concat('D:/servicos/export/','' ,(SELECT FLOOR(10000 + (RAND() * 99999)))),'.','csv')
            FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Error:

0 55 01:02:58 SELECT -- QUERY id THE CODE, NAME THE NAME, version AS VERSAO, build as build, DATE AS FROM system INTO OUTFILE Concat(Concat(’D:/servicos/export/','' ,(SELECT FLOOR(10000 + (RAND() * 99999)),'.','csv') FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY ' n' Error Code: 1064. You have an error in your SQL syntax; check the manual that Corresponds to your Mariadb server version for the right syntax to use near 'Concat(Concat(’D:/servicos/export/',' ,(SELECT FLOOR(10000 + (RAND() * 99999))' at line 8 0.000 sec

mysql query

  • Already tried using the function concat?

  • Already, but it didn’t work, I don’t know if it’s because: INTO OUTFILE

  • Show how your attempt was and if you got any error message

  • @Jeffersonquesado I added the question.

  • I would say it’s missing parenthesis, but I can’t be sure. Also take advantage is to leave here the documentation indicating that with a concat(str1, str2, str3) you would solve the problem, without needing multiple. Also note that calculations can be made in place of arguments without needing to be a sub-consumption

  • Okay, in my count the amount of parentheses is correct, so it must be something else

  • Maybe that empty string, why you need it?

  • do not need, I will remove to test - Tested gave the same thing.

Show 3 more comments

1 answer

0


Solution:

delimiter |
ALTER EVENT ExportaVersao
    ON SCHEDULE 
        EVERY 5 SECOND
    COMMENT 'Exporta Versão do Sistema'
    DO
      BEGIN
      SET @sql_text = 
        CONCAT ("SELECT * FROM SISTEMA  into outfile 'D:/servicos/export/", 
        DATE_FORMAT( NOW(), '%Y%m%d%H%i%s'), 
        ".csv 'FIELDS TERMINATED BY ';'");
       PREPARE s1 FROM @sql_text;
       EXECUTE s1;
       DROP PREPARE s1;
      END|

delimiter ;

Browser other questions tagged mysql query

You are not signed in. Login or sign up in order to post.