【已解决】手动操作mysql数据库添加二级topic主题

希望手动操作mysql，实现添加一个二级的topic

比如：

在一级topic：Animal 里加一个二级topic：ocean animals

而现有的一级topic的Animal中是没有这个二级topic，所以界面上是选择不出来的：

然后先去备份在线的mysql的数据库：

<code>mysqldump -h your_rd_host -P pport -B db_name -uroot -pPWD | gzip &gt; aliyun_rds_mysql_dbname_dump_180713.sql.gz
</code>

用sz下载到本地：

解压后，再去本地导入：

但是在本地已有的naturling中导入会出现错误：

<code>➜  online_backup mysql -u root -p dbname &lt; aliyun_rds_mysql_dbname_dump_180713.sql
Enter password:
ERROR 1215 (HY000) at line 115: Cannot add foreign key constraint
</code>

估计原因是和现有本地数据混杂在一起，所以外键找不到

所以要先删除了原有本地数据库

<code>➜  online_backup mysql -u root -p
Enter password:
...
mysql&gt; show databases;
mysql&gt; drop database dbname;
Query OK, 28 rows affected (0.23 sec)

mysql&gt; quit
</code>

再重新导入，然后提示找不到数据库

<code>➜  online_backup mysql -u root -p dbname &lt; aliyun_rds_mysql_dbname_dump_180713.sql
Enter password:
ERROR 1049 (42000): Unknown database 'dbname'
</code>

所以去新建数据库，此处用图形界面工具Sequel中去创建：

然后再去导入就可以了：

<code>➜  online_backup mysql -u root -p dbname &lt; aliyun_rds_mysql_dbname_dump_180713.sql
Enter password:
</code>

然后继续操作：

<code>➜  online_backup mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql&gt; select dbname;
ERROR 1054 (42S22): Unknown column 'dbname' in 'field list'
mysql&gt; use dbname;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql&gt; show tables;
+----------------------------+
| Tables_in_naturling        |
+----------------------------+
| auth_group                 |
| auth_group_permissions     |
| auth_permission            |
| django_admin_log           |
| django_content_type        |
| django_migrations          |
| django_session             |
| django_site                |
| enum_value_dict            |
| keyword                    |
| keyword_rel                |
| media                      |
| media_keyword_rel          |
| media_scene_rel            |
| qa                         |
| qa_keyword_rel             |
| qa_scene_rel               |
| scene                      |
| scene_keyword_rel          |
| script_dialog              |
| script_history             |
| script_review              |
| script_script              |
| thesaurus                  |
| user_functiongroup         |
| user_functiongroup_members |
| user_user                  |
| user_user_groups           |
| user_user_user_permissions |
+----------------------------+
29 rows in set (0.00 sec)

mysql&gt; INSERT INTO `keyword`(`name`,`type`,`createTime`,`modifyTime`)
    -&gt;   VALUES("ocean animals","topic","2018-07-13 10:09:27","2018-07-13 10:09:27");
Query OK, 1 row affected (0.01 sec)

mysql&gt; select * from keyword where type="topic" AND name="ocean animals";
+------+---------------+-------+---------------------+---------------------+
| id   | name          | type  | createTime          | modifyTime          |
+------+---------------+-------+---------------------+---------------------+
| 7529 | ocean animals | topic | 2018-07-13 10:09:27 | 2018-07-13 10:09:27 |
+------+---------------+-------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql&gt; SELECT * FROM keyword WHERE type="sectorTopic" AND name="Animal";
+------+--------+-------------+---------------------+---------------------+
| id   | name   | type        | createTime          | modifyTime          |
+------+--------+-------------+---------------------+---------------------+
| 5792 | Animal | sectorTopic | 2018-06-15 17:30:56 | 2018-06-15 17:30:56 |
+------+--------+-------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql&gt; INSERT INTO `keyword_rel`
    -&gt;   (`keyword1`,`keyword2`,`type`,`createTime`,`modifyTime`,`active`,`verify`)
    -&gt;   VALUES(5792,7529,0,"2018-07-13 10:33:10","2018-07-13 10:33:10","Y","Y");
Query OK, 1 row affected (0.01 sec)

mysql&gt; SELECT * FROM keyword_rel WHERE keyword1=5792 AND keyword2=7529;
+-----+----------+----------+------+---------------------+---------------------+--------+--------+
| id  | keyword1 | keyword2 | type | createTime          | modifyTime          | active | verify |
+-----+----------+----------+------+---------------------+---------------------+--------+--------+
| 172 |     5792 |     7529 |    0 | 2018-07-13 10:33:10 | 2018-07-13 10:33:10 | Y      | Y      |
+-----+----------+----------+------+---------------------+---------------------+--------+--------+
1 row in set (0.01 sec)

mysql&gt;
</code>

即可看到导入的二级topic：

和通过keyword_rel实现的父子的一级二级的关系了：

然后界面上就可以选择到该二级topic了：

转载请注明：在路上 » 【已解决】手动操作mysql数据库添加二级topic主题