云SQL:Python客户端API在CSV文件导入时默认为utf8mb3字符集

Question

我正在使用Google Cloud的导入API将Google Cloud Storage中的CSV文件加载到Cloud SQL MySQL 5.7数据库：

https://cloud.google.com/sql/docs/mysql/admin-api/v1beta4/instances/import

导入API为MySQL数据库生成一个LOAD DATA INFILE命令，该命令采用"utf8"字符集。然而，我的CSV文件中的数据使用"utf8mb4"编码，这是"utf8"的超集。当某些字符串无法编码为"utf8"时，这会导致加载过程失败：

Exception: CloudSQL Exception: {'kind': 'sql#operation', 'selfLink': 'https://www.googleapis.com/sql/v1beta4/projects/***', 'targetProject': '***', 'targetId': '***', 'targetLink': 'https://www.googleapis.com/sql/v1beta4/projects/***', 'name': '0211c99e-0633-42f1-9ee1-069473308273', 'operationType': 'IMPORT', 'status': 'RUNNING', 'user': '***', 'insertTime': '2019-01-14T02:36:39.861Z', 'startTime': '2019-01-14T02:36:39.972Z', 'error': {'kind': 'sql#operationErrors', 'errors': [{'kind': 'sql#operationError', 'code': 'ERROR_RDBMS', 'message': "Import CSV error: Error 1300: Invalid utf8 character string: ''Afikanisitani|'Apekanikana|A Phu Han (Afghanistan)|A Phú Hãn 'n"}]}, 'importContext': {'kind': 'sql#importContext', 'uri': '***', 'database': '**', 'importUser': '', 'csvImportOptions': {'table': '***'}}}

相关片段："消息"："导入CSV错误：错误1300：无效的utf8字符串：">

是否有方法使用导入API添加"utf8mb4"字符集(或任何其他字符集)？

我尝试在"csvImportOptions"字典中添加"字符集"："utf8mb4"，但导入API似乎只需要该字典中的"table"one_answers"columns"键。

注意，如果我直接从MySQL客户端运行LOAD DATA INFILE命令，我就可以导入CSV而不会出现任何问题：

LOAD DATA INFILE 'myCSVFile.csv'
INTO TABLE 'my table'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'n'
IGNORE 1 ROWS

from pprint import pprint
from googleapiclient import discovery
from oauth2client.client import GoogleCredentials
credentials = GoogleCredentials.get_application_default()
service = discovery.build('sqladmin', 'v1beta4', credentials=credentials)
# Project ID of the project that contains the instance.
project = 'my-project'  # TODO: Update placeholder value.
# Cloud SQL instance ID. This does not include the project ID.
instance = 'my-instance'  # TODO: Update placeholder value.
instances_import_request_body = {
"importContext": {
"kind": "sql#importContext",
"fileType": "CSV",
"uri": gcs_uri,
"database": database,
"csvImportOptions": {
"table": table
}
}
}
request = service.instances().import_(project=project, instance=instance, body=instances_import_request_body)
response = request.execute()

附加数据点我很清楚，Google API生成的LOAD DATA INFILE查询默认为"utf8"字符集。

失败，错误消息与API 相同

LOAD DATA INFILE 'problematic.csv'
INTO TABLE my_table
**CHARACTER SET utf8**
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
ERROR 1300 (HY000): Invalid utf8 character string: ''Afikanisitani|'Apekanikana|A Phu Han (Afghanistan)|A Phú Hãn '

作品：

LOAD DATA INFILE 'problematic.csv'
INTO TABLE my_table
**CHARACTER SET utf8mb4**
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
Query OK, 75641 rows affected (1.14 sec)
Records: 75641  Deleted: 0  Skipped: 0  Warnings: 0

此处的文档不正确：https://cloud.google.com/sql/docs/mysql/import-export/importing

LOAD DATA INFILE ... ***CHARACTER SET 'utf8mb4'***
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'.

Answer 1

解决方案是在您的Google Cloud SQL实例上设置以下标志：

character-set-server: utf8mb4

配置数据库标志

CLI命令：

gcloud sql instances patch [INSTANCE_NAME] --database-flags character-set-server=utf8mb4

数据库标志列在settings下，作为集合databaseFlags:

gcloud sql instances describe [INSTANCE_NAME]

警告：任何未包含在patch命令中的标志都将设置回其默认值