脚本如下,就是从系统表里获取某张表的字段的一些信息,然后写到对应的自定义的元数据表Sync_MetaData,Sync_MetaItem.
create proc up_InitMeta
@tableName nvarchar(50),
@dataImporter nvarchar(1000),
@changedDataConverter nvarchar(1000)
as
begin
declare @metaDataID int,@xOrder int
select @xOrder = max(XOrder) + 1 from Sync_MetaData
if(@xOrder is null)
set @xOrder= 1
INSERT INTO [Sync_MetaData]
([TableName]
,[IgoreCase]
,[xOrder]
,[DataImporter]
,[ChangedDataConverter])
VALUES
(@tableName
,1
,@xOrder
,@dataImporter
,@changedDataConverter)
set @metaDataID = @@IDENTITY
set @xOrder = 1
INSERT INTO [Sync_MetaItem]
select @metaDataID,c.name,c.name,dbo.fc_GetTypeCode(t.name),0,c.is_nullable,null,null,null,ROW_NUMBER() OVER (ORDER BY column_id) from sys.columns c
inner join sys.systypes t on t.xusertype=c.system_type_id
where c.is_identity = 0 and c.object_id in (select object_id from sys.objects where name = @tableName)Update k Set k.IsPrimaryKey=1 From Sync_MetaItem k Where k.MetaDataID in (select MetaDataID from Sync_MetaData where TableName=@tableName)
And Exists (Select 1 from sysindexkeys ik,syscolumns c
where ik.id=c.Id and ik.colid=c.colid and ik.id=Object_id(@tableName) and c.Name=k.ColumnName
And Exists(select 1 from sysindexes i where ik.id=i.id and ik.indid=i.indid
and i.status & 2948=2048))
end
gocreate function fc_GetTypeCode
(
@typename nvarchar(50)
)
returns int
as
begin
if CHARINDEX('int',@typename) > 0
return 9
if CHARINDEX('char',@typename) > 0
return 18
if CHARINDEX('bit',@typename) > 0
return 3
if CHARINDEX('date',@typename) > 0
return 16
if CHARINDEX('decimal',@typename) > 0
return 15
if CHARINDEX('float',@typename) > 0
return 13
if CHARINDEX('text',@typename) > 0
return 18
if CHARINDEX('money',@typename) > 0
return 18
if CHARINDEX('numeric',@typename) > 0
return 14
if CHARINDEX('time',@typename) > 0
return 20
if CHARINDEX('xml',@typename) > 0
return 18return -1
end
create proc up_InitMeta
@tableName nvarchar(50),
@dataImporter nvarchar(1000),
@changedDataConverter nvarchar(1000)
as
begin
declare @metaDataID int,@xOrder int
select @xOrder = max(XOrder) + 1 from Sync_MetaData
if(@xOrder is null)
set @xOrder= 1
INSERT INTO [Sync_MetaData]
([TableName]
,[IgoreCase]
,[xOrder]
,[DataImporter]
,[ChangedDataConverter])
VALUES
(@tableName
,1
,@xOrder
,@dataImporter
,@changedDataConverter)
set @metaDataID = @@IDENTITY
set @xOrder = 1
INSERT INTO [Sync_MetaItem]
select @metaDataID,c.name,c.name,dbo.fc_GetTypeCode(t.name),0,c.is_nullable,null,null,null,ROW_NUMBER() OVER (ORDER BY column_id) from sys.columns c
inner join sys.systypes t on t.xusertype=c.system_type_id
where c.is_identity = 0 and c.object_id in (select object_id from sys.objects where name = @tableName)Update k Set k.IsPrimaryKey=1 From Sync_MetaItem k Where k.MetaDataID in (select MetaDataID from Sync_MetaData where TableName=@tableName)
And Exists (Select 1 from sysindexkeys ik,syscolumns c
where ik.id=c.Id and ik.colid=c.colid and ik.id=Object_id(@tableName) and c.Name=k.ColumnName
And Exists(select 1 from sysindexes i where ik.id=i.id and ik.indid=i.indid
and i.status & 2948=2048))
end
gocreate function fc_GetTypeCode
(
@typename nvarchar(50)
)
returns int
as
begin
if CHARINDEX('int',@typename) > 0
return 9
if CHARINDEX('char',@typename) > 0
return 18
if CHARINDEX('bit',@typename) > 0
return 3
if CHARINDEX('date',@typename) > 0
return 16
if CHARINDEX('decimal',@typename) > 0
return 15
if CHARINDEX('float',@typename) > 0
return 13
if CHARINDEX('text',@typename) > 0
return 18
if CHARINDEX('money',@typename) > 0
return 18
if CHARINDEX('numeric',@typename) > 0
return 14
if CHARINDEX('time',@typename) > 0
return 20
if CHARINDEX('xml',@typename) > 0
return 18return -1
end
看看MYSQL HELP,自己动手做一下吧,有问题再问
select @xOrder = max(XOrder) + 1 from Sync_MetaData ->
set xOrder = (select max(XOrder) + 1 from Sync_MetaData );
set @metaDataID = @@IDENTITY->set @metaDataID = lcast_insert_id()如果是5以上,看看的系统表 information_schma
(
typename nvarchar(50)
)
returns int
begin
if instr('int',typename) > 0
select 9;
if instr('char',typename) > 0
select 18;
if instr('bit',typename) > 0
select 3;
if instr('date',typename) > 0
select 16;
if instr('decimal',typename) > 0
select 15;
if instr('float',typename) > 0
select 13;
if instr('text',typename) > 0
select 18;
if instr('money',typename) > 0
select 18;
if instr('numeric',typename) > 0
select 14;
if instr('time',typename) > 0
select 20;
if instr('xml',typename) > 0
select 18;select -1;
end
我把这个函数改成这样了,但报第五行有语法错误
select 9;
END IF;其它的自行修改
create function fc_GetTypeCode
(
typename nvarchar(50)
)
returns int
begin
declare retV int;if instr('int',typename) > 0 then
set retV = 9;
elseif instr('char',typename) > 0 then
set retV = 18;
elseif instr('bit',typename) > 0 then
set retV = 3;
elseif instr('date',typename) > 0 then
set retV = 16;
elseif instr('decimal',typename) > 0 then
set retV = 15;
elseif instr('float',typename) > 0 then
set retV = 13;
elseif instr('text',typename) > 0 then
set retV = 18;
elseif instr('money',typename) > 0 then
set retV = 18;
elseif instr('numeric',typename) > 0 then
set retV = 14;
elseif instr('time',typename) > 0 then
set retV = 20;
elseif instr('xml',typename) > 0 then
set retV = 18;
else
set retV = -1;
end ifreturn retV;
end
我已经改成这样还是报第7行有错误
DROP FUNCTION IF EXISTS fc_GetTypeCode$$
CREATE FUNCTION fc_GetTypeCode
(
typename NVARCHAR(50)
)
RETURNS INT
BEGIN
DECLARE retV INT;IF INSTR('int',typename) > 0 THEN
SET retV = 9;
ELSEIF INSTR('char',typename) > 0 THEN
SET retV = 18;
ELSEIF INSTR('bit',typename) > 0 THEN
SET retV = 3;
ELSEIF INSTR('date',typename) > 0 THEN
SET retV = 16;
ELSEIF INSTR('decimal',typename) > 0 THEN
SET retV = 15;
ELSEIF INSTR('float',typename) > 0 THEN
SET retV = 13;
ELSEIF INSTR('text',typename) > 0 THEN
SET retV = 18;
ELSEIF INSTR('money',typename) > 0 THEN
SET retV = 18;
ELSEIF INSTR('numeric',typename) > 0 THEN
SET retV = 14;
ELSEIF INSTR('time',typename) > 0 THEN
SET retV = 20;
ELSEIF INSTR('xml',typename) > 0 THEN
SET retV = 18;
ELSE
SET retV = -1;
END IF;RETURN retV;
END$$
DELIMITER ;
SET retV = -1;
END IF; -- 少了分号
还问个问题,在sqlserver2008里有个ROW_NUMBER() OVER (ORDER BY column_id)的函数,不知道在mysql是否有对应的函数啊。
还问个问题,在sqlserver2008里有个ROW_NUMBER() OVER (ORDER BY column_id)的函数,不知道在mysql是否有对应的函数啊。
MYSQL中没有此函数,插入有自增字段的临时表中 OR 如表中有唯一标识的字段,用查询解决
select f1,f2,@a:=@a+1 from tt;
select f1,f2,@a:=@a+1 from (select * from tt order by column_id) a ;
DROP PROCEDURE IF EXISTS up_InitMeta$$
CREATE PROCEDURE up_InitMeta
(
databaseName NVARCHAR(50),
tableName NVARCHAR(50),
dataImporter NVARCHAR(1000),
changedDataConverter NVARCHAR(1000)
)
BEGIN
DECLARE metaDataID INT;
DECLARE xOrder INT;
DECLARE a INT;SET xOrder = (select MAX(XOrder) + 1 from Sync_MetaData );
IF xOrder is null THEN
SET xOrder= 1;
END IF;
INSERT INTO Sync_MetaData
(TableName
,IgoreCase
,xOrder
,DataImporter
,ChangedDataConverter)
VALUES
(tableName
,1
,xOrder
,dataImporter
,changedDataConverter);SET metaDataID = lcast_insert_id();
SET xOrder = 1;
SET a = 1;
INSERT INTO Sync_MetaItem
SELECT metaDataID,COLUMN_NAME ,COLUMN_NAME ,fc_GetTypeCode(DATA_TYPE), CASE WHEN COLUMN_KEY = 'PRI' THEN 1 ELSE 0 END,
CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,null,null,null,a:=a +1
FROM information_schema.COLUMNS
WHERE EXTRA <> 'auto_increment' AND TABLE_SCHEMA =databaseName AND TABLE_NAME = tableName;
END$$
DELIMITER ;
DROP PROCEDURE IF EXISTS up_InitMeta$$
CREATE PROCEDURE up_InitMeta(databaseName VARCHAR(50),tableName VARCHAR(50),dataImporter VARCHAR(1000),changedDataConverter VARCHAR(1000))
BEGIN
DECLARE metaDataID INT;
DECLARE xOrder INT;
DECLARE a INT;
SET xOrder = (SELECT MAX(XOrder) + 1 FROM Sync_MetaData );
IF xOrder IS NULL THEN
SET xOrder= 1;
END IF;
INSERT INTO Sync_MetaData
(TableName
,IgoreCase
,xOrder
,DataImporter
,ChangedDataConverter)
VALUES
(tableName
,1
,xOrder
,dataImporter
,changedDataConverter);SET metaDataID = lcast_insert_id();
SET xOrder = 1;
SET @a = 1;
INSERT INTO Sync_MetaItem
SELECT metaDataID,COLUMN_NAME ,COLUMN_NAME ,fc_GetTypeCode(DATA_TYPE), CASE WHEN COLUMN_KEY = 'PRI' THEN 1 ELSE 0 END,
CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,NULL,NULL,NULL,@a:=@a +1
FROM information_schema.COLUMNS
WHERE EXTRA <> 'auto_increment' AND TABLE_SCHEMA =databaseName AND TABLE_NAME = tableName;
END$$DELIMITER ;
SHOW WARNINGS;