Replication的犄角旮旯（三）--聊聊@bitmap

《Replication的犄角旮旯》系列导读

---------------------------------------华丽丽的分割线--------------------------------------------

关于replication中的bitmap，貌似介绍的文档不多；本文将从对此参数做一初步的简析，并介绍如何利用这个参数处理一些特定环境下的问题；

再次强调，本方法虽多次经受验证无误，但多次被MS supporter们建议不要尝试使用此方法，还望各位DBA三思！

先来看看@bitmap在哪里出现

　　我们先创建一个表的复制订阅，表结构如下

 USE [test_aaa]

 GO

 /****** Object:  Table [dbo].[test_b]    Script Date: 2014/1/23 16:12:28 ******/

 SET ANSI_NULLS ON

 GO

 SET QUOTED_IDENTIFIER ON

 GO

 SET ANSI_PADDING ON

 GO

 CREATE TABLE [dbo].[test_b](

     [id1] [int] NOT NULL,

     [id2] [int] NOT NULL,

     [id3] [int] NOT NULL,

     [id4] [int] NOT NULL,

     [name] [varchar](10) NULL,

     [remark1] [varchar](100) NULL,

     [remark2] [varchar](100) NULL,

     [remark3] [varchar](100) NULL,

     [remark4] [varchar](100) NULL,

  CONSTRAINT [pk_id1_id2_id3_id4] PRIMARY KEY CLUSTERED

 (

     [id1] ASC,

     [id2] ASC,

     [id3] ASC,

     [id4] 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

　　到订阅库的存储过程中，找到sp_MSupd_dbotest_b，生成脚本

 USE [test_byxl1]

 GO

 /****** Object:  StoredProcedure [dbo].[sp_MSupd_dbotest_b]    Script Date: 2014/1/23 14:28:46 ******/

 SET ANSI_NULLS ON

 GO

 SET QUOTED_IDENTIFIER ON

 GO

 ALTER procedure [dbo].[sp_MSupd_dbotest_b]

     @c1 int = NULL,

     @c2 int = NULL,

     @c3 int = NULL,

     @c4 int = NULL,

     @c5 varchar(10) = NULL,

     @c6 varchar(100) = NULL,

     @c7 varchar(100) = NULL,

     @c8 varchar(100) = NULL,

     @c9 varchar(100) = NULL,

     @pkc1 int = NULL,

     @pkc2 int = NULL,

     @pkc3 int = NULL,

     @pkc4 int = NULL,

     @bitmap binary(2)

 as

 begin

     if (substring(@bitmap,1,1) & 1 = 1) or

        (substring(@bitmap,1,1) & 2 = 2) or

        (substring(@bitmap,1,1) & 4 = 4) or

        (substring(@bitmap,1,1) & 8 = 8)

     begin

         update [dbo].[test_b]

         set  [id1] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [id1] end,

              [id2] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [id2] end,

              [id3] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [id3] end,

              [id4] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [id4] end,

              [name] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [name] end,

              [remark1] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [remark1] end,

              [remark2] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [remark2] end,

              [remark3] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [remark3] end,

              [remark4] = case substring(@bitmap,2,1) & 1 when 1 then @c9 else [remark4] end

         where [id1] = @pkc1   and [id2] = @pkc2   and [id3] = @pkc3   and [id4] = @pkc4

         if @@rowcount = 0

             if @@microsoftversion>0x07320000

                 exec sp_MSreplraiserror 20598

     end

     else

     begin

         update [dbo].[test_b]

         set [name] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [name] end,

             [remark1] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [remark1] end,

             [remark2] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [remark2] end,

             [remark3] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [remark3] end,

             [remark4] = case substring(@bitmap,2,1) & 1 when 1 then @c9 else [remark4] end

         where [id1] = @pkc1   and [id2] = @pkc2   and [id3] = @pkc3   and [id4] = @pkc4

         if @@rowcount = 0

             if @@microsoftversion>0x07320000

                 exec sp_MSreplraiserror 20598

     end

 end

　　看到这么多@bitmap，是不是有种升仙的感觉？

　　@bitmap 是binary类型，即二进制串；简单来说，它是用来表示所操作的字段位置的参数，通过@bitmap，分发代理从distribution.dbo.msrepl_commands中读取命令时（update操作），才会知道哪些列进行了更新；

　　我们先来解析一下这个存储过程；

1、根据表结构的code，我们知道这个表共有9个字段，其中id1~id4被定义为联合主键；

由于binary(1)表示1个字节（8位的2进制），因此我们表示9个字段的@bitmap就只能用binary(2)来容纳了；

　　其次，有的童鞋说，他们看到的update存储过程只有一个程序段，而我的例子中有两部分（29行~44行、46行~57行）。这个是由于存在联合主键造成的；即当被订阅的表中含有联合主键（2个或以上的字段一同作为主键）的时候才会出现两段代码，前者是更新主键列，后者则是更新非主键列；

2、根据更新列的位置不同，@bitmap中的对应的值也不同；

　 substring(@bitmap,1,1) & 1 = 1 表示第一列有更新；

　substring(@bitmap,1,1) & 2 = 2 表示第二列有更新；

　 substring(@bitmap,1,1) & 4 = 4 表示第三列有更新；

以此类推

　 substring(@bitmap,1,1) & 128 = 128 表示第八列有更新；

那第九位呢？ =256么？由于1个字节只有8位，而128=2^7，当第九位出现时就要进位了

　 substring(@bitmap,2,1) & 1 = 1

怎么样，不难理解吧？

定义4个字段的联合主键只是为了举例说明的时候方便一些，实际的生产环境中可能不太经常能遇到；

再来看一下@bitmap在哪里可以获取到呢？我先更新一条记录，更新之前先关闭相应的分发代理（此处不需要分发命令应用到订阅端）

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAigAAAARCAIAAAB4hcCeAAADw0lEQVR4nO1ZvW4bMQzWayZAHyDIVHQsuhieiqCP1w4F0qGDhw4eM3RghzOuivgjUv8H84PhyDyKIj/pREoJ4HA4HA7HQITZDjgcDofjvuCJx+FwOBxD4YnHcccIAcKQV2DYQMNwlIiO4qceR4lI9LMugOCZK49wOofTOW4sjs3P/TPbnc4Y9g6vtFkIM2uY91kRCTva9ih56sy3QjvmqwPIGqhMbXUb3yL75u5GvT9jCInVGnK40g4AAHeadTaQ02qY97kRkaPHwr3tzLdFI+Y98YyAJ56bqeU2gftF7fY3F/rtbz1kiT0e8yaFTev/3wBpO7z/JP2wPFB25O6CZ/yFjyyM77X0p9eCgfQ248RTbHMkITbqAnHOTiSxjrwyhZtJgUxZ83w6J59MMHGjICRNnNz4TPjFsXM2k1VBegIm6oBnr4Y60mYsySYhHVZgvnDRwhrMW9Z8IJvvkkeNMGlrnia6iG6yKCOFZPfsKLJNvfEeNkmFYYTQmrlDdkEZit9SzqX9Hc5qGoBfMxDjjL85TdP41H6E26bYBZtC1imBfNFfRp1gs1HWufVbgHlBnvN+AeYFeaJFNgtzzPYTH4PwmNxJCOtS+yxZcZCFm2kKlVUMJxSG7p14mhNic4kvnnAJVTk6Lj+Tb+AJ0ULwGNePyTfwwevH100xGXuiL9sk5ZxQ7T0KmSSEpC7Rl22Sck6ow3TmuS4672czz3Uhtchm7eFGf+LJ+mfcOoWCRTlQWcKQPemdePT6PRLPTU2RYCoTD2YsW3tuMN9ayG198VgE5RRz259yXXGJOZHYqAN+qyKFeBdTHhy5vF5H/lzmky4HY14eFGuRTb9qMwnJZdHDJkwlhNa0LN2axCMIhcTT7MRTf2uB0GSKlbELNmvpSu2K21/xhQ9ns2B5Lc98yxMPbvdm3px4tl/xB9BPQZMTgthd46L6n3hcBaeZyMCcqTVCYR0nmpyfWCjUOL0JIaNmlY3/X8/eP5Fsk47FrHKasvNSPNjd5OJif7R/Y00RXL2iWSGa2IVVh59yfW1QrgYNdbEO2V3Tl8GCzOOBbJjLPB4o62/lc4fDUYz8/YmjD5z5uZCLT8O5pAu+fIaHR/j6cvt5uVRKyHq5ieWDSmhCcK+/uZlqi7aR/r6M9V4F9kS7zNoAGD7vQ+DMr4CFTzTXK3z8BD++w9MzfHuBP1c4nV0yRzJy724+7z9fxzlfidXW/JI5uwuc+bFYO/G8vQEA/HqFD4/w8AhPzy6ZIxm5dzef9+37EFhtzR8oZ1fCmR+LfyU0i2G1WUQhAAAAAElFTkSuQmCC" alt="" />

我们去distribution里看看具体的分发命令（具体做法请见《Replication的犄角旮旯（二）--寻找订阅端丢失的记录》）

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAA6sAAAB7CAIAAAAdVoFgAAAUBklEQVR4nO2dPY/bxhaG+TPuT7i3s4AUcen9CSkD3ErY6v6EdAshlZFqkUJQZUDpnCKA0tlNgE2xALcwoBQBlCLFFi5UuFCRgregxB1xPniGXzNDPi8eHHCpIedwSI3ePZ6Vs/98/Q3AHCi+5ABTJfj7CwAgLbLgGQCMQ3CPAjAcwd9fAABpgQOGuRDcowAMR/D3FwBAWpwd8Df//d/Dx/fBJ3EAAAAAgKE5O2DsLwAAAADMhLMDDp4HAAAAAMA44IABAAAAYF6cHfD+U+5LUeq0K47LF067AiEUn7IsC50CQgghNKA2P+3k4IARmoVwwAghhKatzU+7o1g44Fkqywr80LSl3WKrA+ZhmLy4xQihecjmgLMsm44DXmXZKuU5vcy/+yW0P4nxqPKTUnJCY8uwOwvTRY3Te7XHq/HQvQsdsNbS1bvtcB4GfU/EDwNCCE1SRgecXTQRB1wURdIOuFQvl9DmJDbH427gbhl2Z2EqdI2fki2B8XvX7qCrBuzeycPQbmfEDwNCCE1SugPOroUDjkXBHLBRqZse948j9N746pi9G3r2eU54GDr23vjqmL0jhNA8lNIqiGoxQGnj1B8bjZ2xZe1UemO9Wa2x47StO7JdkXGPcEDceXYtDJfbWXa1EZW/Me6M0PRklmKk7TzROmAeBt+UIn4YEEJokkrGAdesm2On/PDCtDTW0ZEafVPy6sh4RePkKZX7U7bHD+nhPuPDmh79R9tRmX2NZl+9a+qhBszDIE8p7ocBIYQmqcoBZ02KpQZc22PcNh4rPEruLG0p2QquxsvRy8C2Q9w/OvKUm3IPOT6GQ5kJ38/42ExPwN419bMKgoehXUoxDAhCCE1d6X0bmtzLtjvKywHL05CsanCfqncH7E64QQmZHtsFRmU7IjM9k3XAPAyNvSOE0DyUTA14uFUQXi0bz9OL1TYewiqIrjsdlzBmShH2rinLstXdanW3cjcT9c7D4NgZYe8IITQPpbQO2Li0QN/Z/XDbzkIzjsLDO7Z0rKyoraCoOeDaS2p7+dBZlWl/5JRd//2TreWYO6s98aRU7df3BOy9/rqPA9Y74mGY0MOAEEKTVDIO2Kj27i1WTe+KUIoqV0F4OGCEEEIoKSX8fcDuqmrXAmcIpZgzmqTKGnDoLBBCCKGhNJf/Ew4hJJffX8IhhBBCqSntVRAIIYQQQgj5atxvQ0MIIYQQQii0Nj/t5OCAEUIIIYRQ8irdqWpW88eH/PcP+e8f8scHdf/qboUDRgghhBBCyUt1wKX3ffj44cPH3YePu4ePVz64kwMGAAAAAIgEL7v84oCHseNDyXdQbt7cHD8/AwAAAEAS+Do9LyeZtgMWNl7drXDAAAAAAAnh6/S8nGTyDljyawEOGAAAACAtfJ2el5PEAQMAAABAdIRywLvlV28P1fb5v5Rb7qx7lANrL/35dmFo1kluB/z+5/c4YAAAAIB00Z1ezeD174APPyyyLMsuDnh3my1/vey/3Rn3vOjXZdVm8cNBbdyjHA74/c/vV3crHDAAAABAuuhOb3W3MprgXmvAf75dnB3wbqlWc796ezDsUVRzwHqDPmRzwKX9xQEDAAAAJI3RARtN8PAO+Lyt71F1ePvVyyqIjgXg776//9e/X1d89/19ud/ogCv7iwMGAAAASBqbA9ZNcCQOWD+JY9FwsyoTXNnfwuKAt++2OGAAAACACeBwwNt32/hWQbzo8Parxds/r9ZFtKsHf/f9vWp/C/sqiMoE44ABAAAA0sXmgGv2dzgH7PmXcJUuxre7A9bl+Eu40gTjgAEAAADSxeiAdfvbpwM+fxdEuXDh18Lz29DO5zgXgNXG/f1JnMMBlyYYBwwAAACQLsb1rkbjx/+IYQYHDAAAAJAWvk7Py0nigAEAAAAgOnDABqkLoiXggAEAAAASwtfpeTnJFwecHDdvbrwIfiMBAAAAQIiv05uLAwYAAAAAaAEOGAAAAADmBQ4YAAAAAOYFDhgAAAAA5sWVA/ZaQRxcwccOAAAAAFLk6tvQgmfjxc2bm+A5AAAAAEBynB1wcvZ3/ylf3a1Cl6FFCj5QAAAAAKCStgNOogycRJIAAAAA86HZAS/XZha3dUZO3eiAH357ePjtYffLTmX7bhtwiMtadcAEAAAAAEBF5IA3T8WZdbG04HbAWZY5fmyH0QFv1vW/5zt+Pi7Xm4BDnEqtGgAAAGAmtKkB69XfxW2+eOXspskBZ3bZzul2wKcvp/xY5Mdi+3ha3uKAAQAAAOBMswNWq6r548v2w28P561/is06X7xaWvu4uFiHwVWdrm27hsMB7//Y5095/phvH0+bdd7RATtykBSzccAAAAAAUeHngF9cb1HsftmV9vf05bRZ56+/fm3uoMnjVnv6qgFvnvLdL7vNOt++25Z4OWA9YeOGPFscMAAAAEBUNDvg7bvt/o99/nj+I7OK0mWWf2fmdsC6NTQ64MbtGsJ1wNvH07kY/JRvnvLDX4fmQbm2vGp0Z4UDBgAAAIgfkQM21oA36/z05VQut92+29oc8LkbS/VUbdBXDfj57+fj5+Px8zF/KraPp+3jSc158WopMcHG5NtliwMGAAAAiIq2qyD+KXa/7F7+2szpgB0utkt7mwM+/HW4KlQ/5UVRFOtNsV5u1nn+6DLBjabcnSEOGAAAACB+mh3w/Y/3+dN5CUS1xLaM1VrbzXrTuAqiWktgNJRuGc8sXAVxdsBFURTF4a/D4na5eLUU/t2e4yVqwAAAAACJInLA6iqC8k/fTl9O23fb/FiUKw3uf7x3eMqr/pr+Hm5vWndrxOGAy+L08fMxP14c8HpZ3C6LoliuF5t1vrhtzpYaMAAAAMAk8XPA5cqHau1vtdC20QGrNWB1Z62NbcOI2wGX9jc/FmpV+PTldPjrsHlyfXebLb09NWAAAACASSBywNU3P6grH8qvgLj/8b7E5ilrvtBWUrU543arICr7mz9dHPDtsrhdPP/9vFwvGh1wZlqwYcukXa0aAAAAAEIhcsCvv369eLWsRXWjRNRf2+8U02l2wE/F8mlRfZfF8fNx/8f+4bcH+f/f4X5JWLTGAQMAAABERbMDjhabA84fz/8vRsnZAd8uittF/pgvbhcOByxZprzXHLBjpy1PAAAAAAjFBB3w4nb57X+/XdwuS0oHfP4fMS6rOFqsA7YtZa6tkcABAwAAAETO1BywTul3dWLLEwAAAADGYfoOODip5AkAAAAwE5J3wAVCCCGEEEI+StgBAwAAAAC0wOCAV3crAIDhKLIMAAAiJPgHxOAfQBfHa3bAN29uAAAGIvgUDwAARoJ/QAzK/lPe7IDHXIoxcncSRZgSsomblZ5CT/EAAGBmuvJwwPtP+eFTPnSsuhuhL2FUfweIIR+i5PkZ7Ykldo/qVBtDPkQikTjnqC5OiyGfga7RwwGPk9bIhls+TJiqJCK/rqQYVQccQz5EIpE456g64BjyGegaqQFLhwlTlUTk15UUIzVgIpFIjCdSA85LqAFjqhKK/LqSYqQGTCQSifFEasD5XmlEDRhTlUTk15UUIzVgIpFIjCdSA85LAtaAs56G3nieLMuE5+9oqiS99HWlrWM1GuXGmL33e+0df12RXPvI41O7R6F6HzTGUwOe6ggTiUSiPMZTA26ck42vSmbyudeAhR910daA+/2ors4W3I738vzEVk0c9Ne51GNUNeBJjjCRSCTKY1Q14HZzcuNRsdeAIxnWyZuq2tmSroFN/teVpO+OLcZTA57qCBOJRKI8xlMDPrSdk9OuARuL2OrShUyR45yO83SvAev51Hp0925ceGC7rtqexja28xsfFD1nx6iq7Y09ukdDOD5ed1l/fvQn1tivLX99nPX2xisyXldjG9v5jc9z47NkHNWYo6MGrN99yRMlGW1bG/f4E4lE4uSjowYc1ZxsfFU4k8deA860D/vq8mx7jLHxPJJh0k2VMR/jzWjsvd2VSo5qvMZ2oyo5Su89M9myvu5y7fmpPbHGs+n56G8/fX9jm9ZHGcdHmKE758ijrQbcOLaSKLxHKY4bkUgkDhFtNeAI52TJp7btGuOtAUsubOgBOlyPgPzj2dH7QWANbRaqZpLk4+OI7UY1u1Qc1T21DG2j4XvtXvfL69cVx2jUrsvRxni91VDIjxI+jY0Zet39SKKtBmx87+hPoDsa74hk2iUSicR5RlsNOMI5WfKJbLtGasCiR6FHU+XVxn2lvZiqLqNqNF7uK+1yFZL71e+vK15t3OMvP0p4v+T3Mf7YWAO2jZskCu9RiuNGJBKJQ8TGGrC+J9Sc3PjZ6rjGeGvAtd8PqktSL68yYXpLdThs53EcpQ+TcB2wfjNqvahdG9vYrsuYc63HxnxsD5B6ZuHI6GfWM7eNhu3aW99l/fmRrAM29quPp2NU9XGzXamef2M+tifZmE+m3bta7zHHLuuAG6Nx9OR3jUgkEucWu6wDboy183SZk42vCmfy2GvAwR8CdZj6+i6I7NoSBb+6icV+vwui9nYNfnVTjVF9FwSRSCTOPEb1XRDDXWO8NeAWxxp/n+hrmHr8gq1QdaZ24zPQqA79/PT1xIa6an3Yg4/tcDGq7wMmEonEmceovg+4jL1/JlID9himCL8PmOh4fqgmJhSpAROJRGI8kRpwvlcaJVEDHnSYMFVJRH5dSTFSAyYSicR4YoQ14CGu0cMBr+5W4zByd/KUYssKjHCzkkN1wMGTAQCAaloOnslwSB3wyBRFMX6nyaUENrhZaVFNtcEzAQCAm9lMyyIHHEPJmkgkTjJO/p/biEQiMa04+XXAZdxLHPDhU16MpYsrPxbF84Xw24dPefAc2BZu70d8XFEPUlZBIIQQCq8ZTMsHoQOuGg3txxXD/RwV+/hSAhtj/sKGetAMplqEEEpJM5iW/WrAIxSlFcNduplYKosXUx5LPmw7tqkBJ6YZTLUIIZSSZjAtUwOWQg04IagBJ6YZTLUIIZSSZjAtx1wDjqKaeG2qIsqHbcc2NeDEJJhqy/8EaJRcstH6mol8h7TfwR/uVrY7c2yPliSfFHNGqvQRa35LzsABUwOWYqwBX56h59p/1qe+Wtup7g9+UVPFVgOWfBLrbdgzxJ4ryRywfrbazvYJWDpqp6ojtUdjzr4n9O10fDl6F2bVe/LDjUa7M492d4z3wv3GcZyq5+TsHfWVs+388bTxmpT0mcRxHuOZjQPr7tUxLQecZHpUpxpwNsDXZCS3Djg7G9njZeP5euOqTXVs1Sb4dU1y21gDlkygehv2DLGnLh8HbDtb1+nes5n7DNWFS0egj6yMQ9TxnC2Okry5+k1pzBN2PPPIvsHxptCfTOFJhlYvOdvOLHSlI7TxnRN6mW8bJ8lal7ZpWTICSah9DbgcgpnXgIvrMnC1x7ahHhX8iiaMsQaMA45nT11tHXCtgbujSBxw63PK2+CAUalGNxmhHImN/D4atE3SDliYc/yKuQYcRTXx2lQZ9l874IaqcHVs1Sb4dU1yW1gDdjsV9sTjgBvPIOmonS/MFOk/2s5Qe65q28Y2tjM3dqe3cZzHfVpbG/eZ3cmo+x1Hydt0lHuc5W36zUc/uW8+xvNk14+WemBaOfc18n09hN3b6LfDParVS/pRjXscKbmSnIED7rQOOKMGfPGylwf0ZY9xo3YUDIS8BlybMtrNL+zx3VNXkwNWDzSeQdKRcMqWf2bYTqgm4zibfh7JhiNh3/PYcpMMtUO2w/syFt3VOD7GOyi5F63zcd8LYT6287S4EbHl3FfmfT2E3dtIPo8kL3nNt35PgnNaHuetOrRirgGXbiaWyqJ7HfDlbfxcsA44gm2vdcDubfYMsacu51RbO9D20VjJ1kw4ZRsPdHwm2c5g+yB3u4HGDUfCvufRPw5rY1ho1+7OxJ1zX8aiu2yjUV2s8Q4ax6fffPRkHPnUcnafx9FX/Dn3mHxfD2H3NrabYnvXGEdSuEf4o57i5B0wNWAp7hqwcKNQHLN+BugLasCR76nLOdXWDnTcR3czoQ+wveTuQm/pcABdNrwSkwyL7UfbS42ffLac+zIW3dU4PsZ7N1xuRheo/2jLx5Ghwz+lmHP3/Pt6CLu3kXweSV7ymuj83pLOaXmct+rQirkGHEU18dpU1fdnFxVKxbfQSsKNiuQaJ7NtrAEXRaEM+MuP6obehj0D7bmSjwPWz6ZvSyTpqHZC0bVYXJTxqNp+SRtbj8LzGA/Uz+PuWpiMcVgapfbeeIYWvegDVduvvlo4HzxH8u3yceTpzsd4p4yPhO2eRpuzozt3zu70ImyjX3itmW00JHtsCRQO2adlyR1JQl2/CyLr2wQnVwOGCDHWgFG8kjngcSbcxsl9uDQm8IlilO/nZfVJb9zvu2dMxZZPO00jZ8lVxNZmONn8t/uYxmk5dXWqAQ8Rk1sHzHaE27YaMIpU6Uy1jnJLx7qI5PC++upLw+WD/Q2laeQcm7VNcVQTmpZbq9M64CEiNWDoDjXgxDSDqRYhhFLSDKblmGvAUVQTr01VRPmw7dimBpyYZjDVIoRQSprBtEwNWAo14ISgBpyYZjDVIoRQSprBtOxXA17drUaAdcBssw54XprBVIsQQilpBtOyRw345s3NaFADhi5QA05MM5hqEUIoJc1gWvaoAY+wBIJIJM4wVlNt8EyIRCKRuFem5eCZDHiNRbGXOOCgNh0hNGnNoNiAEEIpaR7TMg4YIRRU85hqEUIoGc1jWsYBI4SCah5TLUIIJaN5TMs4YIRQUM1jqkUIoWQ0j2kZB4wQCqpqqgUAgKiYtHDACKGgCj7FAwCAkUkLB4wQCqrgUzwAABiZtHDACCGEEEJoXsIBI4QQQgiheUl1wP8H4xaRb1VEvPcAAAAASUVORK5CYII=" alt="" />

从命令中我们可以看出，更新的列位置为第5(name)、7(remark2)、8(remark3)列，按照二进制的表示方法为(注意反取，即←表示第1至第9位)

0 1101 0000

由于第九位没有更新，因此为0，所以bitmap就是 1101 0000，换成十六进制就是0xD0，由于bitmap超出1个字节，因此后面再补0，就是我们看到的0xd000了

可能看到这里，有些童鞋会说，这太麻烦了，遇到一个很宽的表，光数逗号就数死了……尽管我们明白逗号是为了分割字段的，但系统为什么会这样生成呢，这个问题可以参考：http://msdn.microsoft.com/zh-cn/library/ms152489%28v=sql.120%29.aspx

这个和article的属性，调用订阅端存储过程方式有关（SCALL），这个不是本文的重点，在此不做赘述；

根据上面的算法，我们就可以知道，当我们要更新一个表时，可以根据更新列的位置，推算出实际的bitmap值，但这又有什么用呢？

-------------------------------------我是华丽丽的分割线-------------------------------------

应用场景：一个表（还是说商品表吧，比较典型），保存商品信息、简介等内容，都是varchar(max)或text类型；商家在促销活动前通常会大批量的更新这些内容（比如加个促销活动介绍等等），更新大字段是复制环境中最头疼的问题；

由于一条复制命令有长度限制（1K左右），如果一条更新记录中的更新内容过大，就会被拆分成多条命令写到msrepl_commands中（我曾经碰到过1条记录的更新操作被拆成100个复制命令），如果高峰时期有用户大量的进行这种操作，那作为DBA就可以升级为“张三疯”了；

这时候我们就可以根据具体的update命令（一般除了更新必要的字段外，还会捎带更新updatetime这样的时间戳，具体就去找研发兄弟们要吧），计算出相应的bitmap，然后在相应的存储过程中加个判断，屏蔽掉这样的操作；

具体操作就是，在sp_MSupd_dbotest_b这里的第24、25行之间加上

if @bitmap=0xd000

return;

再打开分发代理，这样就屏蔽了@bitmap=0xd000的全部操作；

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAdQAAABtCAIAAACX9wcyAAAOrUlEQVR4nO1dvW7byhLmY9xHuLcLgRRJGT/CLQ9wK0JPkS4wUhmnMlIIqgyozCkCKC8QwI0BujDAFAGU4hQsXKhIoeIUewtK9Ir7w+X+zm6+wYfFhqFnRsPdTx/HtFQxxv7z5r8TdE8ty8q6p3bIeZjocf3h+urd1eG5BwAASIWKgXwBAACiw5x8d83rm/04rwZrdsoj3A9O/uvHTS05LSD5fv7rM8gXAABSMCLf/Z91VVXVmXx3q6r5ej6+2kmPvNjXZjyn/nPPnxyHfD//9fn6wzXIFwAAUjBWvj9u6hP57hpew76+2UuOqMlXPCEk+Q7MC/IFAIAa3Mj3NBeP8La/ef3SdnCUve8/3v7r329HvP94qyHfkXlBvgAAUEME8hWdaBrEC/h3ZF4V+W7vtiBfAABoInzb4cX2N6/rmx8XjQg7Ffz+4y3PvCry5fkX5AsAAClYkO/CX7iNduZcd/IVTfMLt4F/Qb4AAJDCkqcdhk7BV7bwUbOTj5Ps5U/295s3/aNm27styBcAAFLAH1kAAAAkAMgXAAAgAYoi3/HZhlmAfAEASAsl+WaHq3dXi5C89AAA/M4oh3wBAAAyAsgXAAAgAUC+AAAACQDyBQAASAAd+aZ8fGG5JS8lAACAOXSPmiVPbhGu3l0lzwEAAMAQcvLNjnm7p/b6w3Va6W1oyQsFAAAFFEW+WYjfLJIEACA0FpNvs5ajXk0R+ZVIyff+2/39t/vdlx2PyefsxM8zx/c2AAD8woZ8N4/shDVrFNCTb1VVmn/aQUq+m/X014aH50Oz3iSseC4KHQCAoPCgfEXNW6/a+pU26hz5VmpT+dST7/HXsT2w9sC2D8dmBfIFACAxFpMvryXbh5f5/bf70+wftlm39atGGfJMoBpu5UlWNZ9AQ77d9659bNuHdvtw3KxbR/LV5GAi4UG+AAB0juT7QriM7b7sBuY9/jpu1u3bN2/l8ebodTziS/luHtvdl91m3W7vtgMWka+YsHRini3IFwCAzoJ8t3fb7nvXPpx+lzViILjh11l68hVZSUq+s/MJDHu+24fjSQI/tpvHdv9zP1+jS7blR31WIF8AAFSwIV+p8t2s2+Ov49Ba3d5tVeR7iqrQjPwJvpRv/3d/eD4cng/tI9s+HLcPRz7n+lVjwr/S5O2yBfkCANB5azv8w3Zfdi+/1NKSr4ZAXc5Xke/+5/5Cnj+2jDG23rB1s1m37YOOf2ffD/QZgnwBAFBhMfnefrptH089h7GdOoxjX3Wz3sy2HcabdymX6U3q2bDtcCJfxhhj+5/7etXUrxrDXw9q/gvKFwCARbAhX/62ffgN2/HXcXu3bQ9suLW//XSrobOL8HO/dutkPVYpNOQ7SPLD86E9nMl33bBVwxhr1vVm3dar+WyhfAEA8Agn8h1aDWOfd2yqzpIvr3z5g5NzVBMp9OQ7MG97YLwWPv467n/uN4+6B+NU6XVQvgAAOMCGfMdnG/hWw/CQw+2n2wEqOptQkkpIqkjZru0wMm/7eCbfVcNWdf9336zrWfKtZB0SVSZ2Ch0AgN8NNuT79s3b+lUzGfnJAKPwtg9siZgn30fWPNbj0xqH50P3vbv/dm/+JyH6/zKU6iBfAAC64j/VbHieYfhTiwEn8l3VbFW3D229qjXka9KS7gTy1RxU5QkAwO+G8sm3XjV//O+PetUMGMj39EcW57aJRc9X1baeNCVAvgAASFE4+YoYqFYEtTwBACgbvx35JkcueQIAEBSlkS+DwWCwHKwc8gUAAMgI8+R7/eEaAAAA8Asj8r16dwUAAAB4hCn5xmyFRAgXOgT8049obUg1hGWUKvOU7QLy7Z7a/VMbeuS5PlAUPgT8x/dPISLl9YmqJs/HPNtI5FvMigl9peGffkTK6xNVTZ6PebaRyLeYFZO7cszdP4WIlNcnqpo8H/NsI5FvMSsmd+WYu38KESmvT1Q1eT7m2UYi32JWTO7KMXf/FCJSXp+oavJ8zLONRL7JV0xVVeL5w0fY6M8xv9LD5+CIccfj44fmZOrfpErFK19VhaXVM1+fIUbx2nXcIqFfVZP1TKSqdmsgEvmS3YcT5gqxf4bVY8jyZP0bVul3UL4qRguxPh3HSVZ85tSuo+Y9ntr7vYmSM8k2EvmS3Ycela9+VRmue5f3j9D+RZansBPo7L0Q69NxzJ3RTGqbvKqdbI+bZBuJfNPuQ8P7L5f9Y37fZLeSDP1bK1MT/yYrTOq/4kx1XUzOWRpRfF1LI0r98Eeqy3cmi/U5ehZXIx9RtZYm54j5q9aJxTpPUtVqbldSqGploEvEbCORrwWTWozmPd9KtmeW1m7yv+JqE4/brSRz/7NRrP1LK2buX8xW6l8Vy+IVSfe/RUSVH9WKslif0v0vxprEleYs9aOqg911jFlVk9omr6rJKpVmG4l8Z3PyMoZmRs2V1uxG62uz1L/J7nLMn3+3X7oTxCi8N9VOcI842Y36iJOs9H4Maz67PqVXkI8iZTQxE42fRXnSqapJzkSqqslflW0k8jW53u4jZeU7e1U8+tfEclfWjjtBrLxmJ3hnNPH1qiLq955YGZPru4jRxIgTRtPkrFrnhsxLoarmtU1eVes1EIl8Ta63+6i/VxIrxR+XnrPoSovve5XavPtXnePRP3/mIv9inaX1kXKEXcVUP6WKJY0ovV7S12JSedX6VP0sf1wcxTWsulKTDKVnoqouVZ28CvNsI5FvBObVMIvHMfe/EMvdP4WIlNcnqpo8H/NsI5FvMSsm978Qy90/hYiU1yeqmjwf82wjkW8xKyZ35Zi7fwoRKa9PVDV5PubZRiLfYlZM7soxd/8UIlJen6hq8nzMs41EvtexvtpofGH5hoB/+hGRKilklOp1ZPKNDMZY7iHgn35EpEoKGaV6FZN842t71VMvfkcKdzEYMWLMboxHvnsfwQztxPXf991TF3AM/IqK8N+fcYgwP0eMEctx3j21yXMwnMe/jtbz7iVVEvlo5l523wLly6L0wkeur6qq+951T12gsaoq9/JpzMt7Y2r/fUzEj2iNfT6poqrBqupqy5RvBD3/0uWA8k3vv4dGk85z02iE8tHMc7ufcDUo31AG5bsU0GioavIcllTV1aB8Q1kR/sfVBo12Mc9NoxHKp6D7CVfLT/n6UsQa5etFFGveG/Px308wfOzIOBmN/9/JQf646JBH/IjWkGo0mqmiqsGq6mpOyrcK8LSWXvkOBQ2qfM/XzNWK8N+L7/nnBX3gNwA3uTiH3wwTP+JcpdE0/sUcxDxn43rUaCY14VI9TM7xnidT30+Eu47e7ydMrrtYQ7HOHueJle+ZB6F85Vak8mWXSkS2AcQtKj8iIn5Ea6i6kwRTRVWDVdXVMlO+3VMXWvkyT+RVhP9xtV1oH24n+NShGo02xMpCo13SBBGNFvU6Ws819xMy8k1+P+FqTj1fKF+NFax8+a0rbAlx08qPiIgf0RoajUYtVVQ1WFVdDcpXVpRslGlo/72qV6jeCaF6vqP+5f0T1Giy4iTXaFGvY6CeL5cqkfsJV4PylRUlG2Ua2n8vwkR98Ft0csMobOAXxI9oDb1GI5Uqqhqsqq6WmfKtzhb6aQd3/irC/7jaLnqa3OKe9jr5czTG+xznUo3G/ciFluH9GEfspXEt5lKNJhTn4vjkHINUA/Z8hVR9XkfrufR+QlZVu1QL6vmOryqy8vU1Vj7koca8vDem9m8vDSwQP6I1VBqNIFDVYFV1NSflG2LEX7hR8t+H60WKc1XPl+Bc1fMlOI9/Hb33fAnO0/d8Q4xQvpT89zEBjYaqJs9hSVVdDco3lBXhf1xt0GgX89w0GqF8CrqfcDUo31AG5bsU0GioavIcllTV1ZYp32hfTgflS8N/D40mneem0Qjlo5nndj/haguUb8wvp4PypeG/jwloNFQ1eQ5LqupqC5RvhJ7DZKzw7ZkYMWIkOcYj3y6wzuItzvd1hn5FRfiPrybSixoT7PNJFVUNVlVXo/u0g/tr01tofi/Cf4+er3TeoecbYN6h50vkaQf316a3IpRpaP99TECjoarJc1hSVVeD8oV/jf9xtUGjXcxz02iE8inofsLVoHzhX+O/jwloNFQ1eQ5LqupqmSlf7jOKXE3F775CFOG/n7zn858vJXy6lXjwMDk++uF9Kvbexc8qIk4/z2ySZ+X7s6zGuVSjqVM9qPKXFcdnnkxxPxH0Ovq9nxhzMEu1l6VaXM93fHnRlG/FEUrlTC5SfvcYogj/kvd8ft3PTsSf0iB+RGuoNBrBVFHVYFV1NUvly3OuX/7VKF+/5Bua34vwP642ub4btclwjmInXJwj9anv+fLaR7bl5P7FHDzOVT3fy+LIa8Jryck5MXu+4a6j9VzV8xWqqsuZvxZl9nwr7jssKq/fZ2HS83VnXjbXMw2krHPz34tQ7AT5hP8pqTce8SNaQ6PRqKWKqgarqqtZKt9AzGvytIMX5mWFfMdaaP+9VIfy+0HYAPY6VKPRuIjUNZqsOMk1WtTrGOJ+guE73EIz76zy9cW8TK0cfYUown8vYm7TiltUfkRE/IjW0HcnSaWKqgarqqvZK98QzGve83W32Z4p/HOrTaJ9DHXo5Q2jTpPqNZp5z3dyiyrsw+AazTDncc5rNO96Lf51tJ7r7ycMc+bvP8LdW1Dp+cZ82oE3x1eu4ndfIYrw30/AeZ7uTH5jzJromSk0WtCI1pBqNJqpoqrBqupqlsp38sJi9nx9mZf3rtL99+F6keJcpdEIzlUajeA8/nX0fj9BcJ6+5xtZ+fq10PxehP8+JuJHtIaqO0kQqGqwqrqafc830AjlS8n/uNqg0S7muWk0QvkUdD/halC+8K/x38cENBqqmjyHJVV1NShf+Nf476HRpPPcNBqhfDTz3O4nXG2Z8o32BZpQvjT89zEBjYaqJs9hSVVdbYHyjfkFmlC+NPyPqw0a7WKem0YjlE9B9xOutkD5Rug5YMSIESP90Z15F5Cvl2AwGAwGGwzkC4PBYAkM5AuDwWAJ7P96+HC+CsLNAAAAAABJRU5ErkJggg==" alt="" />

按照前面的操作，并没有进行下面的更新

而我在此之后又从发布服务器insert了一条主键为2,2,2,2的记录，以证明该操作在update之后已经传到了订阅端，而update被屏蔽了；

需要注意的是，由于按位操作是绝对严格，对于只更新column1和同时更新column1、column2将产生不同的bitmap，操作时一定要谨慎；

巴特西

Replication的犄角旮旯（三）--聊聊@bitmap

最新文章

热门文章