Migrating data into Firebase from MySQL

问题:

I have an existing PHP/MySQL app which I am trying to migrate to AngularJS/Firebase, just as a way to learn these newer technologies.

The app has its own schema of tables in MySQL. One such table looks something like:

CREATE TABLE `dictionary` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `word` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `wordmeaning` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `wordlength` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

My question is: how do I migrate this table structure, and the data in it, from MySQL to Firebase?

I have tried exporting the data into a JSON string via a query such as:

SELECT      CONCAT("[",
          GROUP_CONCAT(
               CONCAT("{id:'",id,"',"),
               CONCAT("word:'",word,"',"),
               CONCAT("wordmeaning:'",wordmeaning,"',"),
               CONCAT("wordlength:'",wordlength,"'}")
          )
     ,"]") 
AS json FROM dictionary;

This gives a valid JSON string such as:

[{id:'1',word:'cat',wordmeaning:'a mammal species',wordlength:'3'},
{id:'2',word:'catapult',wordmeaning:'throwing device',wordlength:'8'},
{id:'3',word:'cart',wordmeaning:'something to carry things in',wordlength:'4'}]

I saved this in a file and tried to import the file from Firebase, using the Import JSON button, to which I got:Error parsing JSON data. Please validate your input. (The JSON string is valid. I checked it at http://jsonviewer.stack.hu/)

Any ideas about what I could be doing wrong? I am assuming that Firebase can handle such structures and the data in them.

Thanks.

回答1:

Your JSON is not valid. Change it to this to be valid:

[{"id":"1","word":"cat","wordmeaning":"a mammal species","wordlength":"3"},
{"id":"2","word":"catapult","wordmeaning":"throwing device","wordlength":"8"},
{"id":"3","word":"cart","wordmeaning":"something to carry things in","wordlength":"4"}]

So these are the changes:

1. use double-quotes instead of single-quotes
2. put (double) quotes around the keys, not just the values

You may want to consider not quoting the values of id and wordlength, since these properties seem to be numeric.

Edit

These two online tools seem to validate the JSON correctly (or at least in line with what Firebase expects):

• http://www.jslint.com/
• http://jsonlint.com/

The second one also pretty prints the JSON, so that might be a reason to prefer one or the other.

回答2:

Just in case anyone else comes across this question in the future there is a much easier way of doing this.

You can export your MySQL tables to a CSV file using a tool like SQLYog.

Once you have your CSV file you can then convert that to a JSON format using a free online tool like this one: http://www.convertcsv.com/csv-to-json.htm

I just imported 40 tables from MySQL to Firebase in about 15 minutes. Hope that helps someone!