|
背景：
Win10系统，通过Xshell连接远程数据库，上传数据。建表和简单数据录入直接通过navicat完成。
问题：
使用Navicat粘贴比较大的json数据到表字段中时，出现长度受限的情况，无法将完整的json数据放入数据库表字段。
查了mysql给的指南，JSON的长度应该是和LONGTEXT一样的，所以理论上能存下完整的数据，但navicat无法完整的粘贴进来，也不能完整显示已经存好的数据。所以只能通过mysql命令行来插入和查看了。
JSON Storage Requirements
In general, the storage requirement for a JSON column is approximately the same as for a LONGBLOB or LONGTEXT column; that is, the space consumed by a JSON document is roughly the same as it would be for the document’s string representation stored in a column of one of these types. However, there is an overhead imposed by the binary encoding, including metadata and dictionaries needed for lookup, of the individual values stored in the JSON document. For example, a string stored in a JSON document requires 4 to 10 bytes additional storage, depending on the length of the string and the size of the object or array in which it is stored.
In addition, MySQL imposes a limit on the size of any JSON document stored in a JSON column such that it cannot be any larger than the value of max_allowed_packet.
解决：
首先连接数据库，在Xshell打开终端
输入mysql -u root -p ，输入数据库密码，进入mysql：
[root@localhost ~]
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1118
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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>
选择要修改的数据库：
mysql> use rs_db
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>
在表shapefile01中新增一个名为json_data的json类型的字段：
alter table shapefile01 add json_data json;
如果已经有这个字段想要改数据类型，使用下面这一句，意思是将表article01中的json_data列改为json类型。
alter table shapefile01 modify json_data json;
接下来把一条json数据插入表中指定的位置，这里是把’ ‘里的内容插入到表shapefile01的json_data列，行索引为id = 10101。需要把json文件的内容粘贴在’ '里。
update shapefile01 set json_data = '' where id = 10101;
插入成功后，使用select查看插入的内容：
select json_data from shapefile01 where id = 10101;
输出显示json数据的后半部分文本，以及----------------------表示的空白。看得到数据结尾处已经录进去了，但受限于显示区域，前半部分没有显示，按理说是正常插入了。后面研究一下navicat是不是能设置显示最大输入的长度。
|