在MySQL中将JSON值设置为文本变量错误
在MySQL中将JSON值设置为文本变量错误
提问于 2020-03-12 14:43:54
更新:这是JSON代码的Pastebin。如果有人能告诉我为什么MySQL拒绝将它作为文本变量来阅读，以及如何绕过它，我将不胜感激：https://pastebin.com/ju8xPPsV
所以我这里有个奇怪的。在Windows上运行8.0.18。我的环境都是utf8mb4和utf8mb4_unicode_ci。
问题是，MySQL拒绝将附加的JSON文本读入文本字段，无论定义了哪个字符集，在尝试将文本变量设置为代码时总是会出现以下错误：
错误1366 (HY000)：不正确的字符串值：‘\X962019.’用于第1行的“json_text”列
很明显，文本中有一个MySQL不喜欢的字符，但是这个文本是什么字符，在哪里，以及我如何‘擦除’这个文本才能被接受呢？
这发生在试图将JSON赋给文本值的行上：
set json_text= JSON_EXTRACT((injason), CONCAT('$.', 'in_JSON'));以下是完整的代码：
DROP PROCEDURE IF EXISTS `sp_jason`;
DELIMITER //
CREATE DEFINER=`root`@`localhost`
PROCEDURE `sp_jason`(
IN parameterName VARCHAR(10)
)
COMMENT 'schema_version_applied_to: v1.0.0'
master_block:BEGIN
DECLARE json_text text CHARACTER SET latin1;
DECLARE injason JSON;
DECLARE exit handler for SQLEXCEPTION, SQLWARNING, NOT FOUND
BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;
END;
SET @hugeJason='
{
"in_JSON": {
"data_value": {
"name": "Wesley Snell",
"about": "Over 15 years of R&D development on over 30 products in various categories (automotive, medical, telecom, mobil devices, navigation, security, industrial automation, consumer products, CAD, hospitality solutions, kiosk, point of sale ...",
"email": "wesley@qualcomm.com",
"articles": [
{
"linkURL": "https://www.linkedin.com/pulse/invitation-part-state-ai-ml-december-2019-january-2020-kiran-gunnam"
}
],
"nameLast": "Snell",
"education": [
{
"degreeName": "Master\'s degree",
"schoolName": "University of California, San Diego - Jacobs School of Engineering",
"description": "The next step in the wireless revolution is the connection of everyday devices through wireless technology. Embedded wireless technology is today exploding in nearly every market sector from personal electronics and medical devices, to the transportation infrastructure and manufacturing. ...",
"fieldOfStudy": "Wireless Embedded Systems",
"schoolImageURL": "https://media-exp1.licdn.com/dms/image/C4D0BAQHEc15VMuDaGg/company-logo_100_100/0?e=1589414400&v=beta&t=VA3iTzTB4ZBq-iYp_Fd4FYbCG-4YV7gDL47JCXaMPk4",
"activitiesAndSocieties": "Minor study in Digital Hardware Engineering and Business Management",
"datesAttendedOrGraduationString": "2017 – 2019"
}
],
"interests": [
{
"title": "Ford Motor Company",
"imageURL": "https://media-exp1.licdn.com/dms/image/C4D0BAQGUxHv2MadZ9w/company-logo_100_100/0?e=1589414400&v=beta&t=t6v_lnhbQYiUjdGSiPBSQ89PpTMges1JsxsBQHtSfSY",
"occupation": null,
"followerCount": 2254781
},
{
"title": "Bill Gates",
"imageURL": "https://media-exp1.licdn.com/dms/image/C5603AQHv9IK9Ts0dFA/profile-displayphoto-shrink_100_100/0?e=1586390400&v=beta&t=oC0Mo8Qep7-PeP7TSjNEx94AbX4um89htO25XGhJZFE",
"occupation": "Co-chair, Bill & Melinda Gates Foundation",
"followerCount": 24625634
}
],
"nameFirst": "Wesley",
"experience": [
{
"roles": [
{
"jobType": "full-time",
"jobTitle": "Senior Staff Engineer - Machine Learning , Computer Vision & IOT (R&D Division)",
"location": null,
"currentRole": true,
"jobDescription": "Working on machine learning based computer vision based projects on embedded platforms. Heavy use of programming in C. Modern C++, Python and MATLAB. Heavy interaction in hardware, schematic review and inter process communications.",
"employmentDuration": {
"datesEmployedEnd": "Present",
"datesEmployedStart": "2019",
"datesEmployedString": "2019 – Present",
"employmentDurationYears": 1,
"employmentDurationString": "1 yr"
}
},
{
"jobType": "full-time",
"jobTitle": "Senior Staff Engineer - Android Team & Wireless Communications (Wireless R&D Division)",
"location": null,
"currentRole": true,
"jobDescription": "A year plus of Android software development for mobile applications with coding in Java and c++ with the NDK. ...",
"employmentDuration": {
"datesEmployedEnd": "Present",
"datesEmployedStart": "2019",
"datesEmployedString": "",
"employmentDurationYears": 1,
"employmentDurationString": "1 yr"
}
},
{
"jobType": null,
"jobTitle": "Senior Staff Engineer - R&D Division (Platform & Tools Lead)",
"location": "San Diego",
"currentRole": false,
"jobDescription": "Working on new Research and Developments projects, for state of the art products. ...",
"employmentDuration": {
"datesEmployedEnd": "Present",
"datesEmployedStart": "Apr 2016",
"datesEmployedString": "Apr 2016 – Present",
"employmentDurationYears": 3,
"employmentDurationMonths": 11,
"employmentDurationString": "3 yrs 11 mos"
}
}
],
"currentJob": true,
"companyName": "Qualcomm",
"employmentDurationYears": 14,
"employmentDurationString": "14 yrs 7 mos"
},
{
"roles": [
{
"jobType": null,
"jobTitle": "President/Founder",
"location": "San Diego",
"currentRole": false,
"jobDescription": "Many projects in the following areas. - Internet Applications (JavaScript/ HTML/ SQL). ...",
"employmentDuration": {
"datesEmployedEnd": "Feb 2008",
"datesEmployedStart": "Jan 1998",
"datesEmployedString": "Jan 1998 – Feb 2008",
"employmentDurationYears": 10,
"employmentDurationString": "10 yrs 2 mos"
}
}
],
"currentJob": false,
"companyName": "Creative Logic",
"employmentDurationYears": 10,
"employmentDurationString": "10 yrs 2 mos"
},
{
"roles": [
{
"jobType": null,
"jobTitle": "Principal R&D Engine",
"location": null,
"currentRole": false,
"jobDescription": null,
"employmentDuration": {
"datesEmployedEnd": "Dec 1999",
"datesEmployedStart": "Jan 1998",
"datesEmployedString": "Jan 1998 – Dec 1999 2 yrs",
"employmentDurationYears": 2,
"employmentDurationString": "2 yrs"
}
}
],
"currentJob": false,
"companyName": "Infogation",
"employmentDurationYears": 10,
"employmentDurationString": "Jan 1998 – Dec 1999"
}
],
"nameMiddle": "",
"profileURL": "https://www.linkedin.com/in/wesleysnell",
"currentCompany": {
"name": "123",
"imageURL": "https://media-exp1.licdn.com/dms/image/C4E0BAQFIzy7XU945xg/company-logo_100_100/0?e=1589414400&v=beta&t=eIPPusNFhCPBHri11dcijhuyiSsYWJUehf_KUOFCvXQ"
},
"accomplishments": [
{
"projects": [
{
"data": "Jun 2017",
"title": "Data Scientist Competition winner",
"issuer": "DataCamp",
"subTitle": "Jun 2017 DataCamp",
"description": "Data Scientist with Python Career Track on DataCamp"
},
{
"data": "Jun 2017",
"title": "Data Scientist Competition winner",
"issuer": "DataCamp",
"subTitle": "Jun 2017 DataCamp",
"description": "Data Scientist with Python Career Track on DataCamp"
}
],
"honorAndAwards": [
{
"data": "Jun 2017",
"title": "Data Scientist Competition winner",
"issuer": "DataCamp",
"subTitle": "Jun 2017 DataCamp",
"description": "Data Scientist with Python Career Track on DataCamp"
},
{
"data": "Jun 2017",
"title": "Data Scientist Competition winner",
"issuer": "DataCamp",
"subTitle": "Jun 2017 DataCamp",
"description": "Data Scientist with Python Career Track on DataCamp"
}
]
}
],
"currentJobTitle": "Senior Staff Engineer - Machine Learning , Computer Vision & IOT (R&D Division) at Qualcomm",
"currentLocation": "San Diego, California",
"profileImageURL": "https://media-exp1.licdn.com/dms/image/C4D03AQGoa2edXG6hWA/profile-displayphoto-shrink_200_200/0?e=1586390400&v=beta&t=f-Rv8pyCxIAuDRfK8g6AxkNHf-t3MGbZRfCvcOFghoY",
"recommendations": {
"given": [
{
"recommender": {
"name": "Steve Jobs",
"headline": "Founder Apple Inc.",
"relation": "October 22, 2010, Steve managed Wesley directly"
},
"recommendation": "Wes is one of the sharpest engineers I have worked with. His biggest assets are his technical versatility and engineering creativity ..."
},
{
"recommender": {
"name": "Bill Gates",
"headline": "Founder Windows Inc.",
"relation": "October 22, 2015, Bill was senior to Wesley but didn\'t manage"
},
"recommendation": "Wes is one of the sharpest engineers I have worked with. His biggest assets are his technical versatility and engineering creativity ..."
}
],
"received": [
{
"recommender": {
"name": "Steve Jobs",
"headline": "Founder Apple Inc.",
"relation": "October 22, 2010, Steve managed Wesley directly"
},
"recommendation": "Wes is one of the sharpest engineers I have worked with. His biggest assets are his technical versatility and engineering creativity ..."
},
{
"recommender": {
"name": "Bill Gates",
"headline": "Founder Windows Inc.",
"relation": "October 22, 2015, Bill was senior to Wesley but didn\'t manage"
},
"recommendation": "Wes is one of the sharpest engineers I have worked with. His biggest assets are his technical versatility and engineering creativity ..."
}
],
"givenCount": 7,
"receivedCount": 15
},
"connectionDegree": 3,
"numberOfConnections": 500,
"volunteerExperience": [
{
"cause": "Environment",
"companyName": "Pacific Ridge Community",
"description": "Community board planning member for everything in the Sorrento Valley Community. ...",
"designation": "Board Member (past Treasurer)",
"volunteerDuration": "16 yrs 2 mos",
"datesVolunteeredString": "Jan 2004 – Present"
}
],
"connectionDateString": "June 14, 2014",
"skillsAndEndorsements": {
"topSkills": [
{
"skillName": "Embedded Systems",
"eliteEndorsers": [
{
"name": "Sean Liming",
"headline": "Owner of AnnaBooks, LLC."
},
{
"name": "Franz Fisher",
"headline": "Director of NASA"
}
],
"endorsementCount": 79,
"colleagueEndorsers": [
{
"name": "Sean Liming",
"headline": "Owner of AnnaBooks, LLC."
},
{
"name": "Franz Fisher",
"headline": "Director of NASA"
}
]
},
{
"skillName": "Wireless",
"endorsementCount": 78
}
],
"otherSkills": [
{
"skillName": "Hardware",
"endorsementCount": 2
},
{
"skillName": "USB",
"endorsementCount": 6
}
],
"industrySkills": [
{
"skillName": "Mobile Devices",
"endorsementCount": 50
},
{
"skillName": "Wireless",
"endorsementCount": 78
}
],
"toolsAndTechnologies": [
{
"skillName": "C++",
"endorsementCount": 15
},
{
"skillName": "Andriod",
"endorsementCount": 9
}
]
},
"licensesAndCertifications": [
{
"issuedDate": "Nov 2019",
"Issuing authority": "Udemy",
"certificationName": "Certified Computer Professional (CCP)",
"certificationLinkURL": "https://www.udemy.com/certificate/UC-8U59W6QZ",
"credentialIdentifier": "Credential ID UC-8U59W6QZ"
}
]
},
"data_state_c": 1
},
"in_JSON_value": "data_value",
"in_sql_insert_1": "data_state_c,",
"in_sql_insert_2": "1,"
}
';
set injason = cast(@hugeJason as json);
set json_text= JSON_EXTRACT((injason), CONCAT('$.', 'in_JSON'));
select json_text;
END master_block//
DELIMITER ;
call sp_jason(1);有人知道是什么引起了这个问题吗?尼克！你在外面吗？)
回答 2
Stack Overflow用户
发布于 2020-03-12 15:08:56
也许这个答案对你有帮助：MySQL warning: Incorrect string value: '\x96
十六进制96大概是n-破折号(-)的latin1编码。但是您已经指定CSV文件是utf8 8编码的(或utf8mb4)，这个字符对于utf8来说是不可理解的。计划A:更改文件。(这可能不太实际。)
计划B:告诉MySQL文件是latin1 (与utf8相反)。然后MySQL将正确地将其转换为utf8 8编码的E28093。
“排序”与排序和比较有关；“字符集”与‘编码’有关。
可能包含查询的文件被错误地编码。
Stack Overflow用户
发布于 2020-03-12 16:59:10
因此，我已经通过将文本粘贴到https://jsonformatter.curiousconcept.com/中解决了这个问题，它显示JSON结构很好，而且是绿色的，但是它说“逃脱了，没有逃脱的绞车”。遗憾的是，它并没有告诉我在哪里或者哪个角色，而疯狂的是“(例如)”的三个字符已经被转义了。所以所有这些都没有任何意义，但很明显，MySQL在管理某些类型的文本时遇到了严重的问题，甚至试图将文本定义为utf8或utf8mb4，甚至latin1都不会对这样的问题产生任何影响，这是荒谬的。遗憾的是，我没有从这个“修复”中学到任何东西，仍然不知道为什么会发生错误，以及为什么我的伙伴(一个MySQL DBA)能够在完全相同的代码在我的端造成错误的情况下没有任何问题地运行代码；但是现在当我在json处理站点上运行它时，它已经神奇地修复了(记住，问题是将它设置为文本值，最终与JSON格式无关)。我在MySQL中遇到的每一个问题中，95%都与字符集或排序问题有关。我从未遇到过这样的MSSQL问题。我后悔选择了MySQL。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/60656459
复制相关文章
相似问题
腾讯云开发者
