Handling Chinese Characters in MySQL

When inserting Chinese characters into database fields, ensure proper encoding by using:

string_value.encode('utf8')

To modify a column's character set to support full UTF-8 including emoji characters:

ALTER TABLE case_test_point 
MODIFY COLUMN `tp_name` VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;

Example for a different column:

ALTER TABLE go_ppa_scaling_factor 
MODIFY COLUMN `worst_corner` VARCHAR(45) CHARACTER SET utf8mb4 DEFAULT NULL;

Updtaing Related Tables in MySQL

To update records in one table based on matching records from another table:

UPDATE go_engage_ppa_product_item pi, 
     go_engage_ppa_items p 
SET pi.item_name = p.item_name 
WHERE p.id = pi.item_id;

Automatic Timestamp Management

Configure automatic timestamps for tracking record lifecycle:

-- Auto capture creation timestamp
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

-- Auto update timestamp on record modification
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Resolving "Incorrect String Value" Errors

This error typically occurs when character set settings don't match the data being inserted. Fix it by modifying the column's character set:

ALTER TABLE `email_notice`.`go_engage_ppa_process` 
MODIFY COLUMN `typical_corner` VARCHAR(45) 
CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;

Handling Zero Date Values

When importing data containing 0000-00-00 00:00:00 datetime values, MySQL may reject them with ERROR 1292 if sql_mode includes NO_ZERO_DATE.

Check current sql_mode:

SHOW VARIABLES LIKE 'sql_mode';

Temporary Fix (resets on server restart):

SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Permanent Fix — Add to MySQL configuration file (my.cnf or mysqld.cnf for MySQL 5.7 at /etc/mysql/mysql.conf.d/mysqld.cnf):

sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

Restart the MySQL service after making configuration changes.