SQL SERVER 自動生成 MySQL 表結構及索引 的建表SQL

来源:http://www.cnblogs.com/xinysu/archive/2017/06/13/6992415.html
-Advertisement-
Play Games

SQL SERVER的表結構及索引轉換為MySQL的表結構及索引，其實在很多第三方工具中有提供，比如navicat、sqlyog等，但是，在處理某些數據類型、預設值及索引轉換的時候，總有些不盡人意並且需要安裝軟體，懶人開始想法子，所以基於SQL SERVER，寫了一個存儲過程，可以根據表名直接轉換為 ...


 

      SQL SERVER的表結構及索引轉換為MySQL的表結構及索引，其實在很多第三方工具中有提供，比如navicat、sqlyog等，但是，在處理某些數據類型、預設值及索引轉換的時候，總有些不盡人意並且需要安裝軟體，懶人開始想法子，所以基於SQL SERVER，寫了一個存儲過程，可以根據表名直接轉換為MySQL的建表建索引的SQL腳本（針對 MySQL Innodb引擎）。目前不支持分區表的分區配置及區域數據類型的轉換。  
      如果轉載，請註明博文來源： www.cnblogs.com/xinysu/   ，版權歸 博客園 蘇家小蘿蔔 所有。望各位支持！  

 

    建表的SQL中，主要在數據類型轉換、主鍵及索引的處理。

1 數據類型轉換

    數據類型轉換表詳見下表，這些數據類型的轉換目前已測試過，均可正常使用。     但是註意兩類資料庫存儲數據的一些差異，看下能否接受：
  • SQL SERVER中的datetime，保留到微秒(秒後小數點3位)，而mysql僅保留到秒，轉換後是否會影響業務，如果影響，需要新增一個欄位專門來存儲微秒或者毫秒，雖然mysql中沒有時間數據類型的精度到達微秒或者毫秒，但是mysql提供對微秒的相關處理函數microsecond、extract跟date_format。
  • MySQL使用tinyint代替SQL SERVER的bit
  • SQL SERVER的money類型使用decimal替代
  • timestamp的轉換，SQL SERVER中是一個16進位的數字，代表時間戳，每次修改都會數值都會變大。
    • 從功能考慮，轉換為mysql的時候，處理為timestamp的數據類型，預設值為CURRENT_TIMESTAMP，行發生修改則定時修改這一列數據，如果這樣轉換，那麼在SQL SERVER導入數據的時候，就要相應處理。（本文存儲過程預設這麼處理）
    • 從數據考慮，轉換為mysql的時候，處理為bigint的數據類型（修改存儲過程case when b.name = 'timestamp' then ' timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ' 為case when b.name = 'timestamp' then ' bigint ' ）
  • 自增處理，mysql的自增步長跟增量值是整個實例統一的，不能每個表格動態修改，所以這裡在轉化的過程中，為auto_increment，根據實例的設置來處理
 
ID SQL SERVER MySQL Description
1 bigint bigint  
2 binary binary  
3 bit tinyint SQL SERVER的bit類型，對於零，識別為False，非零值識別為True。
MySQL中沒有指定的bool類型，一般都使用tinyint來代替
4 char char  
5 date date  
6 datetime datetime 註意，mssql的保留到微秒(秒後小數點3位)，而mysql僅保留到秒
7 datetime2 datetime 註意，mssql的保留到微秒(秒後小數點7位)，而mysql僅保留到秒
8 datetimeoffset datetime 註意，mssql的保留時區，這個需要程式自己轉換
mssql的保留到微秒(秒後小數點7位)，而mysql僅保留到秒
9 decimal decimal  
10 float float  
11 int int  
12 money float 預設轉換為decimal(19,4)
13 nchar char SQL SERVER轉MySQL按正常位元組數轉就可以
14 ntext text  
15 numeric decimal  
16 nvarchar varchar  
17 real float  
18 smalldatetime datetime  
19 smallint smallint  
20 smallmoney float 預設轉換為decimal(10,4)
21 text text  
22 time time 註意，mssql的保留到秒後小數點8位，而mysql僅保留到秒
23 timestamp timestamp 註意，mssql的行時間戳，處理為mysql的 timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 。這會對後面導數據造成影響，從功能方面來看，可以按照上文轉換；如果從數據來看，若需要轉換16進位的文字存儲到mysql中，則這裡設置為bigint即可，在表格中的臨時表中設置。
24 tinyint tinyint  
25 uniqueidentifier varchar(40) 對應mysql的UUID(),設置為文本類型即可。
26 varbinary varbinary  
27 varchar varchar  
28 xml text mysql不支持xml，修改為text
 

2 主鍵處理

    MySQL不支持非主鍵的聚集索引，也就是聚集索引則是主鍵。故在轉換的過程中，主鍵是根據SQL SERVER表格中的聚集索引來轉換的。   
--SQL SERVER根據聚集索引的列情況來創建mysql的主鍵
SELECT
       col_name(i.object_id,ik.column_id) pk_col
FROM sys.indexes i
JOIN sys.index_columns ik ON i.index_id=ik.index_id and i.object_id=ik.object_id
WHERE i.type=1
      and i.object_id=object_id('tb')
ORDER BY key_ordinal

3 索引添加

    由於聚集索引已處理添加為主鍵，在建表的SQL中已判斷，這裡則只處理非聚集索引。     處理過程中註意：
  • MySQL不支持INCLUDE選項的包含索引，所以在處理的過程中，INCLUDE列添加到索引列中
  • MySQL 不支持WHERE選項的過濾索引，所以在處理的過程中，WHERE選項去除
  • 索引名字處理：包含列1-2個的，直接IX_表名，超過3個列的，取每列前3個字元，整個索引名長度不超過64個字元，超過截取前64個字元

4 測試

    存儲過程 [p_tb_mssqltomysql] 僅含一個參數 @tbsql，用於存儲表格名字，多個表格名中間有逗號隔開，不要有空格或者其他符號。    

    這裡，嘗試創建一個新表來測試。  

    創建表格及對應索引信息測試如下： 
 1 CREATE TABLE tbtest(
 2 id INT IDENTITY(1,1) NOT NULL ,
 3 name NVARCHAR(50) NOT NULL,
 4 phone VARCHAR(11) NOT NULL,
 5 age int default 99 ,
 6 birthday datetime default getdate(),
 7 addresss text,
 8 monyes money default 123456789012345.1234,
 9 smonyes smallmoney,
10 nums int default 2,
11 moneys money,
12 smo smallmoney,
13 curversion timestamp
14 )
15  
16 ALTER TABLE tbtest ADD CONSTRAINT PK_tbtest PRIMARY KEY (ID,phone);
17 CREATE INDEX IX_NAME ON tbtest(NAME);
18 CREATE INDEX IX_phone_age ON tbtest(phone,age);
19 CREATE INDEX IX_nums ON tbtest(nums) WHERE nums>2;
20 CREATE INDEX IX_birthday ON tbtest(birthday) include (name,phone);
      執行存儲過程轉化：   
 1 exec p_tb_mssqltomysql 'tbtest'
 2  
 3  
 4 CREATE TABLE tbtest(id int  not null  auto_increment 
 5 ,name varchar(50) not null 
 6 ,phone varchar(11) not null 
 7 ,age int  null 
 8 ,birthday datetime  null 
 9 ,addresss text  null 
10 ,monyes decimal(19,4)  null 
11 ,smonyes decimal(10,4)  null 
12 ,nums int  null 
13 ,moneys decimal(19,4)  null 
14 ,smo decimal(10,4)  null 
15 ,curversion timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  not null 
16 , primary key (id,phone) );   
17 CREATE   INDEX IX_name ON tbtest( name );
18 CREATE   INDEX IX_phone_age ON tbtest( phone,age );
19 CREATE   INDEX IX_nums ON tbtest( nums );
20 CREATE   INDEX IX_bir_nam_pho ON tbtest( birthday,name,phone );
      在mysql中創建正常，查看mysql的建表腳本如下：    

5 存儲過程腳本

    SQL SERVER轉換MySQL表結構及索引的腳本如下：   
-- =============================================
-- Author: suxinyu
-- Create date: 20170612
-- Description: 根據表名自動把表格的所有建表DDL SQL轉化為 MySQL的建表SQL，不包含分區表，不處理區域數據類型；執行過程中，需要把存儲過程建立在需要導出的資料庫中。
-- Example: exec p_tb_mssqltomysql 'orders,ordernums,channels'
-- =============================================
--存儲過程建立在需要導出表結構的DB上

USE db
GO


CREATE  PROC [dbo].[p_tb_mssqltomysql] 
@tbsql varchar(1000)
AS
SET NOCOUNT ON ;

--處理tablename的字元串，把tablename字元串分割成每一行存儲進入表變數中
DECLARE @tab_tablename table(tbname varchar(100))
DECLARE @tbname varchar(100)
INSERT INTO @tab_tablename(tbname)
SELECT

      SUBSTRING(@tbsql,NUMBER,CHARINDEX(',',@tbsql+',',NUMBER)-number)
FROM master.dbo.spt_values
WHERE TYPE='P'  AND number>0 AND SUBSTRING(','+@tbsql,number,1)=','

--把mysql跟mssql的數據類型對應起來存儲
--空間數據類型不處理
--money類型處理為float
--timestamp處理為 timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DECLARE @tbtype table(mssql varchar(20),mysql varchar(20))
INSERT INTO @tbtype(mssql,mysql) values( 'bigint','bigint'),('binary','binary'),('binary','binary'),('bit','tinyint'),('char','char'),('date','date'),('datetime','datetime'),('datetime2','datetime'),('datetimeoffset','datetime'),('decimal','decimal'),('float','float'),('int','int'),('money','decimal'),('nchar','char'),('ntext','text'),('numeric','decimal'),('nvarchar','varchar'),('real','float'),('smalldatetime','datetime'),('smallint','smallint'),('smallmoney','decimal'),('text','text'),('time','time'),('timestamp','timestamp'),('tinyint','tinyint'),('uniqueidentifier','varchar(40)'),('varbinary','varbinary'),('varchar','varchar'),('xml','text')

DECLARE @tb_exec_sql table(tbname varchar(100),sql nvarchar(max),indexs nvarchar(max))
DECLARE @indexs_sql nvarchar(max) 

--轉化表格SQL
DECLARE NAME CURSOR FOR

SELECT tbname FROM @tab_tablename
OPEN NAME
FETCH NEXT FROM name INTO @tbname
WHILE @@FETCH_STATUS =0
BEGIN
     ;WITH data AS (
     SELECT 
           case when b.is_unique=1 then ' UNIQUE ' else ' ' end is_unique,
           OBJECT_NAME(A.OBJECT_ID) obj_name,
           COL_NAME(A.object_id,A.column_id) colname,
           SUBSTRING(COL_NAME(A.object_id,A.column_id),1,3) col_short,
           is_included_column,
           index_column_id,
           a.index_id,
           A.OBJECT_ID
     FROM SYS.index_columns A INNER JOIN SYS.INDEXES B ON A.OBJECT_ID=B.OBJECT_ID AND A.index_id=B.index_id
     WHERE b.type!=1 and OBJECT_NAME(A.OBJECT_ID)=@tbname
     )
     SELECT 
           @indexs_sql=
           REPLACE(
                      (STUFF(
                              (
                               SELECT 
                                      ' CREATE '
                                         + a.is_unique
                                         +' INDEX '
                                         + CASE WHEN COUNT(*) >=3 THEN SUBSTRING(('IX_'+stuff((SELECT '_'+col_short FROM data b where a.object_id=b.object_id and a.index_id=b.index_id FOR XML PATH('')),1,1,'')),1,64)
                                              ELSE 'IX_'+stuff((SELECT '_'+colname FROM data b where a.object_id=b.object_id and a.index_id=b.index_id FOR XML PATH('')),1,1,'')
                                           END
                                         +' ON '
                                         + a.obj_name
                                         +'( '
                                         + stuff((SELECT ','+colname FROM data b where a.object_id=b.object_id and a.index_id=b.index_id FOR XML PATH('')),1,1,'')
                                         +' );
                                         '
                                FROM data a 
                                GROUP BY a.is_unique,a.obj_name,a.object_id,a.index_id
                                ORDER BY a.object_id,a.index_id 
                                FOR XML PATH('')
                               
                               ),1,1,'')
                      ),'
','')

    
      INSERT INTO @tb_exec_sql(tbname,indexs,sql)
      SELECT
            @tbname,@indexs_sql,
            'CREATE TABLE '+@tbname+'('+
             REPLACE(
                   STUFF( (
                               SELECT
                                     ','+a.name
                                    +
                                    case when b.name = 'timestamp' then ' timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP '
                                         when b.name = 'uniqueidentifier' then ' varchar(40) '
                                         when b.name in ('char','nchar','nvarchar','varbinary','varchar') then ( case when a.length<0 then ' text ' else ' '+c.mysql+'('+ (case when b.name like 'n%' then cast(a.length/2 as varchar(10)) else cast(a.length as varchar(10)) end )+')'  end )
                                         when b.name in ('decimal','float','money','numeric','smallmoney') then ' '+c.mysql+'('+ cast(a.prec as varchar(10)) +','+ cast(a.scale as varchar(10)) +') '
                                         else ' '+c.mysql+' ' end
                                    +
                                    case when a.isnullable=0 then ' not null ' else ' null ' end
                                    +
                                    case when COLUMNPROPERTY( A.ID,A.NAME,'ISIDENTITY')=1 then ' auto_increment ' else '' end
                                    +
                                    case when a.length<0 or b.name in ('text') then ' '
                                         when e.text like ' ((%' then ' default '+substring(e.text,3,len(e.text)-4)
                                         when e.text like ' (''%' then ' default '+substring(e.text,2,len(e.text)-2)
                                         else ' '
                                    end
                                    +
                                    ISNULL(' comment "'+cast(g.value as varchar(1000))+'"
                                                     ','
                                                     ')

                               FROM sys.syscolumns A
                               LEFT JOIN sys.systypes B ON A.XUSERTYPE=B.XUSERTYPE
                               LEFT JOIN @tbtype C ON b.name collate Chinese_PRC_CI_AS  = c.mssql
                               LEFT JOIN sys.sysobjects D ON A.ID=D.ID AND D.XTYPE='U' AND D.NAME<>'DTPROPERTIES'
                               LEFT JOIN sys.syscomments E ON A.CDEFAULT=E.ID
                               LEFT JOIN sys.extended_properties G ON A.ID=G.major_id AND A.COLID=G.minor_id
                               WHERE D.NAME =@tbname
                               order by a.colid
                               FOR XML PATH('')
                              ),1,1,'')
                        ,'&#x0D;','')
             +
             ISNULL(

                 ( SELECT

                                ', primary key ('+STUFF(
                                                              (
                                                                                     SELECT

                                                                ','+col_name(i.object_id,ik.column_id)
                                                         FROM sys.indexes i
                                                         JOIN sys.index_columns ik ON i.index_id=ik.index_id and i.object_id=ik.object_id
                                                         WHERE i.type=1 and i.object_id=object_id(@tbname)
                                                         ORDER BY key_ordinal
                                                         FOR XML PATH('')
                                                       ),1,1,'')

                                                                +') '
                     )
                       ,'')
             +
          ')'
          +
          ISNULL( (
                   SELECT
                                  ' COMMENT "'+CAST(value AS VARCHAR(1000))+'";
                             '

                         FROM sys.extended_properties

                           where major_id=object_id(@tbname) and minor_id=0
                         ),';')

          FETCH NEXT FROM NAME INTO @tbname
END
CLOSE NAME
DEALLOCATE NAME


SELECT * FROM @tb_exec_sql

 

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Android 概覽屏幕
    文章照搬過來的：原文地址https://developer.android.google.cn/guide/components/recents.html 概覽屏幕（也稱為最新動態屏幕、最近任務列表或最近使用的應用）是一個系統級別 UI，其中列出了最近訪問過的 Activity 和任務。 用戶可以瀏 ...
  • Android之Zxing二維碼掃描圖片拉伸
    還是這個接手項目，二維碼掃描集成的是zxing，掃描界面的圖像有明顯的拉伸變形。 這種問題，根據以往的經驗，一般是x，y軸錯位引起的，處理好x，y軸的問題，一般可以解決問題。 由於這個問題，之前有很多人遇到，並分享在網上了，所以，我這裡也就不需要重覆造輪子了。 這裡看了一篇博客：http://blo ...
  • Android 中 DrawerLayout + ViewPager 怎麼解決滑動衝突？
    DrawerLayout 是 Android 官方的側滑菜單控制項，而 ViewPager 相信大家都很熟悉了。今天這裡就講一下當在 DrawerLayout 中嵌套 ViewPager 時，要如何解決滑動衝突的問題，效果如下： 首先，讓我們先來解決 DrawerLayout 和 ViewPager ...
  • iOS----------developerDiskImage
    真機測試時提示Could not find Developer Disk Image.這該怎麼辦？？？？ 這是由於真機系統過高或者過低，Xcode中沒有匹配的配置包文件，我們可以通過這個路徑進入配置包的存放目錄： /Applications/Xcode.app/Contents/Developer/ ...
  • iOS 力學動畫生成器UIKit Dynamics 之碰撞效果講解
    UIKit Dynamic是iOS7 新增的一組類和方法，可賦予UIView逼真的行為和特征，不需要寫動畫效果那些繁瑣的代碼，讓開發人員能夠輕鬆地改善應用的用戶體驗。一共有6個可用於定製UIDynamicAnimator的類，這裡先只簡單介紹下碰撞的動畫效果，即UICollisionBehavior ...
  • 訂製EditText游標
    訂製EditText游標 設置背景android:background="@null" 設置游標樣式：android:textCursorDrawable="@drawable/edit_cursor_line" 去掉或設置游標下的圓點樣式：android:textSelectHandle="@dr ...
  • 【代碼筆記】iOS-removeFromSuper
    代碼： RootViewController.m ...
  • Android學習筆記- Fragment實例 底部導航欄的實現
    1.要實現的效果圖以及工程目錄結構： 先看看效果圖吧： 接著看看我們的工程的目錄結構： 2.實現流程： Step 1：寫下底部選項的一些資源文件 我們從圖上可以看到，我們底部的每一項點擊的時候都有不同的效果是吧！ 我們是通過是否selected來判定的！我們要寫的資源文件有：首先是圖片，然後是文字， ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類