Help us understand the problem. What is going on with this article?

Microsoft SQL Server から MySQL(MariaDB) へテーブル移行

いろいろ方法があるらしいが、今回は練習がてら自分で作ったスクリプトで変換。

ここではテーブルを作る。

データの移行はこちら
MS SQL Server からMySQL(MariaDB) へテーブルデータ移行 - Qiita

元データはこの方法で取得したもの
SQL Server 2008 R2 でテーブル構造を取得 - Qiita

環境

移行元

  • Microsoft SQL Server 2008 R2
  • Windows 2003 Foundation

移行先

  • MariaDB (mysql Ver 15.1 Distrib 10.1.44-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2)
  • Ubuntu 18.04

元データ

CREATE TABLE [dbo].[会員](
        [stid] [int] IDENTITY(1,1) NOT NULL,
        [会員番号] [char](10) NOT NULL,
        [漢字氏名] [nvarchar](255) NULL,
        [フリガナ] [nvarchar](255) NULL,
        [性別] [tinyint] NULL,
        [生年月日] [datetime] NULL,
        [入会年度] [smallint] NULL,
        [所属コード] [nvarchar](8) NULL,
        [年次] [tinyint] NULL,
        [種別コード] [nvarchar](5) NULL,
        [郵便番号] [int] NULL,
        [現住所] [nvarchar](255) NULL,
        [携帯番号] [nvarchar](50) NULL,
 CONSTRAINT [PK_会員1] PRIMARY KEY CLUSTERED
(
        [stid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'会員', @level2type=N'COLUMN',@level2name=N'stid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'会員', @level2type=N'COLUMN',@level2name=N'stid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'会員', @level2type=N'COLUMN',@level2name=N'stid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'会員', @level2type=N'COLUMN',@level2name=N'stid'
GO



変換スクリプト

まず、nkfで文字コードをutfにしておく。

nkf -Lu 会員.sql > 会員.sql.utf8

あとは sed で

cat  会員.sql.utf8 | sed 's/[][]/`/g' | sed 's/`hogehoge`/`test`/' |  sed 's/`dbo`/`test`/' | sed 's/^GO//' | sed 's/^SET ANSI_NULLS ON//'  | sed 's/^SET QUOTED_IDENTIFIER ON//'  | sed 's/^SET ANSI_PADDING ON//' | sed 's/^SET ANSI_PADDING OFF//'|  sed 's/"dbo".//' | sed 's/IDENTITY(1,1) NOT NULL/AUTO_INCREMENT NOT NULL PRIMARY KEY/' | sed 's/^EXEC.*//' | sed '/^$/d' | sed 's/`char`/char/g'| sed 's/`nvarchar`/nvarchar/' |sed 's/`tinyint`/tinyint/'| sed 's/`datetime`/datetime/' |sed 's/`smallint`/smallint/'| sed 's/`int`/int/' |sed 's/`float`/float/'| sed 's/`bit`/bit/' | sed 's/`date`/date/' |  sed 's/`numeric`/numeric/' |  sed 's/`ntext`/longtext/' | sed 's/`decimal`/decimal/'  | sed 's/"/`/g' | sed '/^ CONSTRAINT/,/^) ON /d' | sed '$s/.$/)/'  > 会員.mysql

↑はワンライナーで行ったもの。改行を入れたもの↓。

cat  会員.sql.utf8 | \
sed 's/[][]/`/g' | \
sed 's/`hogehoge`/`test`/' | \
sed 's/`dbo`/`test`/' \
sed 's/^GO//' | \
sed 's/^SET ANSI_NULLS ON//'  | \
sed 's/^SET QUOTED_IDENTIFIER ON//'  | \
sed 's/^SET ANSI_PADDING ON//' | \
sed 's/^SET ANSI_PADDING OFF//'|  \
sed 's/"dbo".//' | \
sed 's/IDENTITY(1,1) NOT NULL/AUTO_INCREMENT NOT NULL PRIMARY KEY/' | \
sed 's/^EXEC.*//' | \
sed '/^$/d' | \
sed 's/`char`/char/g'| \
sed 's/`nvarchar`/nvarchar/' | \
sed 's/`tinyint`/tinyint/'| \
sed 's/`datetime`/datetime/' | \
sed 's/`smallint`/smallint/'| \
sed 's/`int`/int/' |\
sed 's/`float`/float/'|\
sed 's/`bit`/bit/' |\
sed 's/`date`/date/' |  \
sed 's/`numeric`/numeric/' |  \
sed 's/`ntext`/longtext/' | \
sed 's/`decimal`/decimal/'  |\
sed 's/"/`/g' | \
sed '/^ CONSTRAINT/,/^) ON /d' | \
sed '$s/.$/)/' |  \

> 会員.mysql

会員.mysqlができる

CREATE TABLE `test`.`会員`(
    `stid` int AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `会員番号` char(10) NOT NULL,
    `漢字氏名` nvarchar(255) NULL,
    `フリガナ` nvarchar(255) NULL,
    `性別` tinyint NULL,
    `生年月日` datetime NULL,
    `入会年度` smallint NULL,
    `所属コード` nvarchar(8) NULL,
    `年次` tinyint NULL,
    `種別コード` nvarchar(5) NULL,
    `郵便番号` int NULL,
    `現住所` nvarchar(255) NULL,
    `携帯番号` nvarchar(50) NULL)


これをmysql(MariaDB)から

MariaDB [test]> source 会員.mysql

でOK.

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした