Database Migration Summary

database Migration and its differences (oracle,mysql,sqlserver)

Image: MySQL's third-party graphical user interface tool for easy query, design, and export of import data (Navicat)

Xu Liang, 2011-4-1

Introduction: Recent data supermarket projects need to migrate from SQL Server to MySQL. There was not a lot of experience with database migrations, so I didn't know how long it would take and what to do. Through a few days of work, the project has been successfully migrated to MySQL. This document summarizes the experience of migration and facilitates database migration later.

1, What you need to do to migrate your database

1.1 Create TABLE Script modification

1.2 Data import (encoding, data type settings)

1.3 SQL modifications in the project

1.4 Database Connection (drive)

1.5 Connect the program in the project, test and modify the program

2, the common database in the development of different

2.1 Data types

2.2 Self-increasing

2.3-page

2.4 Built-in functions

2.5 Fuzzy Query

3, ORM Tools and Migration

Different efficiencies in database migration using Hibernate, Ibatis

4, comparison of different data types and reference materials for each database

4.1, MySQL in the table SQL

4.2. Java to SQL data type innuendo table

4.3, mysql and Oracle data types of mutual transformation

4.4, Oracle and SQL Server, MySQL data type comparison table

1, database migration needs to do the work

1, 1 database to create a table script modification

1.1.1 Because the data types of various databases are not the same, some data types need to be changed.

1.1.2 In the MySQL script can not set the current time for date data, fields such as: Registertime (registration time), you need to set the current date in the program, save it in the database, or set the Registertime as a timestamp (timestamp)

1.1.3 of tables and fields in MySQL, see the build statement in "5".

1, 2 import data into the target database (where you can modify the data type)

1.2.1 set up the encoding of the database to prevent Chinese garbled

1, the simplest way to modify, is to modify the MySQL My.ini file character Set key values,

such as: Default-character-set = UTF8

(Character_set_server = UTF8)

After the modification, restart the MySQL services, service MySQL restart

2, there is also a way to modify the character set, is the use of MySQL commands , such as:

mysql> Set character_set_client = UTF8;mysql> set character_set_connection = UTF8;

mysql> Set character_set_database = UTF8;mysql> set character_set_results = UTF8;mysql> set Character_set_serve r = UTF8;mysql> set collation_connection = UTF8;mysql> set collation_database = UTF8; mysql> SET collation_server = UTF8;

If: The first two is not set, the encoding can be implemented in the following ways:

A database is set up when the database support encoding: CREATE DATABASE Datmart Charset=utf8; b using the database datmart use Datmart; C Insert Chinese data, you need to set first: set names UTF8; D Import data into source D:\datmart