--------------
|
SHOW VARIABLES LIKE "%CHAR%"
|
--------------
|
|
+--------------------------+----------------------------+
|
| Variable_name | Value |
|
+--------------------------+----------------------------+
|
| character_set_client | utf8 |
|
| character_set_connection | utf8 |
|
| character_set_database | utf8 |
|
| character_set_filesystem | binary |
|
| character_set_results | utf8 |
|
| character_set_server | utf8 |
|
| character_set_system | utf8 |
|
| character_sets_dir | /usr/share/mysql/charsets/ |
|
+--------------------------+----------------------------+
|
8 rows in set (0.00 sec)
|
|
--------------
|
CREATE DATABASE IF NOT EXISTS test
|
--------------
|
|
Query OK, 1 row affected, 1 warning (0.00 sec)
|
|
Note (Code 1007): Can't create database 'test'; database exists
|
--------------
|
CREATE OR REPLACE TABLE table_coupon_varchar (
|
coupon_no varchar(10) NOT NULL DEFAULT '',
|
memo varchar(30) DEFAULT NULL,
|
discount double DEFAULT NULL,
|
PRIMARY KEY (coupon_no)
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8
|
--------------
|
|
Query OK, 0 rows affected (0.04 sec)
|
|
--------------
|
INSERT INTO table_coupon_varchar VALUES ("4","abc'def",250),("5","abc'def",250),("6","gh\\ij",200),("7","gh\\ij",200)
|
--------------
|
|
Query OK, 4 rows affected (0.00 sec)
|
Records: 4 Duplicates: 0 Warnings: 0
|
|
--------------
|
SELECT * FROM table_coupon_varchar
|
--------------
|
|
+-----------+---------+----------+
|
| coupon_no | memo | discount |
|
+-----------+---------+----------+
|
| 4 | abc'def | 250 |
|
| 5 | abc'def | 250 |
|
| 6 | gh\ij | 200 |
|
| 7 | gh\ij | 200 |
|
+-----------+---------+----------+
|
4 rows in set (0.00 sec)
|
|
--------------
|
CREATE OR REPLACE TABLE maria_table_coupon_varchar
|
ENGINE=CONNECT DEFAULT CHARSET=utf8 TABLE_TYPE='MYSQL' TABNAME='table_coupon_varchar' DBNAME='test' OPTION_LIST='user=root,host=10.0.1.36'
|
--------------
|
|
Query OK, 0 rows affected (0.04 sec)
|
|
--------------
|
SELECT * FROM maria_table_coupon_varchar
|
--------------
|
|
+-----------+------+----------+
|
| coupon_no | memo | discount |
|
+-----------+------+----------+
|
| 1 | abc | 250 |
|
| 2 | def | 200 |
|
| 3 | ghi | 300 |
|
+-----------+------+----------+
|
3 rows in set (0.00 sec)
|
|
--------------
|
INSERT INTO maria_table_coupon_varchar SELECT coupon_no, memo, discount FROM table_coupon_varchar WHERE coupon_no = "4"
|
--------------
|
|
--------------
|
SELECT * FROM maria_table_coupon_varchar
|
--------------
|
|
+-----------+------+----------+
|
| coupon_no | memo | discount |
|
+-----------+------+----------+
|
| 1 | abc | 250 |
|
| 2 | def | 200 |
|
| 3 | ghi | 300 |
|
+-----------+------+----------+
|
3 rows in set (0.00 sec)
|
|
--------------
|
INSERT INTO maria_table_coupon_varchar SELECT coupon_no, REPLACE( memo, "'", "''"), discount FROM table_coupon_varchar WHERE coupon_no = "5"
|
--------------
|
|
Query OK, 1 row affected (0.00 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
--------------
|
SELECT * FROM maria_table_coupon_varchar
|
--------------
|
|
+-----------+---------+----------+
|
| coupon_no | memo | discount |
|
+-----------+---------+----------+
|
| 1 | abc | 250 |
|
| 2 | def | 200 |
|
| 3 | ghi | 300 |
|
| 5 | abc'def | 250 |
|
+-----------+---------+----------+
|
4 rows in set (0.00 sec)
|
|
--------------
|
INSERT INTO maria_table_coupon_varchar SELECT coupon_no, memo, discount FROM table_coupon_varchar WHERE coupon_no = "6"
|
--------------
|
|
Query OK, 1 row affected (0.01 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
--------------
|
SELECT * FROM maria_table_coupon_varchar
|
--------------
|
|
+-----------+---------+----------+
|
| coupon_no | memo | discount |
|
+-----------+---------+----------+
|
| 1 | abc | 250 |
|
| 2 | def | 200 |
|
| 3 | ghi | 300 |
|
| 5 | abc'def | 250 |
|
| 6 | ghij | 200 |
|
+-----------+---------+----------+
|
5 rows in set (0.00 sec)
|
|
--------------
|
INSERT INTO maria_table_coupon_varchar SELECT coupon_no, REPLACE( memo, "\\", "\\\\"), discount FROM table_coupon_varchar WHERE coupon_no = "7"
|
--------------
|
|
Query OK, 1 row affected (0.00 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
--------------
|
SELECT * FROM maria_table_coupon_varchar
|
--------------
|
|
+-----------+---------+----------+
|
| coupon_no | memo | discount |
|
+-----------+---------+----------+
|
| 1 | abc | 250 |
|
| 2 | def | 200 |
|
| 3 | ghi | 300 |
|
| 5 | abc'def | 250 |
|
| 6 | ghij | 200 |
|
| 7 | gh\ij | 200 |
|
+-----------+---------+----------+
|
6 rows in set (0.00 sec)
|
|
Bye
|
test4_script_server1.log
test4_script_server1.sql