Search code examples
jsonmysqlimport

Can mysqlimport accept JSON input?

We currently have certain logs composed of pipe-delimited text, which has the format

field1:value1|field2:value2|...|fieldN:valueN\n
field1:value1|field2:value2|...|fieldN:valueN\n
etc.

I have an importer script (bash that runs perl and then mysqlimport), which currently removes a few words from the beginning of each row and then runs mysqlimport on the resulting text file.

We are upgrading our logging to a data service that outputs JSON. The importer script will still be importing the content of that JSON into a MySQL database table. It feels like bad form to take that nice JSON and convert it into a pipe-delimited file for use with mysqlimport - but do I have any options? I couldn't find anything in the mysql documentation or forums.

Solution

  • I'd rather convert the solid JSON to equally solid, a mysqldump compatible SQL multiline insert statement:

    INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:

    INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

    Since you're using a Perl script anyway, this shouldn't pose much of a problem.