Chinese characters are garbled in the MySQL database

[goomoo]

When I wrote MySQL from Purebasic, all the Chinese characters were garbled. Here's the snippet:

Code: Select all

XIncludeFile "MySQL_const.pbi"

Structure znb_user
  uuid_user.s{38} ;文本型。user 全球唯一编号。
  organization_UUID.s{38} ;文本型。organization 全球唯一编号。
  sUsername.s{50} ;文本型。用户名。
  sTrueName.s{50} ;文本型。真名。
  sPasswordMD5.s{50}  ;文本型。密码MD5值去后面23位。
  sUserTel.s{50}  ;文本型。用户电话号码。
  sUserEMail.s{50}  ;文本型。用户电子邮箱。
  iWrite.l  ;整数型。是否可写。1：可写；0：不可写。
  iPrivilege.l  ;整数型。权限。0：本公司管理员；1：客户公司管理员；2：在线监测员。
  sUserMemo.s{255}  ;文本型。用户备注。
  userCreateDate.s{19} ;日期时间型。用户创建时间。如：2023-03-20 09:40:29 总共19个字符
  userUpdateDate.s{19}  ;日期时间型。用户的修改时间。
  isBloocked.l  ;整数型。是否被阻止。0：不阻止。1：阻止；
EndStructure

Procedure.s insert_znb_user(mysql_conn,*user.znb_user)
  
  If mysql_conn<=0
    ProcedureReturn #MySQL_DB_link_Less_equal_To_0
  EndIf
  
  If IsDatabase(mysql_conn)=0
    ProcedureReturn #MySQL_DB_link_Not_exist
  EndIf
  
  sSql.s="INSERT INTO `user`(`uuid_user`, `organization_UUID`, `sUsername`, `sTrueName`, `sPasswordMD5`, `sUserTel`, `sUserEMail`, `iWrite`, `iPrivilege`, `sUserMemo`, `userCreateDate`, `userUpdateDate`, `isBloocked`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

  SetDatabaseString(mysql_conn,0,*user\uuid_user)
  SetDatabaseString(mysql_conn,1,*user\organization_UUID)
  SetDatabaseString(mysql_conn,2,*user\sUsername)  
  SetDatabaseString(mysql_conn,3,*user\sTrueName)
  SetDatabaseString(mysql_conn,4,*user\sPasswordMD5)
  SetDatabaseString(mysql_conn,5,*user\sUserTel)
  SetDatabaseString(mysql_conn,6,*user\sUserEMail)
  SetDatabaseLong(mysql_conn,7,*user\iWrite)
  SetDatabaseLong(mysql_conn,8,*user\iPrivilege)
  SetDatabaseString(mysql_conn,9,*user\sUserMemo)
  SetDatabaseString(mysql_conn,10,*user\userCreateDate)
  SetDatabaseString(mysql_conn,11,*user\userUpdateDate)
  SetDatabaseLong(mysql_conn,12,*user\isBloocked)

  DatabaseUpdate(mysql_conn,sSql)

  ProcedureReturn DatabaseError()
EndProcedure

Code: Select all

XIncludeFile "MySQLFuntions/uuid.pbi"
XIncludeFile "MySQLFuntions/MySQL_includes.pbi"

; Structure znb_user
;   uuid_user.s{38} ;文本型。user 全球唯一编号。
;   organization_UUID.s{38} ;文本型。organization 全球唯一编号。
;   sUsername.s{50} ;文本型。用户名。
;   sTrueName.s{50} ;文本型。真名。
;   sPasswordMD5.s{50}  ;文本型。密码MD5值去后面23位。
;   sUserTel.s{50}  ;文本型。用户电话号码。
;   sUserEMail.s{50}  ;文本型。用户电子邮箱。
;   iWrite.l  ;整数型。是否可写。1：可写；0：不可写。
;   iPrivilege.l  ;整数型。权限。0：本公司管理员；1：客户公司管理员；2：在线监测员。
;   sUserMemo.s{255}  ;文本型。用户备注。
;   userCreateDate.l  ;日期时间型。用户创建时间。
;   userUpdateDate.l  ;日期时间型。用户的修改时间。
;   isBloocked.l  ;整数型。是否被阻止。0：不阻止。1：阻止；
; EndStructure

user.znb_user

uuid.UUID
uuid_Create(@uuid)
user\uuid_user=uuid_ToString(@uuid)
;Debug "user\uuid_user: " + user\uuid_user
;user\uuid_user: 833f54d8-0538-4376-8fef-8700c26a7e54
;user\uuid_user: c668a64c-3ba4-4c30-b63f-56170d014a9c

user\organization_UUID="25e03ebc-fad3-11ed-a750-3085a998715d"
user\sUsername="lkbt"
user\sTrueName="零空比特"
user\sPasswordMD5="`123asdf"
user\sUserTel="13812345678"
user\sUserEMail="www_znb_cc@qq.com"
user\iWrite=0
user\iPrivilege=2
user\sUserMemo="www.znb.cc"
user\userCreateDate=FormatDate("%yyyy-%mm-%dd %hh:%ii:%ss", Date())
user\userUpdateDate=FormatDate("%yyyy-%mm-%dd %hh:%ii:%ss", Date())
user\isBloocked=0

;Debug "user\sTrueName: " + user\sTrueName
;[u]user\sTrueName: 零空比特[/u]    ; <----- This is common.

mysql_conn=OpenDatabase(#PB_Any, "host=localhost port=3306 dbname=lksensor", "root", "`123QWERasdf" , #PB_Database_MySQL)

If mysql_conn<=0 
  MessageRequester("LKSensor","数据库链接有问题。")
  End
EndIf

Debug insert_znb_user(mysql_conn,@user)
;Incorrect datetime value: '1685375424' for column 'userCreateDate' at row 1
;保存成功，返回空文本。

CloseDatabase(mysql_conn)

mysql is already created, it's UTF8,

Code: Select all

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50726
 Source Host           : localhost:3306
 Source Schema         : lksensor

 Target Server Type    : MySQL
 Target Server Version : 50726
 File Encoding         : 65001

 Date: 27/05/2023 16:48:34
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

How to display it correctly?

[mk-soft]

I can only test it with copy and paste of the characters on my NAS MySQL server with a dummy database table.
Here the input and output is the same

Which version of PB and which database driver is used.

Also, I don't know if the complete Chinese character set is available in UCS-16.
PB uses UCS-16 and not UTF-16 and some characters may have to be converted from UTF-16 to UCS-16.

[goomoo]

PureBasic 6.00 LTS (Windows - x64)
Feel the ..Pure.. Power

MySQL 5.7.2

Code: Select all

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `uuid_user` varchar(38) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '文本型。user 全球唯一编号。',
  `organization_UUID` varchar(38) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '文本型。organization 全球唯一编号。',
  `sUsername` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '文本型。用户名。',
  `sTrueName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '文本型。真名。',
  `sPasswordMD5` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '文本型。密码MD5值去后面23位。',
  `sUserTel` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '文本型。用户电话号码。',
  `sUserEMail` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '文本型。用户电子邮箱。',
  `iWrite` int(1) NOT NULL DEFAULT 0 COMMENT '整数型。是否可写。1：可写；0：不可写。',
  `iPrivilege` int(4) NOT NULL DEFAULT 2 COMMENT '整数型。权限。0：本公司管理员；1：客户公司管理员；2：在线监测员。',
  `sUserMemo` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '文本型。用户备注。',
  `userCreateDate` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '日期时间型。用户创建时间。',
  `userUpdateDate` datetime(0) NOT NULL COMMENT '日期时间型。用户的修改时间。',
  `isBloocked` int(4) NOT NULL DEFAULT 0 COMMENT '整数型。是否被阻止。0：不阻止。1：阻止；',
  PRIMARY KEY (`uuid_user`) USING BTREE,
  UNIQUE INDEX `idx_sUsername`(`sUsername`) USING BTREE,
  INDEX `idx_uuid_user`(`uuid_user`) USING BTREE,
  INDEX `idx_organization_UUID`(`organization_UUID`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('48ea3b72-faab-11ed-a750-3085a998715d', '25e03ebc-fad3-11ed-a750-3085a998715d', 'lk', '朗开', '`123asdf', '', ' ', 1, 0, '', '2023-05-27 14:56:47', '2023-04-23 14:52:38', 0);
INSERT INTO `user` VALUES ('59a312dc-faab-11ed-a750-3085a998715d', '25e03ebc-fad3-11ed-a750-3085a998715d', 'lf', '刘峰', '`123asdf', '', ' ', 1, 0, '', '2023-05-27 14:56:50', '2023-03-20 09:40:29', 0);

[goomoo]

Can you convert a string to a snippet of code?

UTF-16 to UCS-16

I can only test it with copy and paste of the characters on my NAS MySQL server with a dummy database table.
Here the input and output is the same

Which version of PB and which database driver is used.

Also, I don't know if the complete Chinese character set is available in UCS-16.
PB uses UCS-16 and not UTF-16 and some characters may have to be converted from UTF-16 to UCS-16.

[benubi]

It seems to work most of the time.

I suspect the error-tuplet sTrueUsername to be encoded in UTF8, but in the MySQL database it's type is stored as ASCII. A UTF8 string was stored (marked) as ASCII, but it's in "reality" it is UTF8. Therefore the DB gives you a broken string. This already happens probably when adding.

It's better to use SQL statements with "?" instead of making them with string concatenation.

Maybe one control character is missing. Maybe a UTF8 signature (BOM) is missing at the start of the string or is broken.

Apparently no conversion between UCS-16 and UTF-16 are needed (character numbers are identical):

https://en.wikipedia.org/wiki/Universal ... racter_Set

What is the first Chinese character of the true name & does the error appear with other names that start with the same character?

[mk-soft]

On my old NAS, MySQL text fields are defined as 'utf8_general_ci'. Only the NAS web interface does not get the output right (output as ASCII). But this is an error of the web interface.

[goomoo]

The original purebasic system built-in library problem, purebasic 6.0 version of MySQL built-in library is too old, converted into HeidiSQL built-in library Chinese can be displayed.

Library download:
https://www.znb.cc/_files/libmariadb.zip

help.chm UseMySQLDatabase() Remarks

MySQL and MariaDB (an opensource fork of MySQL) are powerful, server based database managers which support very large database and high concurrency. PureBasic uses opensource MariaDB library to connect MySQL and MariaDB databases seemlessly, which can be used in commercial application without additional licenses. When shipping your PureBasic program, you will need to add 'libmariadb.dll' (Windows), 'libmariadb.so' (Linux) or 'libmariadb.dylib' (OSX) found in the 'PureBasic/Compilers' directory to your package.

https://www.znb.cc/_files/bbs_images/My ... ed_002.png

[goomoo]

On Linux, this refers to Unbuntu , download:

https://mariadb.com/downloads/connectors/



The installation is ready.