惠普g14-a001tx这款笔记本怎么样？_百度知道
惠普g14-a001tx这款笔记本怎么样？
nvidia820显卡，貌似还可以？玩游戏怎么样看配置i5-3230m的处理器
您还可以继续追问您好感谢您选择HP产品
如果需要咨询HP产品及耗材的价格，在线带您操作，您直接发送您的姓名，或是通过访问惠普技术支持网页( /cn" target="_blank">、惠普技术支持论坛（ <a href="http、电话（如果有备用电话也烦请留一个）://www。如果以上信息没有解决您的问题。我们会有专业的工程师为您“推荐合适机型和查询详细价格”，“更多优惠惊喜”等待着您.cn/hpcommunity ）寻求帮助、所在省市和您的具体购买需求给我发邮件到.com/cn ).hp.com
已回答133290
响应时间&5小时
其他类似问题
为您推荐：
g14的相关知识
等待您来回答
下载知道APP
随时随地咨询
出门在外也不愁ustb80 的BLOG
用户名：ustb80
文章数：83
评论数：14
访问量：80061
注册日期：
阅读量：24883
阅读量：209316
阅读量：967384
阅读量：133261
51CTO推荐博文
前两天有个同事算数据，写出来的sql执行很慢。那个sql也很简单，就是一个左联带条件的查询。explain之后发现，其中有一张表没有用到索引。初始以为是没有建索引，于是建上索引再试，发现问题依旧。后来查看表结构才发现，原来用来做关联的字段是一个varchar类型的字段，而联接的另一张表中的字段类型却是bigint，结果造成了类型的不匹配，以致于索引失效。如果要想索引起效，最直接的办法就是将两张表的对应字段类型改成一样的。但如果表中数据量很大，或者改类型有风险时可以采用另一种办法，即通过类型转换函数来处理。下面详细说明一下：首先建立两张测试表，分别是table_a,table_b.CREATE TABLE `table_a` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`code` varchar(20) NOT NULL COMMENT '编码',
PRIMARY KEY (`id`),
KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8CREATE TABLE `table_b` (
`code` int(10) unsigned NOT NULL COMMENT '编码',
`name` varchar(20) NOT NULL COMMENT '名称',
KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8然后再往两张表中分别插入几条记录INSERT INTO `table_a` (`id`, `code`) VALUES
(1, '1001'),
(5, '1001'),
(3, '1002'),
(6, '1002'),
(2, 'A001'),
(4, 'B001');INSERT INTO `table_b` (`code`, `name`) VALUES
(1001, '测试1'),
(1002, '测试2');好了，数据准备完成，下面可以做测试了首先，我们做一个简单的左联接查询：mysql& SELECT count(1) FROM `table_a` a LEFT JOIN table_b b ON a.code = b.code WHERE b.code =1001;
+----------+
| count(1) |
+----------+
+----------+
1 row in set, 2 warnings (0.00 sec)因为数据量很少，所以查询几乎不耗时，我们来看一下explain的结果：mysql& explain SELECT count(1) FROM `table_a` a LEFT JOIN table_b b ON a.code = b.code
WHERE b.code =1001;
+----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref
| rows | Extra
+----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------------------+
1 | SIMPLE
| code | 4
1 | U Using index
1 | SIMPLE
| NULL | NULL
6 | Range checked for each record (index map: 0x2) |
+----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------------------+
2 rows in set (0.00 sec)注意表格的第二行，其中type是ALL，key是NULL，ref也是NULL，也就是说没有用到索引。当数据量达到百万级时，这样的查询是非常慢的。接下来，我们用convert函数来对字段进行处理，再看一下explain的结果：mysql& explain SELECT count(1) FROM `table_a` a LEFT JOIN table_b b ON a.code = convert(b.code, char)
WHERE b.code =1001;
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref
| rows | Extra
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 | SIMPLE
| code | 4
1 | U Using index |
1 | SIMPLE
| code | 62
2 | U Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
2 rows in set (0.00 sec)可以看到key，ref都不再是NULL了，rows的数量也比之前的要少得多，索引已经生效。当然，用cast也是一样的，如下：mysql& explain SELECT count(1) FROM `table_a` a LEFT JOIN table_b b ON a.code = cast(b.code as char)
WHERE b.code =1001;
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref
| rows | Extra
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 | SIMPLE
| code | 4
1 | U Using index |
1 | SIMPLE
| code | 62
2 | U Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
2 rows in set (0.00 sec)上面的转换是将b表中的字段类型转成跟a表的一样，如果反过来，将a表的转成跟b表的一样，会有效果么？于是，测试了如下的语句：mysql& explain SELECT count(1) FROM `table_a` a LEFT JOIN table_b b ON convert(a.code, signed) = b.code
WHERE b.code =1001;
+----+-------------+-------+-------+---------------+------+---------+-------+------+---------------------------------------------+
| id | select_type | table | type
| possible_keys | key
| key_len | ref
| rows | Extra
+----+-------------+-------+-------+---------------+------+---------+-------+------+---------------------------------------------+
1 | SIMPLE
| code | 4
1 | U Using index
1 | SIMPLE
| index | NULL
| code | 62
6 | U U Using join buffer |
+----+-------------+-------+-------+---------------+------+---------+-------+------+---------------------------------------------+
2 rows in set (0.00 sec)可以看到key这一列不再是NULL了，但ref这一列仍然是NULL，这说明用到了索引，但索引没有找到适合适的引用。从rows的数量上看也没有变化。这种转换是不成功的。思考一下原因，a表中的code包含多种类型的数据，而b表中的code只有一种类型，当由a转换为b类型时，a表中的数据有可能会丢失。实际上Mysql对于由字符串转整型还会给出警告。如下：mysql& select convert('A001', signed);
+-------------------------+
| convert('A001', signed) |
+-------------------------+
+-------------------------+
1 row in set, 1 warning (0.00 sec)结论：当表联接的字段类型不匹配时索引会失效，要想索引生效，可以用cast或convert函数将类型严格的一方转换为类型松散的一方的类型，这样也能避免精度丢失。比如，可以将数值型向字符串类型转，反过来则会有问题。吐槽一下，在firefox中为什么过长的行会被隐藏，敢不敢overflow不hidden，从mysql里拷个explain的表格现在都会被截断，太纠结了。To 51的程序员们本文出自 “” 博客，请务必保留此出处
了这篇文章
类别：┆阅读(0)┆评论(0)MySQL 編9 - 表結合(join)、単純結合、等価結合、外部結合、再帰結合
様々な連結テーブルが作成できるが、ビューの作成(create view) はサポート
されていないようである。
■単純結合
?現実的に単純結合を使う機会はないだろう。
次の例では、test2m の各レコードに testm の全レコードが結合される。
test2m のレコード数 x testm のレコード数の結果が問い合わされる。
create table testm (
) type=InnoDB;
insert into testm values ('a001', 1, 2, 3);
insert into testm values ('a011', 1, 2, 3);
insert into testm values ('b002', 10, 20, 30);
insert into testm values ('c003', 100, 200, 300);
create table test2m (
) type=InnoDB;
insert into test2m values ('abc01', 'a001');
insert into test2m values ('abc02', 'a011');
insert into test2m values ('abc03', 'z999');
select * from test2m,
select * from test2
+-------+-------+------+-------+-------+-------+
| code1 | key1 | data1 | data2 | data3 |
+-------+-------+------+-------+-------+-------+
| abc01 | a001
| abc02 | a011
| abc03 | z999
| abc01 | a001
| abc02 | a011
| abc03 | z999
| abc01 | a001
| abc02 | a011
| abc03 | z999
| abc01 | a001
| abc02 | a011
| abc03 | z999
+-------+-------+------+-------+-------+-------+
■等価結合
?等価結合は、特定のキーで表を結合します。
条件のどちらかのデータが存在しない場合、結果セットには含まれません。
現実的には外部結合が一番使いやすいと思います。
test2m.key1,
from test2m, testm
where test2m.code1 = testm.key1
+-------+-------+-------+-------+-------+
| code1 | data1 | data2 | data3 |
+-------+-------+-------+-------+-------+
| abc01 | a001
| abc02 | a011
+-------+-------+-------+-------+-------+
?inner join を使用して、等価結合することもできます。(インナージョイン)
test2m.key1,
from test2m inner join testm on test2m.code1 = testm.key1
?3 つ以上の結合を inner join で指定するには、次のようにする。
test2m.key1,
from (test2m inner join testm on test2m.code1 = testm.key1)
inner join test3m on test2m.code1 = test3m.key1
■非等価結合
?非常に時間のかかる結合ですが、必要な場面があるかもしれない。
なにがしかの範囲条件で結合する場合に使います。
test2m.key1,
from test2m, testm
where test2m.code1 like concat(substring(testm.key1, 1, 2), '%')
+-------+-------+-------+-------+-------+
| code1 | data1 | data2 | data3 |
+-------+-------+-------+-------+-------+
| abc01 | a001
| abc02 | a011
| abc01 | a001
| abc02 | a011
+-------+-------+-------+-------+-------+
■外部結合(外結合、行結合、行連結、テーブル結合、外部接合)
?外部結合は、一番よく使用します。
一方の条件に対するデータが存在しなかった場合でも片方のデータを結果セ
ットに含める指定ができます。
(左結合、右結合、複数テーブル問い合わせ、複数テーブル参照)
(複数問い合わせ、複合テーブル参照、レフトジョイン、ライトジョイン)
test2m.key1,
from test2m left join testm on test2m.code1 = testm.key1
+-------+-------+-------+-------+-------+
| code1 | data1 | data2 | data3 |
+-------+-------+-------+-------+-------+
| abc01 | a001
| abc02 | a011
| abc03 | z999
+-------+-------+-------+-------+-------+
?right join は、left join の逆で、次のようにする。
test2m.key1,
from test2m right join testm on test2m.code1 = testm.key1
+-------+-------+-------+-------+-------+
| code1 | data1 | data2 | data3 |
+-------+-------+-------+-------+-------+
| abc01 | a001
| abc02 | a011
+-------+-------+-------+-------+-------+
?三つ以上の表を結合することもできます。(3 テーブル結合)
test2m.key1,
from (test2m left join testm on test2m.code1 = testm.key1)
left join test3m on test2m.code1 = test3m.key1
?複数の表を結合すると修飾が難解になりがちなので、テーブル別名を使うと
良いです。
a.key1 as key1,
from test2m a left join testm b on a.code1 = b.key1
** 外部結合を明示するために left join を left outer join、right join
を right outer join と書くこともできる。
(アウタージョイン、レフトアウタージョイン、ライトアウタージョイン)
a.key1 as key1,
from test2m a left outer join testm b on a.code1 = b.key1
■再帰結合(内部結合)
?再帰結合は、同じテーブルをテーブル別名を使用して結合します。
次の例では、得意先m に親得意先CD があるとして、その得意先名を自分自
身と結合しています。(自己結合)
a.得意先CD
a.得意先名
a.親得意先CD 親得意先CD,
b.得意先名
from 得意先m a, 得意先m b
where a.得意先CD = b.親得意先CD
■MySQL 編資料
■PostgreSQL 編、JAVA Servlet、JSP 編資料
■SQL 基礎編資料
■SQL 基礎実地編資料
■SQL チューニング編資料
■Oracle PL/SQL 編資料
Copyright (C)MYSQL实时触发SQL SERVER实现同步实例-Mssql-数据库-壹聚教程网MYSQL实时触发SQL SERVER实现同步实例本文我们来讲讲在mysql数据库，用触发器实时触发SQL SERVER的实例教程，这样我们就可以保持mysql数据同步到sql server上，后面还补充了sqlserver数据实时同步到mysql。
我们对MYSQL数据表更新实时触发sql server里面对应表的增删改操作，首先，增加原来的mysql的表的，包括以下三个：&&& 插入数据： &&&&& &&&& delimiter || &&&& DROP TRIGGER IF EXISTS t_afterinsert_on_accounts || &&&& CREATE TRIGGER t_afterinsert_on_accounts &&&& AFTER INSERT ON sugarcrm642ce.accounts &&&& FOR EACH ROW &&&& BEGIN &&&& insert into sugarcrm642cebackup.accountsbackup &&&& (`id`, &&&& `name`, &&&& `date_entered`, &&&& `date_modified`, &&&& `modified_user_id`, &&&& `created_by`, &&&& `description`, &&&& `deleted`, &&&& `assigned_user_id`, &&&& `account_type`, &&&& `industry`, &&&& `annual_revenue`, &&&& `phone_fax`, &&&& `billing_address_street`, &&&& `billing_address_city`, &&&& `billing_address_state`, &&&& `billing_address_postalcode`, &&&& `billing_address_country`, &&&& `rating`, &&&& `phone_office`, &&&& `phone_alternate`, &&&& `website`, &&&& `ownership`, &&&& `employees`, &&&& `ticker_symbol`, &&&& `shipping_address_street`, &&&& `shipping_address_city`, &&&& `shipping_address_state`, &&&& `shipping_address_postalcode`, &&&& `shipping_address_country`, &&&& `parent_id`, &&&& `sic_code`, &&&& `campaign_id`, &&&& `BehaviorType`) values(new.id,new.name,new.date_entered,new.date_modified,new.modified_user_id,new.created_by,new.description,new.deleted, &&&& new.assigned_user_id,new.account_type,new.industry,new.annual_revenue,new.phone_fax,new.billing_address_street,new.billing_address_city,new.billing_address_state, &&&& new.billing_address_postalcode,new.billing_address_country,new.rating,new.phone_office,new.phone_alternate,new.website,new.ownership,new.employees,new.ticker_symbol, &&&& new.shipping_address_street,new.billing_address_city,new.shipping_address_state,new.shipping_address_postalcode,new.shipping_address_country,new.parent_id, &&&& new.sic_code,new.campaign_id,'I'); &&&& END|| &&&&& &&&& 更新触发器 &&&&& &&&& delimiter || &&&& DROP TRIGGER IF EXISTS t_afterupdate_on_accounts || &&&& CREATE TRIGGER t_afterupdate_on_accounts &&&& AFTER update ON sugarcrm642ce.accounts &&&& FOR EACH ROW &&&& BEGIN &&&& insert into sugarcrm642cebackup.accountsbackup &&&& (`id`, &&&& `name`, &&&& `date_entered`, &&&& `date_modified`, &&&& `modified_user_id`, &&&& `created_by`, &&&& `description`, &&&& `deleted`, &&&& `assigned_user_id`, &&&& `account_type`, &&&& `industry`, &&&& `annual_revenue`, &&&& `phone_fax`, &&&& `billing_address_street`, &&&& `billing_address_city`, &&&& `billing_address_state`, &&&& `billing_address_postalcode`, &&&& `billing_address_country`, &&&& `rating`, &&&& `phone_office`, &&&& `phone_alternate`, &&&& `website`, &&&& `ownership`, &&&& `employees`, &&&& `ticker_symbol`, &&&& `shipping_address_street`, &&&& `shipping_address_city`, &&&& `shipping_address_state`, &&&& `shipping_address_postalcode`, &&&& `shipping_address_country`, &&&& `parent_id`, &&&& `sic_code`, &&&& `campaign_id`, &&&& `BehaviorType`) values(new.id,new.name,new.date_entered,new.date_modified,new.modified_user_id,new.created_by,new.description,new.deleted, &&&& new.assigned_user_id,new.account_type,new.industry,new.annual_revenue,new.phone_fax,new.billing_address_street,new.billing_address_city,new.billing_address_state, &&&& new.billing_address_postalcode,new.billing_address_country,new.rating,new.phone_office,new.phone_alternate,new.website,new.ownership,new.employees,new.ticker_symbol, &&&& new.shipping_address_street,new.billing_address_city,new.shipping_address_state,new.shipping_address_postalcode,new.shipping_address_country,new.parent_id, &&&& new.sic_code,new.campaign_id,'U'); &&&& END|| &&&&& &&&& 删除触发器 &&&&& &&&& delimiter || &&&& DROP TRIGGER IF EXISTS t_afterdelete_on_accounts || &&&& CREATE TRIGGER t_afterdelete_on_accounts &&&& AFTER delete ON sugarcrm642ce.accounts &&&& FOR EACH ROW &&&& BEGIN &&&& insert into sugarcrm642cebackup.accountsbackup &&&& (`id`, &&&& `name`, &&&& `date_entered`, &&&& `date_modified`, &&&& `modified_user_id`, &&&& `created_by`, &&&& `description`, &&&& `deleted`, &&&& `assigned_user_id`, &&&& `account_type`, &&&& `industry`, &&&& `annual_revenue`, &&&& `phone_fax`, &&&& `billing_address_street`, &&&& `billing_address_city`, &&&& `billing_address_state`, &&&& `billing_address_postalcode`, &&&& `billing_address_country`, &&&& `rating`, &&&& `phone_office`, &&&& `phone_alternate`, &&&& `website`, &&&& `ownership`, &&&& `employees`, &&&& `ticker_symbol`, &&&& `shipping_address_street`, &&&& `shipping_address_city`, &&&& `shipping_address_state`, &&&& `shipping_address_postalcode`, &&&& `shipping_address_country`, &&&& `parent_id`, &&&& `sic_code`, &&&& `campaign_id`, &&&& `BehaviorType`) values(old.id,old.name,old.date_entered,old.date_modified,old.modified_user_id,old.created_by,old.description,old.deleted, &&&& old.assigned_user_id,old.account_type,old.industry,old.annual_revenue,old.phone_fax,old.billing_address_street,old.billing_address_city,old.billing_address_state, &&&& old.billing_address_postalcode,old.billing_address_country,old.rating,old.phone_office,old.phone_alternate,old.website,old.ownership,old.employees,old.ticker_symbol, &&&& old.shipping_address_street,old.billing_address_city,old.shipping_address_state,old.shipping_address_postalcode,old.shipping_address_country,old.parent_id, &&&& old.sic_code,old.campaign_id,'D'); &&&& END|| &上面必须增加behaviortype字段，因为如果是I代表插入，U代表更新，D代表删除，接着增加mysql的备份表，专门记录这三种操作。&&& CREATE TABLE `accountsbackup` ( &&&&&& `id` char(36) NOT NULL, &&&&&& `name` varchar(150) DEFAULT NULL, &&&&&& `date_entered` datetime DEFAULT NULL, &&&&&& `date_modified` datetime DEFAULT NULL, &&&&&& `modified_user_id` char(36) DEFAULT NULL, &&&&&& `created_by` char(36) DEFAULT NULL, &&&&&& `description` text, &&&&&& `deleted` tinyint(1) DEFAULT NULL, &&&&&& `assigned_user_id` char(36) DEFAULT NULL, &&&&&& `account_type` varchar(50) DEFAULT NULL, &&&&&& `industry` varchar(50) DEFAULT NULL, &&&&&& `annual_revenue` varchar(100) DEFAULT NULL, &&&&&& `phone_fax` varchar(100) DEFAULT NULL, &&&&&& `billing_address_street` varchar(150) DEFAULT NULL, &&&&&& `billing_address_city` varchar(100) DEFAULT NULL, &&&&&& `billing_address_state` varchar(100) DEFAULT NULL, &&&&&& `billing_address_postalcode` varchar(20) DEFAULT NULL, &&&&&& `billing_address_country` varchar(255) DEFAULT NULL, &&&&&& `rating` varchar(100) DEFAULT NULL, &&&&&& `phone_office` varchar(100) DEFAULT NULL, &&&&&& `phone_alternate` varchar(100) DEFAULT NULL, &&&&&& `website` varchar(255) DEFAULT NULL, &&&&&& `ownership` varchar(100) DEFAULT NULL, &&&&&& `employees` varchar(10) DEFAULT NULL, &&&&&& `ticker_symbol` varchar(10) DEFAULT NULL, &&&&&& `shipping_address_street` varchar(150) DEFAULT NULL, &&&&&& `shipping_address_city` varchar(100) DEFAULT NULL, &&&&&& `shipping_address_state` varchar(100) DEFAULT NULL, &&&&&& `shipping_address_postalcode` varchar(20) DEFAULT NULL, &&&&&& `shipping_address_country` varchar(255) DEFAULT NULL, &&&&&& `parent_id` char(36) DEFAULT NULL, &&&&&& `sic_code` varchar(10) DEFAULT NULL, &&&&&& `campaign_id` char(36) DEFAULT NULL, &&&&&& `BehaviorType` varchar(45) DEFAULT NULL, &&&&&& `ExecutingState` varchar(45) DEFAULT NULL, &&&&&& `ModificationTime` varchar(45) DEFAULT NULL, &&&&&& PRIMARY KEY (`id`) &&&& ) ENGINE=InnoDB DEFAULT CHARSET=utf8; &这个表如无意外，ID不要设置成为唯一索引，因为本身就存在多条同一个记录的更改。接下来在sql server建立对应的表。&&& USE [SugarCRMDB] &&&& GO &&&&& &&&& /****** Object:& Table [dbo].[account]&&& Script Date:
13:49:20 ******/ &&&& SET ANSI_NULLS ON &&&& GO &&&&& &&&& SET QUOTED_IDENTIFIER ON &&&& GO &&&&& &&&& SET ANSI_PADDING ON &&&& GO &&&&& &&&& CREATE TABLE [dbo]. [account]( &&&&&&&&&&&&&&&&&&&&& [id] [char] (36) NOT NULL, &&&&&&&&&&&&&&&&&&&&& [name] [varchar] (150) NULL, &&&&&&&&&&&&&&&&&&&&& [date_entered] [datetime] NULL, &&&&&&&&&&&&&&&&&&&&& [date_modified] [datetime] NULL, &&&&&&&&&&&&&&&&&&&&& [modified_user_id] [char] (36) NULL, &&&&&&&&&&&&&&&&&&&&& [created_by] [char] (36) NULL, &&&&&&&&&&&&&&&&&&&&& [description] [text] NULL, &&&&&&&&&&&&&&&&&&&&& [deleted] [smallint] NULL, &&&&&&&&&&&&&&&&&&&&& [assigned_user_id] [char] (36) NULL, &&&&&&&&&&&&&&&&&&&&& [account_type] [varchar] (50) NULL, &&&&&&&&&&&&&&&&&&&&& [industry] [varchar] (50) NULL, &&&&&&&&&&&&&&&&&&&&& [annual_revenue] [varchar] (100) NULL, &&&&&&&&&&&&&&&&&&&&& [phone_fax] [varchar] (100) NULL, &&&&&&&&&&&&&&&&&&&&& [billing_address_street] [varchar] (150) NULL, &&&&&&&&&&&&&&&&&&&&& [billing_address_city] [varchar] (100) NULL, &&&&&&&&&&&&&&&&&&&&& [billing_address_state] [varchar] (100) NULL, &&&&&&&&&&&&&&&&&&&&& [billing_address_postalcode] [varchar] (20) NULL, &&&&&&&&&&&&&&&&&&&&& [billing_address_country] [varchar] (255) NULL, &&&&&&&&&&&&&&&&&&&&& [rating] [varchar] (100) NULL, &&&&&&&&&&&&&&&&&&&&& [phone_office] [varchar] (100) NULL, &&&&&&&&&&&&&&&&&&&&& [phone_alternate] [varchar] (100) NULL, &&&&&&&&&&&&&&&&&&&&& [website] [varchar] (255) NULL, &&&&&&&&&&&&&&&&&&&&& [ownership] [varchar] (100) NULL, &&&&&&&&&&&&&&&&&&&&& [employees] [varchar] (10) NULL, &&&&&&&&&&&&&&&&&&&&& [ticker_symbol] [varchar] (10) NULL, &&&&&&&&&&&&&&&&&&&&& [shipping_address_street] [varchar] (150) NULL, &&&&&&&&&&&&&&&&&&&&& [shipping_address_city] [varchar] (100) NULL, &&&&&&&&&&&&&&&&&&&&& [shipping_address_state] [varchar] (100) NULL, &&&&&&&&&&&&&&&&&&&&& [shipping_address_postalcode] [varchar] (20) NULL, &&&&&&&&&&&&&&&&&&&&& [shipping_address_country] [varchar] (255) NULL, &&&&&&&&&&&&&&&&&&&&& [parent_id] [char] (36) NULL, &&&&&&&&&&&&&&&&&&&&& [sic_code] [varchar] (10) NULL, &&&&&&&&&&&&&&&&&&&&& [campaign_id] [char] (36) NULL &&&& ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] &&&&& &&&& GO &&&&& &&&& SET ANSI_PADDING OFF &&&& GO &&&&& &最后建立sql server存储过程，这边尤其要注意的是要rtrim mysql char类型，因为sql server对这个是完全填充的，如果有多余空格插入会报错，同时 要定义为 别名，因为在 下面的插入是根据别名字段来的。&&& USE [SugarCRMDB] &&&& GO &&&& /****** Object:& StoredProcedure [dbo].[trigger_account]&&& Script Date:
14:38:38 ******/ &&&& SET ANSI_NULLS ON &&&& GO &&&& SET QUOTED_IDENTIFIER ON &&&& GO &&&& ALTER procedure [dbo]. [trigger_account] &&&& as &&&&&&&& --insert into sugarcrmtablebackup(id,name)
* from openquery(MySql, 'select id,name from sugarcrmtablebackup where NOT ISNULL(ExecutingState) ') &&&&&&&&&&&&&&&&&&&&& &&&&&&&&&&&&&&&&&&&&& --插入BehaviorState为I的记录 &&&&&&&&&&&&&&&&&&&&& --insert into account(id,name,date_entered,date_modified,modified_user_id,created_by,[description],deleted,assigned_user_id,account_type,industry,annual_revenue,phone_fax,billing_address_street,billing_address_city,billing_address_state,billing_address_postalcode,billing_address_country,rating,phone_office,phone_alternate,website,[ownership],employees,ticker_symbol,shipping_address_street,shipping_address_city,shipping_address_state,shipping_address_postalcode,shipping_address_country,parent_id,sic_code,campaign_id) &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& --select * from openquery(MySql, 'select id,name,date_entered,date_modified,modified_user_id,created_by,description,deleted,assigned_user_id,account_type,industry,annual_revenue,phone_fax,billing_address_street,billing_address_city,billing_address_state,billing_address_postalcode,billing_address_country,rating,phone_office,phone_alternate,website,ownership,employees,ticker_symbol,shipping_address_street,shipping_address_city,shipping_address_state,shipping_address_postalcode,shipping_address_country,parent_id,sic_code,campaign_id from sugarcrm642cebackup.accountsbackup where& ISNULL(ExecutingState) and BehaviorType=''I''')& &&&&& &&&&&&&&&&&&&&&&&&&&& insert into account( id ,name, date_entered,date_modified ,modified_user_id, created_by,[description] ,deleted, assigned_user_id,account_type ,industry, annual_revenue,phone_fax ,billing_address_street, billing_address_city,billing_address_state ,billing_address_postalcode, billing_address_country,rating ,phone_office, phone_alternate,website ,[ownership], employees,ticker_symbol ,shipping_address_street, shipping_address_city,shipping_address_state ,shipping_address_postalcode, shipping_address_country,parent_id ,sic_code, campaign_id) &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& select * from openquery(MySql , 'select rtrim(id), name,date_entered,date_modified,rtrim(modified_user_id),rtrim(created_by),description,deleted,rtrim(assigned_user_id),account_type,industry,annual_revenue,phone_fax,billing_address_street,billing_address_city,billing_address_state,billing_address_postalcode,billing_address_country,rating,phone_office,phone_alternate,website,ownership,employees,ticker_symbol,shipping_address_street,shipping_address_city,shipping_address_state,shipping_address_postalcode,shipping_address_country,rtrim(parent_id),sic_code,rtrim(campaign_id) from sugarcrm642cebackup.accountsbackup where& ISNULL(ExecutingState) and BehaviorType=''I''')&& &&&&& &&&&& &&&&&&&&&&&&&&&&&&&&& update openquery (MySql, 'select * from sugarcrm642cebackup.accountsbackup where& ISNULL(ExecutingState) and BehaviorType=''i''') set ExecutingState='D' ,ModificationTime= getdate() &&&&& &&&&& &&&&&&&&&&&&&&&&&&&&& --删除BehaviorState为U的记录 &&&&&&&&&&&&&&&&&&&&& if object_id ('tempdb..#temp') is not null Begin &&&&&&&& drop table #temp &&&& End &&&&&&& &&&&&&&& select * into #temp from openquery( MySql, 'select rtrim(id),name from sugarcrm642cebackup.accountsbackup where& ISNULL(ExecutingState) and BehaviorType=''D''') &&&&& &&&&&&&&&&&&&&&&&&&&& declare @count int --变量定义 &&&&&&&&&&&&&&&&&&&&& set @count =(select count(*) from #temp ); &&&&& &&&&&&&&&&&&&&&&&&&&& if @count &0 begin &&&&&&&&&&&&&&&&&&&&& &&&&& &&&&&&&&&&&&&&&&&&&&& delete from account where id in (select rtrim(id ) from #temp) &&&&& &&&&&&&&&&&&&&&&&&&&& update openquery (MySql, 'select * from sugarcrm642cebackup.accountsbackup where& ISNULL(ExecutingState) and BehaviorType=''D''') set ExecutingState='D' ,ModificationTime= getdate() &&&&& &&&&&&&&&&&&&&&&&&&&& End &&&&&&&&&&&&&&&&&&&&& --更新BehaviorState为D的记录 &&&&&&&&&&&&&&&&&&&&& if object_id ('tempdb..#temp1') is not null Begin &&&&&&&& drop table #temp1 &&&& End &&&&&&& &&&&&&&&& select * into #temp1 from openquery( MySql, 'select rtrim(id) id, name,date_entered,date_modified,rtrim(modified_user_id) modified_user_id,rtrim(created_by) created_by,description,deleted,rtrim(assigned_user_id) assigned_user_id,account_type,industry,annual_revenue,phone_fax,billing_address_street,billing_address_city,billing_address_state,billing_address_postalcode,billing_address_country,rating,phone_office,phone_alternate,website,ownership,employees,ticker_symbol,shipping_address_street,shipping_address_city,shipping_address_state,shipping_address_postalcode,shipping_address_country,rtrim(parent_id) parent_id,sic_code,rtrim(campaign_id) campaign_id from sugarcrm642cebackup.accountsbackup where& ISNULL(ExecutingState) and BehaviorType=''U''') &&&&&&&&&&&&&&&&&&&&& declare @count2 int --变量定义 &&&&&&&&&&&&&&&&&&&&&& set @count2 =(select count(*) from #temp1 ) &&&&&&&&&&&&&&&&&&&&& &&&&&&&&&&&&&&&&&&&&& if @count2 &0 begin &&&&&&&&&&&&&&&&&&&&& &&&&&&&&&&&&&&&&&&&&& if& EXISTS (SELECT * FROM account&& a, #temp1 b WHERE a.ID = b.ID ) &&&&& &&&&&&&&&&&&&&&&&&&&& --declare @a int &&&&&&&&&&&&&&&&&&&&& update account&& set& id=t .id, name=t .name, date_entered=t .date_entered, date_modified=t .date_modified, modified_user_id=t .modified_user_id, created_by=t .created_by, &&&&&&&&&&&&&&&&&&&&& [description]=t .[description], deleted=t .deleted, assigned_user_id=t .assigned_user_id, account_type=t .account_type, industry=t .industry, annual_revenue=t .annual_revenue, &&&&&&&&&&&&&&&&&&&&& phone_fax=t .phone_fax, billing_address_street=t .billing_address_street, billing_address_city=t .billing_address_city, billing_address_state=t .billing_address_state, &&&&&&&&&&&&&&&&&&&&& billing_address_postalcode =t. billing_address_postalcode,billing_address_country =t. billing_address_country,rating =t. rating,phone_office =t. phone_office, &&&&&&&&&&&&&&&&&&&&& phone_alternate=t .phone_alternate, website=t .website, [ownership]=t .[ownership], employees=t .employees, ticker_symbol=t .ticker_symbol, shipping_address_street=t .shipping_address_street, &&&&&&&&&&&&&&&&&&&&& shipping_address_city =t. shipping_address_city,shipping_address_state =t. shipping_address_state,shipping_address_postalcode =t. shipping_address_postalcode, &&&&&&&&&&&&&&&&&&&&& shipping_address_country =t. shipping_address_country,parent_id =t. parent_id,sic_code =t. sic_code,campaign_id =t. campaign_id from&& account inner join(&& select id ,name, date_entered,date_modified ,modified_user_id, created_by,[description] ,deleted, assigned_user_id,account_type ,industry, annual_revenue,phone_fax ,billing_address_street, billing_address_city,billing_address_state ,billing_address_postalcode, billing_address_country,rating ,phone_office, phone_alternate,website ,[ownership], employees,ticker_symbol ,shipping_address_street, shipping_address_city,shipping_address_state ,shipping_address_postalcode, shipping_address_country,parent_id ,sic_code, campaign_id from #temp1) t on t. id=account .id &&&&&&&&&&&&&&&&&&&&& &&&&&&&&&&&&&&&&&&&&&& if EXISTS (SELECT * FROM account&& a, #temp1 b WHERE a.ID != b.ID ) &&&&& &&&&&&&&&&&&&&&&&&&&&&& if object_id ('tempdb..#temp2') is not null Begin &&&&&&&& drop table #temp2 &&&&&&&&&&&&&&&&&&&&& end &&&&&&&&&&&&&&&&&&&&&& select * into #temp2 from #temp1 a where a. id not in (select id from account ) &&&&& &&&&&&&&&&&&&&&&&&&&& insert into account ( id ,name, date_entered,date_modified ,modified_user_id, created_by,[description] ,deleted, assigned_user_id,account_type ,industry, annual_revenue,phone_fax ,billing_address_street, billing_address_city,billing_address_state ,billing_address_postalcode, billing_address_country,rating ,phone_office, phone_alternate,website ,[ownership], employees,ticker_symbol ,shipping_address_street, shipping_address_city,shipping_address_state ,shipping_address_postalcode, shipping_address_country,parent_id ,sic_code, campaign_id) &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& select&& t .id ,t. name,t .date_entered, t.date_modified ,t. modified_user_id,t .created_by, t.[description] ,t. deleted,t .assigned_user_id, t.account_type ,t. industry,t .annual_revenue, t.phone_fax ,t. billing_address_street,t .billing_address_city, t.billing_address_state ,t. billing_address_postalcode,t .billing_address_country, t.rating ,phone_office, t.phone_alternate ,t. website,t .[ownership], t.employees ,t. ticker_symbol,t .shipping_address_street, t.shipping_address_city ,t. shipping_address_state,t .shipping_address_postalcode, t.shipping_address_country ,t. parent_id,t .sic_code, t.campaign_id from #temp2 t &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& --inner join& ( select id from account) a on a.id !=t.id &&&&& &&&&&&&&&&&&&&&&&&&&& if not exists (SELECT * FROM account& a,#temp1 b WHERE a .ID = b .ID) and not exists (SELECT * FROM account&& a, #temp1 b WHERE a.ID != b.ID ) &&&&&&&&&&&&&&&&&&&&&& insert into account ( id ,name, date_entered,date_modified ,modified_user_id, created_by,[description] ,deleted, assigned_user_id,account_type ,industry, annual_revenue,phone_fax ,billing_address_street, billing_address_city,billing_address_state ,billing_address_postalcode, billing_address_country,rating ,phone_office, phone_alternate,website ,[ownership], employees,ticker_symbol ,shipping_address_street, shipping_address_city,shipping_address_state ,shipping_address_postalcode, shipping_address_country,parent_id ,sic_code, campaign_id) &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& select&& t .id ,t. name,t .date_entered, t.date_modified ,t. modified_user_id,t .created_by, t.[description] ,t. deleted,t .assigned_user_id, t.account_type ,t. industry,t .annual_revenue, t.phone_fax ,t. billing_address_street,t .billing_address_city, t.billing_address_state ,t. billing_address_postalcode,t .billing_address_country, t.rating ,phone_office, t.phone_alternate ,t. website,t .[ownership], t.employees ,t. ticker_symbol,t .shipping_address_street, t.shipping_address_city ,t. shipping_address_state,t .shipping_address_postalcode, t.shipping_address_country ,t. parent_id,t .sic_code, t.campaign_id from #temp1 t &&&&&&&&&&&&&&&&&&&&&& &&&&& &&&&& &&&&&&&&&&&&&&&&&&&&& update openquery (MySql, 'select * from sugarcrm642cebackup.accountsbackup where& ISNULL(ExecutingState) and BehaviorType=''U''') set ExecutingState='D' ,ModificationTime= getdate() &&&&&&&&&&&&&&&&&&&&&& End &&&&&&&& --delete from sql_tem &&&&&&&& --delete openquery(MySql, 'SELECT * FROM sugarcrmtablebackup')& &最后再开启sql server代理，因为本身这个需要计划任务每秒去monitor这个mysql变化，因为sql server这边只能设置10秒更新一次，那这边就只能如此，如果要看是否成功，可以右击该计划任务查看历史记录。sqlserver数据实时同步到mysql1.安装安装mysqlconnector2.配置mysqlconnectorODBC数据管理器-&系统DSN-&添加-&mysql ODBC 5.3 ANSI driver-&填入data source name如jt,mysql的ip、用户名、密码即可3.新建链接服务器exec sp_addlinkedserver@server='jt', --ODBC里面data source name@srvproduct='mysql', --自己随便@provider='MSDASQL', --固定这个@datasrc=NULL,@location=NULL,@provstr='DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=192.168.5.188;DATABASE=UID=PORT=3306;',@catalog = NULLexec sp_addlinkedsrvlogin@rmtsrvname='jt',@useself='false',@rmtuser='root',@rmtpassword='password';select * from openquery(jt,'SELECT * FROM ')GOUSE [master]GOEXEC master.dbo.sp_serveroption @server=N'jt', @optname=N'rpc out', @optvalue=N'TRUE'GOEXEC master.dbo.sp_serveroption @server=N'jt', @optname=N'remote proc transaction promotion', @optvalue=N'false'GO---4.sqlserver和mysql新建库和表crcreate table sz(id int not null identity(1,1) primary key,orderno char(20) not null,ordertime datetime not null default getdate(),remark varchar(200))gocreate table sz(id int(11) not null ,orderno char(20) not null,ordertime datetime(6) not null ,remark varchar(200),primary key (id)) engine=innodb default charset=utf8;---5.建立回环--建立LOOPBACK 服务器链接EXEC sp_addlinkedserver @server = N'loopback' , @srvproduct = N' ' , @provider = N'SQLNCLI',@datasrc = @@SERVERNAMEgo--设置服务器链接选项，阻止SQL Server 由于远过程调用而将本地事务提升为分布事务（重点）USE [master]GOEXEC master.dbo.sp_serveroption @server=N'loopback', @optname=N'rpc out', @optvalue=N'TRUE'GOEXEC master.dbo.sp_serveroption @server=N'loopback', @optname=N'remote proc transaction promotion', @optvalue=N'false'GO----6.编写触发器和存储过程----6.1 insert--重写触发器use suzhougoalter trigger tr_insert_sz on suzhou.dbo.szfor insertasdeclare @id int, @orderno char(20),@ordertime datetime,@remark varchar(200)select @id=id,@orderno=orderno,@ordertime=ordertime,@remark =beginprint @idprint @ordernoprint @ordertimeprint @remarkexec loopback.suzhou.dbo.sp_insert @id,@orderno,@ordertime,@remarkendgo--存储过程use suzhougocreate PROCEDURE sp_insert(@id int,@orderno char(20),@ordertime datetime,@remark varchar(200))ASBEGINSET NOCOUNT ON;Insert openquery(jt, 'select * from sz')(id,orderno,ordertime,remark)values(@id,@orderno,@ordertime,@remark)ENDgo----6.2 update--重写触发器use suzhougocreate trigger tr_update_sz on suzhou.dbo.szfor updateasdeclare @orderno char(20),@remark varchar(200)select @orderno=orderno,@remark =beginexec loopback.suzhou.dbo.sp_update @orderno,@remarkendgo--存储过程use suzhougocreate PROCEDURE sp_update(@orderno char(20),@remark varchar(200))ASBEGINSET NOCOUNT ON;update openquery(jt, 'select * from sz') set remark=@remark where orderno=@ordernoENDgo---update数据测试use suzhougoupdate sz set remark='ocpyang' where orderno='a001'go----6.3 delete--重写触发器use suzhougocreate trigger tr_delete_sz on suzhou.dbo.szfor deleteasdeclare @orderno char(20)select @orderno=beginexec loopback.suzhou.dbo.sp_delete @ordernoendgo--存储过程use suzhougocreate PROCEDURE sp_delete(@orderno char(20))ASBEGINSET NOCOUNT ON;delete openquery(jt, 'select * from sz') where orderno=@ordernoENDgo---delete数据测试use suzhougodelete from sz where orderno='a001'go 上一页：没有了 &&&&&下一页：相关内容
手机迅雷爱大厨编辑推荐
电脑壁纸相关专题
