将具有3个表的旧MySQL迁移到JSON(Migrate Old MySQL with 3 tables to JSON)

将具有3个表的旧MySQL迁移到JSON(Migrate Old MySQL with 3 tables to JSON)

我正在努力将我的PHP / MySQL数据迁移到Firebase，它只允许通过JSON导入。 几年前我制作了这5个MySQL表并没有正确完成。 我从来没有正式设置任何外键，所以我使用单独的查询而不是连接。

现在，我正在尝试编写一个脚本来移动大约4000个组合行。 我正在使用PHPMyAdmin并拥有所有可用的标准功能。 我真的很感激任何正确方向的提示或提示。 提前谢谢！ :)

这是我的布局的简化版本，包含所有必要的部分：

TABLE users - id (int, primary key) - username (varchar) TABLE posts - id (int, primary key) - content (varchar) - userid (int, should be foreign key to users) TABLE comments - id (int, primary key) - content (varchar) - userid (int, should be foreign key to users) - postid (int, should be foreign key to posts) TABLE commentLikes - id (int, primary key) - likeValue (int) - commentid (int, should be foreign key to comments) - userid (int, should be foreign key to users) TABLE postLikes - id (int, primary key) - likeValue (int) - postid (int, should be foreign key to posts) - userid (int, should be foreign key to users)

基本上，网站上的用户可以创建帖子并对帖子发表评论。 评论和帖子都可以被喜欢。

我绘制了我想要的JSON文档。 我会为用户提供一种类型，为帖子提供一种类型。

Post JSON format { id: 100, content: "A post by user 1", userid: 1, comments: [ { content: "A comment by user 2", userid: 2, commentLikes: [ { likeValue: 1, userid: 2 }, { likeValue: -1, userid: 3 } ] }, { content: "A comment by user 3", userid: 3, commentsLikes: [] } ], postLikes: [ { likeValue: 1, userid: 2 }, { likeValue: -1, userid: 3 } ] } User JSON Format { id: 1, username: "Bob" }

有没有人对如何实现这种转变有任何想法？ 我知道MySQL的设置非常糟糕。 但是，我不能丢失这些数据。 这是我写的脚本，但我不相信它有效。 即使在脚本工作之后，我如何批量创建这些JSON文件以导入Firebase？

SELECT p.*, c.content, c.userid, cL.likeValue, cL.userid, pL.likeValue, pL.userid FROM posts p, comments c, commmentLikes cL, postLikes pL WHERE c.postid = p.id OR pL.postid = p.id

如果你读到这里，非常感谢你的帮助！ :)

I am working on migrating my PHP/MySQL data to Firebase, which only allows import via JSON. I made these 5 MySQL tables a few years ago and didn't do it correctly. I never officially set anything as a foreign key, so I was using separate queries instead of joins.

Now, I am attempting to write a script to move about 4000 combined rows. I'm using PHPMyAdmin and have all the standard features available there. I'd really appreciate any tips or hints in the right direction. Thanks ahead of time! :)

Here's a simplified version of my layout with all the necessary parts:

TABLE users - id (int, primary key) - username (varchar) TABLE posts - id (int, primary key) - content (varchar) - userid (int, should be foreign key to users) TABLE comments - id (int, primary key) - content (varchar) - userid (int, should be foreign key to users) - postid (int, should be foreign key to posts) TABLE commentLikes - id (int, primary key) - likeValue (int) - commentid (int, should be foreign key to comments) - userid (int, should be foreign key to users) TABLE postLikes - id (int, primary key) - likeValue (int) - postid (int, should be foreign key to posts) - userid (int, should be foreign key to users)

Basically, users on the site can create posts and comment on posts. Comments and posts can both be liked.

I mapped out what I would like the JSON documents to look like. I would have one type for users, and one type for posts.

Post JSON format { id: 100, content: "A post by user 1", userid: 1, comments: [ { content: "A comment by user 2", userid: 2, commentLikes: [ { likeValue: 1, userid: 2 }, { likeValue: -1, userid: 3 } ] }, { content: "A comment by user 3", userid: 3, commentsLikes: [] } ], postLikes: [ { likeValue: 1, userid: 2 }, { likeValue: -1, userid: 3 } ] } User JSON Format { id: 1, username: "Bob" }

Does anyone have any ideas on how to accomplish this transformation? I understand that the MySQL is set up pretty badly. However, I cannot afford to lose this data. Here's a script that I wrote, but I don't believe it works. Even after the script works, how might I batch create these JSON files for import into Firebase?

SELECT p.*, c.content, c.userid, cL.likeValue, cL.userid, pL.likeValue, pL.userid FROM posts p, comments c, commmentLikes cL, postLikes pL WHERE c.postid = p.id OR pL.postid = p.id

If you've read til here, thank you so much for your help! :)

最满意答案

使用我安装的PHPMyadmin版本，4.2.7.1我能够直接导出整个表，或者从查询生成的视图作为JSON。

如果您的版本没有在导出格式选项中列出JSON，则只需升级到具有“导出到PHPMyAdmin的JSON插件”的任何版本。 看起来是任何版本> = 3.5.1

Using my installed version of PHPMyadmin, 4.2.7.1 I was able to directly export an entire table, or a view generated from a query as JSON.

If your version doesn't have JSON listed in the export format options just upgrade to any version that has the 'Export to JSON plugin for PHPMyAdmin'. Looks to be any version >= 3.5.1