github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/cdc/sink/dmlsink/txn/mysql/dml_test.go (about)

     1  // Copyright 2022 PingCAP, Inc.
     2  //
     3  // Licensed under the Apache License, Version 2.0 (the "License");
     4  // you may not use this file except in compliance with the License.
     5  // You may obtain a copy of the License at
     6  //
     7  //     http://www.apache.org/licenses/LICENSE-2.0
     8  //
     9  // Unless required by applicable law or agreed to in writing, software
    10  // distributed under the License is distributed on an "AS IS" BASIS,
    11  // See the License for the specific language governing permissions and
    12  // limitations under the License.
    13  
    14  package mysql
    15  
    16  import (
    17  	"testing"
    18  
    19  	"github.com/pingcap/tidb/pkg/parser/charset"
    20  	"github.com/pingcap/tidb/pkg/parser/mysql"
    21  	"github.com/pingcap/tiflow/cdc/model"
    22  	"github.com/stretchr/testify/require"
    23  )
    24  
    25  func TestPrepareUpdate(t *testing.T) {
    26  	t.Parallel()
    27  	testCases := []struct {
    28  		quoteTable   string
    29  		preCols      []*model.Column
    30  		cols         []*model.Column
    31  		expectedSQL  string
    32  		expectedArgs []interface{}
    33  	}{
    34  		{
    35  			quoteTable:   "`test`.`t1`",
    36  			preCols:      []*model.Column{},
    37  			cols:         []*model.Column{},
    38  			expectedSQL:  "",
    39  			expectedArgs: nil,
    40  		},
    41  		{
    42  			quoteTable: "`test`.`t1`",
    43  			preCols: []*model.Column{
    44  				{
    45  					Name:  "a",
    46  					Type:  mysql.TypeLong,
    47  					Flag:  model.HandleKeyFlag | model.PrimaryKeyFlag,
    48  					Value: 1,
    49  				},
    50  				{
    51  					Name:  "b",
    52  					Type:  mysql.TypeVarchar,
    53  					Flag:  0,
    54  					Value: "test",
    55  				},
    56  			},
    57  			cols: []*model.Column{
    58  				{
    59  					Name:  "a",
    60  					Type:  mysql.TypeLong,
    61  					Flag:  model.HandleKeyFlag | model.PrimaryKeyFlag,
    62  					Value: 1,
    63  				},
    64  				{Name: "b", Type: mysql.TypeVarchar, Flag: 0, Value: "test2"},
    65  			},
    66  			expectedSQL:  "UPDATE `test`.`t1` SET `a` = ?, `b` = ? WHERE `a` = ? LIMIT 1",
    67  			expectedArgs: []interface{}{1, "test2", 1},
    68  		},
    69  		{
    70  			quoteTable: "`test`.`t1`",
    71  			preCols: []*model.Column{
    72  				{
    73  					Name:  "a",
    74  					Type:  mysql.TypeLong,
    75  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
    76  					Value: 1,
    77  				},
    78  				{
    79  					Name:  "b",
    80  					Type:  mysql.TypeVarString,
    81  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
    82  					Value: "test",
    83  				},
    84  				{
    85  					Name:  "c",
    86  					Type:  mysql.TypeLong,
    87  					Flag:  model.GeneratedColumnFlag,
    88  					Value: 100,
    89  				},
    90  			},
    91  			cols: []*model.Column{
    92  				{
    93  					Name:  "a",
    94  					Type:  mysql.TypeLong,
    95  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
    96  					Value: 2,
    97  				},
    98  				{
    99  					Name:  "b",
   100  					Type:  mysql.TypeVarString,
   101  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   102  					Value: "test2",
   103  				},
   104  				{
   105  					Name: "c",
   106  					Type: mysql.TypeLong, Flag: model.GeneratedColumnFlag,
   107  					Value: 100,
   108  				},
   109  			},
   110  			expectedSQL:  "UPDATE `test`.`t1` SET `a` = ?, `b` = ? WHERE `a` = ? AND `b` = ? LIMIT 1",
   111  			expectedArgs: []interface{}{2, "test2", 1, "test"},
   112  		},
   113  		{
   114  			quoteTable: "`test`.`t1`",
   115  			preCols: []*model.Column{
   116  				{
   117  					Name:  "a",
   118  					Type:  mysql.TypeLong,
   119  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   120  					Value: 1,
   121  				},
   122  				{
   123  					Name: "b", Type: mysql.TypeVarchar,
   124  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   125  					Value: []byte("你好"),
   126  				},
   127  				{
   128  					Name:  "c",
   129  					Type:  mysql.TypeLong,
   130  					Flag:  model.GeneratedColumnFlag,
   131  					Value: 100,
   132  				},
   133  			},
   134  			cols: []*model.Column{
   135  				{
   136  					Name:  "a",
   137  					Type:  mysql.TypeLong,
   138  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   139  					Value: 2,
   140  				},
   141  				{
   142  					Name:  "b",
   143  					Type:  mysql.TypeVarchar,
   144  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   145  					Value: []byte("世界"),
   146  				},
   147  				{
   148  					Name:  "c",
   149  					Type:  mysql.TypeLong,
   150  					Flag:  model.GeneratedColumnFlag,
   151  					Value: 100,
   152  				},
   153  			},
   154  			expectedSQL:  "UPDATE `test`.`t1` SET `a` = ?, `b` = ? WHERE `a` = ? AND `b` = ? LIMIT 1",
   155  			expectedArgs: []interface{}{2, []byte("世界"), 1, []byte("你好")},
   156  		},
   157  		{
   158  			quoteTable: "`test`.`t1`",
   159  			preCols: []*model.Column{
   160  				{
   161  					Name:  "a",
   162  					Type:  mysql.TypeLong,
   163  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   164  					Value: 1,
   165  				},
   166  				{
   167  					Name:    "b",
   168  					Type:    mysql.TypeTinyBlob,
   169  					Flag:    model.MultipleKeyFlag | model.HandleKeyFlag,
   170  					Charset: charset.CharsetBin,
   171  					Value:   []byte("你好"),
   172  				},
   173  				{
   174  					Name:  "c",
   175  					Type:  mysql.TypeLong,
   176  					Flag:  model.GeneratedColumnFlag,
   177  					Value: 100,
   178  				},
   179  			},
   180  			cols: []*model.Column{
   181  				{
   182  					Name:  "a",
   183  					Type:  mysql.TypeLong,
   184  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   185  					Value: 2,
   186  				},
   187  				{
   188  					Name:    "b",
   189  					Type:    mysql.TypeTinyBlob,
   190  					Flag:    model.MultipleKeyFlag | model.HandleKeyFlag,
   191  					Charset: charset.CharsetBin,
   192  					Value:   []byte("世界"),
   193  				},
   194  				{
   195  					Name:  "c",
   196  					Type:  mysql.TypeLong,
   197  					Flag:  model.GeneratedColumnFlag,
   198  					Value: 100,
   199  				},
   200  			},
   201  			expectedSQL:  "UPDATE `test`.`t1` SET `a` = ?, `b` = ? WHERE `a` = ? AND `b` = ? LIMIT 1",
   202  			expectedArgs: []interface{}{2, []byte("世界"), 1, []byte("你好")},
   203  		},
   204  		{
   205  			quoteTable: "`test`.`t1`",
   206  			preCols: []*model.Column{
   207  				{
   208  					Name:  "a",
   209  					Type:  mysql.TypeLong,
   210  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   211  					Value: 1,
   212  				},
   213  				{
   214  					Name:    "b",
   215  					Type:    mysql.TypeTinyBlob,
   216  					Flag:    model.MultipleKeyFlag | model.HandleKeyFlag,
   217  					Charset: charset.CharsetGBK,
   218  					Value:   "你好",
   219  				},
   220  				{
   221  					Name:  "c",
   222  					Type:  mysql.TypeLong,
   223  					Flag:  model.GeneratedColumnFlag,
   224  					Value: 100,
   225  				},
   226  			},
   227  			cols: []*model.Column{
   228  				{
   229  					Name:  "a",
   230  					Type:  mysql.TypeLong,
   231  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   232  					Value: 2,
   233  				},
   234  				{
   235  					Name:    "b",
   236  					Type:    mysql.TypeTinyBlob,
   237  					Flag:    model.MultipleKeyFlag | model.HandleKeyFlag,
   238  					Charset: charset.CharsetGBK,
   239  					Value:   "世界",
   240  				},
   241  				{
   242  					Name:  "c",
   243  					Type:  mysql.TypeLong,
   244  					Flag:  model.GeneratedColumnFlag,
   245  					Value: 100,
   246  				},
   247  			},
   248  			expectedSQL:  "UPDATE `test`.`t1` SET `a` = ?, `b` = ? WHERE `a` = ? AND `b` = ? LIMIT 1",
   249  			expectedArgs: []interface{}{2, "世界", 1, "你好"},
   250  		},
   251  	}
   252  	for _, tc := range testCases {
   253  		query, args := prepareUpdate(tc.quoteTable, tc.preCols, tc.cols, false)
   254  		require.Equal(t, tc.expectedSQL, query)
   255  		require.Equal(t, tc.expectedArgs, args)
   256  	}
   257  }
   258  
   259  func TestPrepareDelete(t *testing.T) {
   260  	t.Parallel()
   261  	testCases := []struct {
   262  		quoteTable   string
   263  		preCols      []*model.Column
   264  		expectedSQL  string
   265  		expectedArgs []interface{}
   266  	}{
   267  		{
   268  			quoteTable:   "`test`.`t1`",
   269  			preCols:      []*model.Column{},
   270  			expectedSQL:  "",
   271  			expectedArgs: nil,
   272  		},
   273  		{
   274  			quoteTable: "`test`.`t1`",
   275  			preCols: []*model.Column{
   276  				{
   277  					Name:  "a",
   278  					Type:  mysql.TypeLong,
   279  					Flag:  model.HandleKeyFlag | model.PrimaryKeyFlag,
   280  					Value: 1,
   281  				},
   282  				{
   283  					Name:  "b",
   284  					Type:  mysql.TypeVarchar,
   285  					Flag:  0,
   286  					Value: "test",
   287  				},
   288  			},
   289  			expectedSQL:  "DELETE FROM `test`.`t1` WHERE `a` = ? LIMIT 1",
   290  			expectedArgs: []interface{}{1},
   291  		},
   292  		{
   293  			quoteTable: "`test`.`t1`",
   294  			preCols: []*model.Column{
   295  				{
   296  					Name:  "a",
   297  					Type:  mysql.TypeLong,
   298  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   299  					Value: 1,
   300  				},
   301  				{
   302  					Name:  "b",
   303  					Type:  mysql.TypeVarString,
   304  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   305  					Value: "test",
   306  				},
   307  				{
   308  					Name:  "c",
   309  					Type:  mysql.TypeLong,
   310  					Flag:  model.GeneratedColumnFlag,
   311  					Value: 100,
   312  				},
   313  			},
   314  			expectedSQL:  "DELETE FROM `test`.`t1` WHERE `a` = ? AND `b` = ? LIMIT 1",
   315  			expectedArgs: []interface{}{1, "test"},
   316  		},
   317  		{
   318  			quoteTable: "`test`.`t1`",
   319  			preCols: []*model.Column{
   320  				{
   321  					Name:  "a",
   322  					Type:  mysql.TypeLong,
   323  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   324  					Value: 1,
   325  				},
   326  				{
   327  					Name: "b", Type: mysql.TypeVarchar,
   328  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   329  					Value: []byte("你好"),
   330  				},
   331  				{
   332  					Name:  "c",
   333  					Type:  mysql.TypeLong,
   334  					Flag:  model.GeneratedColumnFlag,
   335  					Value: 100,
   336  				},
   337  			},
   338  			expectedSQL:  "DELETE FROM `test`.`t1` WHERE `a` = ? AND `b` = ? LIMIT 1",
   339  			expectedArgs: []interface{}{1, []byte("你好")},
   340  		},
   341  		{
   342  			quoteTable: "`test`.`t1`",
   343  			preCols: []*model.Column{
   344  				{
   345  					Name:  "a",
   346  					Type:  mysql.TypeLong,
   347  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   348  					Value: 1,
   349  				},
   350  				{
   351  					Name:    "b",
   352  					Type:    mysql.TypeTinyBlob,
   353  					Flag:    model.MultipleKeyFlag | model.HandleKeyFlag,
   354  					Charset: charset.CharsetBin,
   355  					Value:   []byte("你好"),
   356  				},
   357  				{
   358  					Name:  "c",
   359  					Type:  mysql.TypeLong,
   360  					Flag:  model.GeneratedColumnFlag,
   361  					Value: 100,
   362  				},
   363  			},
   364  			expectedSQL:  "DELETE FROM `test`.`t1` WHERE `a` = ? AND `b` = ? LIMIT 1",
   365  			expectedArgs: []interface{}{1, []byte("你好")},
   366  		},
   367  		{
   368  			quoteTable: "`test`.`t1`",
   369  			preCols: []*model.Column{
   370  				{
   371  					Name:  "a",
   372  					Type:  mysql.TypeLong,
   373  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   374  					Value: 1,
   375  				},
   376  				{
   377  					Name:    "b",
   378  					Type:    mysql.TypeTinyBlob,
   379  					Flag:    model.MultipleKeyFlag | model.HandleKeyFlag,
   380  					Charset: charset.CharsetGBK,
   381  					Value:   "你好",
   382  				},
   383  				{
   384  					Name:  "c",
   385  					Type:  mysql.TypeLong,
   386  					Flag:  model.GeneratedColumnFlag,
   387  					Value: 100,
   388  				},
   389  			},
   390  			expectedSQL:  "DELETE FROM `test`.`t1` WHERE `a` = ? AND `b` = ? LIMIT 1",
   391  			expectedArgs: []interface{}{1, "你好"},
   392  		},
   393  	}
   394  	for _, tc := range testCases {
   395  		query, args := prepareDelete(tc.quoteTable, tc.preCols, false)
   396  		require.Equal(t, tc.expectedSQL, query)
   397  		require.Equal(t, tc.expectedArgs, args)
   398  	}
   399  }
   400  
   401  func TestWhereSlice(t *testing.T) {
   402  	t.Parallel()
   403  	testCases := []struct {
   404  		cols             []*model.Column
   405  		forceReplicate   bool
   406  		expectedColNames []string
   407  		expectedArgs     []interface{}
   408  	}{
   409  		{
   410  			cols:             []*model.Column{},
   411  			forceReplicate:   false,
   412  			expectedColNames: nil,
   413  			expectedArgs:     nil,
   414  		},
   415  		{
   416  			cols: []*model.Column{
   417  				{
   418  					Name:  "a",
   419  					Type:  mysql.TypeLong,
   420  					Flag:  model.HandleKeyFlag | model.PrimaryKeyFlag,
   421  					Value: 1,
   422  				},
   423  				{
   424  					Name:  "b",
   425  					Type:  mysql.TypeVarchar,
   426  					Flag:  0,
   427  					Value: "test",
   428  				},
   429  			},
   430  			forceReplicate:   false,
   431  			expectedColNames: []string{"a"},
   432  			expectedArgs:     []interface{}{1},
   433  		},
   434  		{
   435  			cols: []*model.Column{
   436  				{
   437  					Name:  "a",
   438  					Type:  mysql.TypeLong,
   439  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   440  					Value: 1,
   441  				},
   442  				{
   443  					Name: "b", Type: mysql.TypeVarString,
   444  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   445  					Value: "test",
   446  				},
   447  				{
   448  					Name:  "c",
   449  					Type:  mysql.TypeLong,
   450  					Flag:  model.GeneratedColumnFlag,
   451  					Value: 100,
   452  				},
   453  			},
   454  			forceReplicate:   false,
   455  			expectedColNames: []string{"a", "b"},
   456  			expectedArgs:     []interface{}{1, "test"},
   457  		},
   458  		{
   459  			cols:             []*model.Column{},
   460  			forceReplicate:   true,
   461  			expectedColNames: []string{},
   462  			expectedArgs:     []interface{}{},
   463  		},
   464  		{
   465  			cols: []*model.Column{
   466  				{
   467  					Name:  "a",
   468  					Type:  mysql.TypeLong,
   469  					Flag:  model.HandleKeyFlag | model.PrimaryKeyFlag,
   470  					Value: 1,
   471  				},
   472  				{
   473  					Name:  "b",
   474  					Type:  mysql.TypeVarchar,
   475  					Flag:  0,
   476  					Value: "test",
   477  				},
   478  			},
   479  			forceReplicate:   true,
   480  			expectedColNames: []string{"a"},
   481  			expectedArgs:     []interface{}{1},
   482  		},
   483  		{
   484  			cols: []*model.Column{
   485  				{
   486  					Name:  "a",
   487  					Type:  mysql.TypeLong,
   488  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   489  					Value: 1,
   490  				},
   491  				{
   492  					Name:  "b",
   493  					Type:  mysql.TypeVarString,
   494  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   495  					Value: "test",
   496  				},
   497  				{
   498  					Name:  "c",
   499  					Type:  mysql.TypeLong,
   500  					Flag:  model.GeneratedColumnFlag,
   501  					Value: 100,
   502  				},
   503  			},
   504  			forceReplicate:   true,
   505  			expectedColNames: []string{"a", "b"},
   506  			expectedArgs:     []interface{}{1, "test"},
   507  		},
   508  		{
   509  			cols: []*model.Column{
   510  				{
   511  					Name:  "a",
   512  					Type:  mysql.TypeLong,
   513  					Flag:  model.UniqueKeyFlag,
   514  					Value: 1,
   515  				},
   516  				{
   517  					Name:  "b",
   518  					Type:  mysql.TypeVarchar,
   519  					Flag:  0,
   520  					Value: "test",
   521  				},
   522  			},
   523  			forceReplicate:   true,
   524  			expectedColNames: []string{"a", "b"},
   525  			expectedArgs:     []interface{}{1, "test"},
   526  		},
   527  		{
   528  			cols: []*model.Column{
   529  				{
   530  					Name:  "a",
   531  					Type:  mysql.TypeLong,
   532  					Flag:  model.MultipleKeyFlag,
   533  					Value: 1,
   534  				},
   535  				{
   536  					Name:  "b",
   537  					Type:  mysql.TypeVarString,
   538  					Flag:  model.MultipleKeyFlag,
   539  					Value: "test",
   540  				},
   541  				{
   542  					Name:  "c",
   543  					Type:  mysql.TypeLong,
   544  					Flag:  model.GeneratedColumnFlag,
   545  					Value: 100,
   546  				},
   547  			},
   548  			forceReplicate:   true,
   549  			expectedColNames: []string{"a", "b", "c"},
   550  			expectedArgs:     []interface{}{1, "test", 100},
   551  		},
   552  		{
   553  			cols: []*model.Column{
   554  				{
   555  					Name:  "a",
   556  					Type:  mysql.TypeLong,
   557  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   558  					Value: 1,
   559  				},
   560  				{
   561  					Name:  "b",
   562  					Type:  mysql.TypeTinyBlob,
   563  					Flag:  model.MultipleKeyFlag | model.HandleKeyFlag,
   564  					Value: []byte("你好"),
   565  				},
   566  				{
   567  					Name:  "c",
   568  					Type:  mysql.TypeLong,
   569  					Flag:  model.GeneratedColumnFlag,
   570  					Value: 100,
   571  				},
   572  			},
   573  			forceReplicate:   false,
   574  			expectedColNames: []string{"a", "b"},
   575  			expectedArgs:     []interface{}{1, []byte("你好")},
   576  		},
   577  		{
   578  			cols: []*model.Column{
   579  				{
   580  					Name:  "a",
   581  					Type:  mysql.TypeLong,
   582  					Flag:  model.MultipleKeyFlag,
   583  					Value: 1,
   584  				},
   585  				{
   586  					Name:    "b",
   587  					Type:    mysql.TypeTinyBlob,
   588  					Flag:    model.MultipleKeyFlag,
   589  					Charset: charset.CharsetGBK,
   590  					Value:   []byte("你好"),
   591  				},
   592  				{
   593  					Name:  "c",
   594  					Type:  mysql.TypeLong,
   595  					Flag:  model.GeneratedColumnFlag,
   596  					Value: 100,
   597  				},
   598  			},
   599  			forceReplicate:   true,
   600  			expectedColNames: []string{"a", "b", "c"},
   601  			expectedArgs:     []interface{}{1, "你好", 100},
   602  		},
   603  	}
   604  	for _, tc := range testCases {
   605  		colNames, args := whereSlice(tc.cols, tc.forceReplicate)
   606  		require.Equal(t, tc.expectedColNames, colNames)
   607  		require.Equal(t, tc.expectedArgs, args)
   608  	}
   609  }
   610  
   611  func TestMapReplace(t *testing.T) {
   612  	t.Parallel()
   613  	testCases := []struct {
   614  		quoteTable    string
   615  		cols          []*model.Column
   616  		expectedQuery string
   617  		expectedArgs  []interface{}
   618  	}{
   619  		{
   620  			quoteTable: "`test`.`t1`",
   621  			cols: []*model.Column{
   622  				{
   623  					Name:  "a",
   624  					Type:  mysql.TypeLong,
   625  					Value: 1,
   626  				},
   627  				{
   628  					Name:  "b",
   629  					Type:  mysql.TypeVarchar,
   630  					Value: "varchar",
   631  				},
   632  				{
   633  					Name:  "c",
   634  					Type:  mysql.TypeLong,
   635  					Value: 1,
   636  					Flag:  model.GeneratedColumnFlag,
   637  				},
   638  				{
   639  					Name:  "d",
   640  					Type:  mysql.TypeTiny,
   641  					Value: uint8(255),
   642  				},
   643  			},
   644  			expectedQuery: "REPLACE INTO `test`.`t1` (`a`,`b`,`d`) VALUES ",
   645  			expectedArgs:  []interface{}{1, "varchar", uint8(255)},
   646  		},
   647  		{
   648  			quoteTable: "`test`.`t1`",
   649  			cols: []*model.Column{
   650  				{
   651  					Name:  "a",
   652  					Type:  mysql.TypeLong,
   653  					Value: 1,
   654  				},
   655  				{
   656  					Name:  "b",
   657  					Type:  mysql.TypeVarchar,
   658  					Value: "varchar",
   659  				},
   660  				{
   661  					Name:  "c",
   662  					Type:  mysql.TypeLong,
   663  					Value: 1,
   664  				},
   665  				{
   666  					Name:  "d",
   667  					Type:  mysql.TypeTiny,
   668  					Value: uint8(255),
   669  				},
   670  			},
   671  			expectedQuery: "REPLACE INTO `test`.`t1` (`a`,`b`,`c`,`d`) VALUES ",
   672  			expectedArgs:  []interface{}{1, "varchar", 1, uint8(255)},
   673  		},
   674  		{
   675  			quoteTable: "`test`.`t1`",
   676  			cols: []*model.Column{
   677  				{
   678  					Name:  "a",
   679  					Type:  mysql.TypeLong,
   680  					Value: 1,
   681  				},
   682  				{
   683  					Name:    "b",
   684  					Type:    mysql.TypeVarchar,
   685  					Charset: charset.CharsetGBK,
   686  					Value:   []byte("你好"),
   687  				},
   688  				{
   689  					Name:    "c",
   690  					Type:    mysql.TypeTinyBlob,
   691  					Charset: charset.CharsetUTF8MB4,
   692  					Value:   []byte("世界"),
   693  				},
   694  				{
   695  					Name:    "d",
   696  					Type:    mysql.TypeMediumBlob,
   697  					Charset: charset.CharsetBin,
   698  					Value:   []byte("你好,世界"),
   699  				},
   700  				{
   701  					Name:  "e",
   702  					Type:  mysql.TypeBlob,
   703  					Value: []byte("你好,世界"),
   704  				},
   705  			},
   706  			expectedQuery: "REPLACE INTO `test`.`t1` (`a`,`b`,`c`,`d`,`e`) VALUES ",
   707  			expectedArgs: []interface{}{
   708  				1, "你好", "世界", []byte("你好,世界"),
   709  				[]byte("你好,世界"),
   710  			},
   711  		},
   712  	}
   713  	for _, tc := range testCases {
   714  		// multiple times to verify the stability of column sequence in query string
   715  		for i := 0; i < 10; i++ {
   716  			query, args := prepareReplace(tc.quoteTable, tc.cols, false, false)
   717  			require.Equal(t, tc.expectedQuery, query)
   718  			require.Equal(t, tc.expectedArgs, args)
   719  		}
   720  	}
   721  }