写入 MySQL 5.7 表 的 json 列类型下的数据的键顺序被打乱的排查分析
1、json 数据的原始格式如下。其键名的顺序如下：TexGEtcUx、QkGyGZ2ST、yE9gC1kxT、Q1yqz2RBi。如图1
图1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | { "sections": { "TexGEtcUx": { "type": "main-index-carousel", "settings": { "loop": true, "autoplay": true, "height": "full", "interval": 5 }, "blocks": { "slide-0": { "type": "slide", "settings": { "image": "", "url": "/", "align": "center", "title": "Image with text overlay", "description": "Use overlay text to give your customers insight into your brand. Select imagery and text that relates to your style and story.", "button": "Shop Now", "opacity": 100, "button_background_color": "#ffffff", "button_color": "#000000", "color": "#ffffff" } }, "slide-1": { "type": "slide", "settings": { "image": "", "url": "/", "align": "center", "title": "Image with text overlay", "description": "Use overlay text to give your customers insight into your brand. Select imagery and text that relates to your style and story.", "button": "Shop Now", "opacity": 100, "button_background_color": "#ffffff", "button_color": "#000000", "color": "#ffffff" } } } }, "QkGyGZ2ST": { "type": "collections" }, "yE9gC1kxT": { "type": "products" }, "Q1yqz2RBi": { "type": "apps", "blocks": { "newsletter": { "type": "internal/newsletter/blocks/newsletter" } } } }} |
2、执行插入数据库的代码如下，打印 $extra[‘schema’]
1 2 3 4 5 6 7 8 9 10 | $asset = ThemeAsset::withoutVersion(function () use($predicate, $extra) { if ($predicate['asset_key'] == 'pages/index.blade.php') { print_r($extra['schema']); exit; } return ThemeAsset::updateOrCreate( $predicate, $extra );}); |
3、打印 $extra[‘schema’] ，其等于 json 数据的原始格式 。如图2
![打印 $extra['schema'] ，其等于 json 数据的原始格式](https://www.shuijingwanwq.com/wp-content/uploads/2022/09/2-6.png)
图2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | { "sections": { "TexGEtcUx": { "type": "main-index-carousel", "settings": { "loop": true, "autoplay": true, "height": "full", "interval": 5 }, "blocks": { "slide-0": { "type": "slide", "settings": { "image": "", "url": "/", "align": "center", "title": "Image with text overlay", "description": "Use overlay text to give your customers insight into your brand. Select imagery and text that relates to your style and story.", "button": "Shop Now", "opacity": 100, "button_background_color": "#ffffff", "button_color": "#000000", "color": "#ffffff" } }, "slide-1": { "type": "slide", "settings": { "image": "", "url": "/", "align": "center", "title": "Image with text overlay", "description": "Use overlay text to give your customers insight into your brand. Select imagery and text that relates to your style and story.", "button": "Shop Now", "opacity": 100, "button_background_color": "#ffffff", "button_color": "#000000", "color": "#ffffff" } } } }, "QkGyGZ2ST": { "type": "collections" }, "yE9gC1kxT": { "type": "products" }, "Q1yqz2RBi": { "type": "apps", "blocks": { "newsletter": { "type": "internal/newsletter/blocks/newsletter" } } } }} |
4、查看写入表中的数据，怀疑其 json 的键的顺序是基于字母顺序排列的。Q1yqz2RBi、QkGyGZ2ST、TexGEtcUx、yE9gC1kxT。而且其内部的子 json 结构的键的顺序也发生了变化。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | { "sections": { "Q1yqz2RBi": { "type": "apps", "blocks": { "newsletter": { "type": "internal/newsletter/blocks/newsletter" } } }, "QkGyGZ2ST": { "type": "collections" }, "TexGEtcUx": { "type": "main-index-carousel", "blocks": { "slide-0": { "type": "slide", "settings": { "url": "/", "align": "center", "color": "#ffffff", "image": "", "title": "Image with text overlay", "button": "Shop Now", "opacity": 100, "description": "Use overlay text to give your customers insight into your brand. Select imagery and text that relates to your style and story.", "button_color": "#000000", "button_background_color": "#ffffff" } }, "slide-1": { "type": "slide", "settings": { "url": "/", "align": "center", "color": "#ffffff", "image": "", "title": "Image with text overlay", "button": "Shop Now", "opacity": 100, "description": "Use overlay text to give your customers insight into your brand. Select imagery and text that relates to your style and story.", "button_color": "#000000", "button_background_color": "#ffffff" } } }, "settings": { "loop": true, "height": "full", "autoplay": true, "interval": 5 } }, "yE9gC1kxT": { "type": "products" } }} |
5、修改表列的类型为 text 后，json 的顺序与原始的保持一致。如图3
图3
近期评论