Shulou(Shulou.com)05/31 Report--

This article is about how to modify the table character set in Mysql. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it.

1. Export table structure

Mysqldump-uroot-p-- default-character-set=gbk-d sam > createtab.sql

Parameter description:

Default-character-set=gbk indicates what character set to connect with, and-d indicates that only the table structure is exported, not data.

two。 Modify the character set in the createtab.sql script to a new character set

[root@sam ~] # cat createtab.sql

-- MySQL dump 10.13 Distrib 5.6.24, for Linux (x86 / 64)

--

-- Host: localhost Database: sam

-

-- Server version 5.6.24-enterprise-commercial-advanced

/ * 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT * /

/ * 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS * /

/ * 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION * /

/ * 40101 SET NAMES gbk * /

/ * 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /

/ * 40103 SET TIME_ZONE='+00:00' * /

/ * 40014 SET @ OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 * /

/ * 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 * /

/ * 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' * /

/ * 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /

--

-- Table structure for table `sam1`

--

DROP TABLE IF EXISTS `sam1`

/ *! 40101 SET @ saved_cs_client = @ @ character_set_client * /

/ *! 40101 SET character_set_client = utf8 * /

CREATE TABLE `sam1` (

`id`int (11) DEFAULT NULL

`name` varchar (20) DEFAULT NULL

) replace ENGINE=InnoDB DEFAULT CHARSET=latin1 with gbk

/ * 40101 SET character_set_client = @ saved_cs_client * /

/ * 40103 SET TIME_ZONE=@OLD_TIME_ZONE * /

/ * 40101 SET SQL_MODE=@OLD_SQL_MODE * /

/ * 40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS * /

/ * 40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS * /

/ * 40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT * /

/ * 40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS * /

/ * 40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION * /

/ * 40111 SET SQL_NOTES=@OLD_SQL_NOTES * /

-- Dump completed on 2017-06-14 22:39:07

3. Data has no update status, export data

[root@sam] # mysqldump-uroot-p-quick-- no-create-info-- extended-insert-- default-character-set=latin1 sam > samdata.sql

Parameter description:

-- quick this option is used to dump large tables. It forces mysqldump to retrieve rows in the table one row at a time from the server instead of all, and cache it in memory before output

-- no-create-info does not write CREATE TABLE statements that recreate each dump table

-- extended-insert uses a multiline INSERT syntax that includes several VALUES lists. This makes the dump file smaller and can be inserted faster when the file is loaded

-- default-character-set=latin1 exports all data according to the original character set, so that all Chinese characters in the exported file are visible and will not be saved as garbled.

4. Open samdata.sql and change SET NAMES latin1 to SET NAMES gbk

[root@sam ~] # cat samdata.sql

-- MySQL dump 10.13 Distrib 5.6.24, for Linux (x86 / 64)

--

-- Host: localhost Database: sam

-

-- Server version 5.6.24-enterprise-commercial-advanced

/ * 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT * /

/ * 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS * /

/ * 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION * /

/ *! 40101 SET NAMES latin1 changed to gbk*/

/ * 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /

/ * 40103 SET TIME_ZONE='+00:00' * /

/ * 40014 SET @ OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 * /

/ * 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 * /

/ * 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' * /

/ * 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /

--

-- Dumping data for table `sam1`

--

LOCK TABLES `sam1` WRITE

/ *! 40000 ALTER TABLE `sam1` DISABLE KEYS * /

INSERT INTO `sam1` VALUES (1), (2)), (3))

/ *! 40000 ALTER TABLE `sam1` ENABLE KEYS * /

UNLOCK TABLES

/ * 40103 SET TIME_ZONE=@OLD_TIME_ZONE * /

/ * 40101 SET SQL_MODE=@OLD_SQL_MODE * /

/ * 40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS * /

/ * 40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS * /

/ * 40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT * /

/ * 40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS * /

/ * 40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION * /

/ * 40111 SET SQL_NOTES=@OLD_SQL_NOTES * /

-- Dump completed on 2017-06-14 22:47:23

5. Create a new database with a new character set

Mysql > create database sam_gbk default charset gbk

Query OK, 1 row affected (0.00 sec)

6. Create tables and execute createtab.sql

[root@sam ~] # mysql-uroot-p sam_gbk show create table sam1\ G

* * 1. Row *

Table: sam1

Create Table: CREATE TABLE `sam1` (

`id`int (11) DEFAULT NULL

`name` varchar (20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql > select * from sam1

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 2 | b | |

| | 3 | c |

+-+ +

3 rows in set (0.00 sec)

The above is how to modify the table character set in Mysql. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.