神刀安全网

Upgrading JSON data stored in TEXT columns

One of the more frequently asked questions with MySQL 5.7 is “How can I upgrade my JSON data from using TEXT in an earlier version of MySQL to use the native JSON data type?”. Today I wanted to show an example of how to do so, using sample data from SF OpenData .

Step 1: Preflight Checks

Since the JSON data type converts data to a native format for storage, it requires that all data inserted into it be valid. This check can be done before running the ALTER TABLE command by using the function JSON_VALID() :

mysql> SHOW CREATE TABLE features/G 1. row Table: features Create Table: CREATE TABLE

features

(

id

int(11) NOT NULL AUTO INCREMENT,

feature

longtext NOT NULL, PRIMARY KEY (

id

) ) ENGINE=InnoDB AUTOINCREMENT=262141 DEFAULT CHARSET=latin1 1 row in set (0.01 sec)

mysql> SELECT * FROM features WHERE JSON VALID(feature)=0/G 1. row id: 43253 feature: {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.45004426230204, 37.77462573231635, 0], [-122.45005814149769, 37.77469350183866, 0], [-122.44969515193891, 37.774739669796645, 0], [-122.44968127306583, 37.774671900232214, 0], [-122.45004426230204, 37.77462573231635, 0]]]}, "properties": {"TO ST": "51", "BLKLOT": "1189002B", "STREET": "CLAYTON", "FROM ST": "49", "LOT NUM": "002B", "ST TYPE": "ST", "ODD EVEN": "O", "BLOCK_NUM": "1189", "MAPB 1 row in set (1.42 sec)

mysql> SHOWCREATETABLEfeatures/G *************************** 1. row ***************************       Table: features CreateTable: CREATETABLE `features` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `feature` longtextNOT NULL,   PRIMARYKEY (`id`) ) ENGINE=InnoDBAUTO_INCREMENT=262141 DEFAULT CHARSET=latin1 1 rowin set (0.01 sec)   mysql> SELECT * FROMfeaturesWHEREJSON_VALID(feature)=0/G *************************** 1. row ***************************     id: 43253 feature: {"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.45004426230204, 37.77462573231635, 0], [-122.45005814149769, 37.77469350183866, 0], [-122.44969515193891, 37.774739669796645, 0], [-122.44968127306583, 37.774671900232214, 0], [-122.45004426230204, 37.77462573231635, 0]]]}, "properties": {"TO_ST": "51", "BLKLOT": "1189002B", "STREET": "CLAYTON", "FROM_ST": "49", "LOT_NUM": "002B", "ST_TYPE": "ST", "ODD_EVEN": "O", "BLOCK_NUM": "1189", "MAPB 1 rowin set (1.42 sec) 

Searching by JSON_VALID(feature)=0 will return all invalid JSON documents, and I have intentionally corrupted one row to show an example. I will be required to manually fix this before changing the data type to JSON:

mysql> UPDATE features SET feature = ‘{"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.45004426230204, 37.77462573231635, 0], [-122.45005814149769, 37.77469350183866, 0], [-122.44969515193891, 37.774739669796645, 0], [-122.44968127306583, 37.774671900232214, 0], [-122.45004426230204, 37.77462573231635, 0]]]}, "properties": {"TO ST": "51", "BLKLOT": "1189002B", "STREET": "CLAYTON", "FROM ST": "49", "LOT NUM": "002B", "ST TYPE": "ST", "ODD EVEN": "O", "BLOCK NUM": "1189", "MAPBLKLOT": "1189002B"}}’ WHERE id = 43253; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM features WHERE JSON_VALID(feature)=0/G Empty set (1.36 sec)

mysql> UPDATEfeaturesSETfeature = '{"type": "Feature", "geometry": {"type": "Polygon", "coordinates": [[[-122.45004426230204, 37.77462573231635, 0], [-122.45005814149769, 37.77469350183866, 0], [-122.44969515193891, 37.774739669796645, 0], [-122.44968127306583, 37.774671900232214, 0], [-122.45004426230204, 37.77462573231635, 0]]]}, "properties": {"TO_ST": "51", "BLKLOT": "1189002B", "STREET": "CLAYTON", "FROM_ST": "49", "LOT_NUM": "002B", "ST_TYPE": "ST", "ODD_EVEN": "O", "BLOCK_NUM": "1189", "MAPBLKLOT": "1189002B"}}' WHEREid = 43253; QueryOK, 1 rowaffected (0.00 sec) Rowsmatched: 1  Changed: 1  Warnings: 0   mysql> SELECT * FROMfeaturesWHEREJSON_VALID(feature)=0/G                                                                      Emptyset (1.36 sec) 

Step 2: ALTER TABLE

Provided the data is all valid, it is now time to change the column definition:

mysql> ALTER TABLE features CHANGE feature feature JSON NOT NULL; Query OK, 206560 rows affected (10.84 sec) Records: 206560  Duplicates: 0  Warnings: 0
mysql> ALTERTABLEfeaturesCHANGEfeaturefeatureJSONNOT NULL; QueryOK, 206560 rowsaffected (10.84 sec) Records: 206560  Duplicates: 0  Warnings: 0 

As expected, whitespace information is lost in the conversion to a native type . I should note however that since JSON does not support comments , no other information will be lost.

The ALTER TABLE operation here will need to rebuild the primary key internally, and change data type is one of the few operations currently not covered by Online DDL . For large tables you may consider performing this operation first on a slave, or emulating online ddl via triggers.

But, that’s it! You are now using the native JSON data type

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Upgrading JSON data stored in TEXT columns

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
分享按钮