Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (gb2312_chinese_ci,错误

Question

我复制的结构为什么会出这个错误啊？

Answer 1

ADOQuery1.Close();
    ADOQuery1.SQL.Text := 'SET NAMES gbk;';
    ADOQuery1.ExecSQL();错误提示是illegal mix of collations( gbk_chinese_ci,implicit) and (latin1_swedish_ci,coercible)for operation '='
而且2个数据表是复制的结构，我想法是从一个表中随机取1条数据放到另一个数据表中，共取10次

Answer 2

while j<2 do
    begin
    Randomize;//有了这个每次随机数就不一样
    llaa:= random(10);
    ADOQuery1.Close();
    ADOQuery1.SQL.Text := 'SET NAMES gbk;';
    ADOQuery1.ExecSQL();
    ADOQuery1.Close;
    ADOQuery1.SQL.Clear ;
    ADOQuery1.SQL.Add('select * from  nc_user_copy where nc_id=:n12c_famelastid');
    ADOQuery1.Parameters.ParamByName('n12c_famelastid').Value :=llaa;
    ADOQuery1.Open ;
    if ADOQuery1.RecordCount >0 then
               begin                     //nicker:= ADOQuery1.FieldValues['nc_tuiguangren'];
                     //username:= ADOQuery1.FieldValues['nc_username'];
                     nick:= ADOQuery1.FieldValues['nc_nick'];
                     //psw:= ADOQuery1.FieldValues['nc_psw'];
                     exp:= ADOQuery1.FieldValues['nc_exp'];
                     gold:= ADOQuery1.FieldValues['nc_gold'];
                     egold:= ADOQuery1.FieldValues['nc_egold'];
                     land:= ADOQuery1.FieldValues['nc_land'];
                     vip:= ADOQuery1.FieldValues['nc_vip'];
                     //tuiguangcount:= ADOQuery1.FieldValues['nc_tuiguangcount'];
                     //zxtime:= ADOQuery1.FieldValues['nc_zxtime'];
                     sex:= ADOQuery1.FieldValues['nc_sex'];
                       if sex=0 then
                          sx:='男'
                          else
                          sx:='女' ;
                    //ADOQuery1.Next ;                     //ADOQuery2.Close;
                     //ADOQuery2.SQL.Clear ;
                     //ADOQuery2.SQL.Add('select * from  nc_vip0_friends5000 where nc_username='''+username+'''');
                     //ADOQuery2.Open ;
                     //if ADOQuery2.RecordCount =0 then
                        //begin
                          ADOQuery2.Close;
                          ADOQuery2.SQL.Clear ;
                          ADOQuery2.SQL.Add('select * from  nc_vip0_friends5000 where nc_friend='''+nick+'''');
                          ADOQuery2.Open ;
                          if ADOQuery2.RecordCount =0 then
                                begin                                ADOQuery1.Close();
                               ADOQuery1.SQL.Text := 'SET NAMES gbk;';
                               ADOQuery1.ExecSQL();                               ADOConnection2.BeginTrans  ;
                                              try
                                            ADOQuery2.Close;
                                                ADOQuery2.SQL.Clear ;
                                                adoquery2.SQL.Add('insert nc_vip0_friends5000(nc_friend,nc_nick,nc_sex,nc_exp,nc_gold,nc_egold,nc_land,nc_vip)') ;
                                                adoquery2.SQL.Add(' values(:nc_friend,:nc_nick,:nc_sex,:nc_exp,:nc_gold,:nc_egold,:nc_land,:nc_vip)');
                                                ADOQuery2.Parameters.ParamByName('nc_friend').Value :=nick ;
                                                ADOQuery2.Parameters.ParamValues['nc_nick']:='好几个几个好几' ;
                                                //ADOQuery2.Parameters.ParamValues['nc_psw']:=psw;
                                                //ADOQuery2.Parameters.ParamByName('nc_zxtime').Value :=zxtime ;
                                                ADOQuery2.Parameters.ParamValues['nc_sex']:=sex  ;
                                                ADOQuery2.Parameters.ParamValues['nc_exp']:=exp;
                                                ADOQuery2.Parameters.ParamValues['nc_gold']:=gold   ;
                                                ADOQuery2.Parameters.ParamValues['nc_egold']:=egold;
                                                ADOQuery2.Parameters.ParamValues['nc_land']:=land;
                                                ADOQuery2.Parameters.ParamValues['nc_vip']:=vip   ;
                                                //ADOQuery2.Parameters.ParamValues['nc_tuiguang']:=nicker   ;
                                                //ADOQuery2.Parameters.ParamValues['nc_tuiguangcount']:=tuiguangcount;                                                adoquery2.ExecSQL;
                                                ADOConnection2.CommitTrans       ;
                                              except
                                                ADOConnection2.RollbackTrans  ;
                                              end;
                                //end;
                          end;
                      self.memo1.lines.add(nick);
                    end;                    j:=j+1;
               end;end;

Answer 3

while j<2 do
    begin
//随机数
    Randomize;//有了这个每次随机数就不一样
    llaa:= random(10);
//让显示中文
    ADOQuery1.Close();
    ADOQuery1.SQL.Text := 'SET NAMES gbk;';
    ADOQuery1.ExecSQL();//取随机数那条数据
    ADOQuery1.Close;
    ADOQuery1.SQL.Clear ;
    ADOQuery1.SQL.Add('select * from  nc_user_copy where nc_id=:n12c_famelastid');
    ADOQuery1.Parameters.ParamByName('n12c_famelastid').Value :=llaa;
    ADOQuery1.Open ;
    if ADOQuery1.RecordCount >0 then
               begin                     //nicker:= ADOQuery1.FieldValues['nc_tuiguangren'];
                     //username:= ADOQuery1.FieldValues['nc_username'];
                     nick:= ADOQuery1.FieldValues['nc_nick'];
                     //psw:= ADOQuery1.FieldValues['nc_psw'];
                     exp:= ADOQuery1.FieldValues['nc_exp'];
                     gold:= ADOQuery1.FieldValues['nc_gold'];
                     egold:= ADOQuery1.FieldValues['nc_egold'];
                     land:= ADOQuery1.FieldValues['nc_land'];
                     vip:= ADOQuery1.FieldValues['nc_vip'];
                     //tuiguangcount:= ADOQuery1.FieldValues['nc_tuiguangcount'];
                     //zxtime:= ADOQuery1.FieldValues['nc_zxtime'];
                     sex:= ADOQuery1.FieldValues['nc_sex'];
                       if sex=0 then
                          sx:='男'
                          else
                          sx:='女' ;
                    //ADOQuery1.Next ;                     //ADOQuery2.Close;
                     //ADOQuery2.SQL.Clear ;
                     //ADOQuery2.SQL.Add('select * from  nc_vip0_friends5000 where nc_username='''+username+'''');
                     //ADOQuery2.Open ;
                     //if ADOQuery2.RecordCount =0 then
                        //begin
//判断是否重复，重复就不存
                          ADOQuery2.Close;
                          ADOQuery2.SQL.Clear ;
                          ADOQuery2.SQL.Add('select * from  nc_vip0_friends5000 where nc_friend='''+nick+'''');
                          ADOQuery2.Open ;
                          if ADOQuery2.RecordCount =0 then
                                begin                                ADOQuery1.Close();
                               ADOQuery1.SQL.Text := 'SET NAMES gbk;';
                               ADOQuery1.ExecSQL();
//将数据存入另一个数据表                               ADOConnection2.BeginTrans  ;
                                              try
                                            ADOQuery2.Close;
                                                ADOQuery2.SQL.Clear ;
                                                adoquery2.SQL.Add('insert nc_vip0_friends5000(nc_friend,nc_nick,nc_sex,nc_exp,nc_gold,nc_egold,nc_land,nc_vip)') ;
                                                adoquery2.SQL.Add(' values(:nc_friend,:nc_nick,:nc_sex,:nc_exp,:nc_gold,:nc_egold,:nc_land,:nc_vip)');
                                                ADOQuery2.Parameters.ParamByName('nc_friend').Value :=nick ;
                                                ADOQuery2.Parameters.ParamValues['nc_nick']:='好几个几个好几' ;
                                                //ADOQuery2.Parameters.ParamValues['nc_psw']:=psw;
                                                //ADOQuery2.Parameters.ParamByName('nc_zxtime').Value :=zxtime ;
                                                ADOQuery2.Parameters.ParamValues['nc_sex']:=sex  ;
                                                ADOQuery2.Parameters.ParamValues['nc_exp']:=exp;
                                                ADOQuery2.Parameters.ParamValues['nc_gold']:=gold   ;
                                                ADOQuery2.Parameters.ParamValues['nc_egold']:=egold;
                                                ADOQuery2.Parameters.ParamValues['nc_land']:=land;
                                                ADOQuery2.Parameters.ParamValues['nc_vip']:=vip   ;
                                                //ADOQuery2.Parameters.ParamValues['nc_tuiguang']:=nicker   ;
                                                //ADOQuery2.Parameters.ParamValues['nc_tuiguangcount']:=tuiguangcount;                                                adoquery2.ExecSQL;
                                                ADOConnection2.CommitTrans       ;
                                              except
                                                ADOConnection2.RollbackTrans  ;
                                              end;
                                //end;
                          end;
                      self.memo1.lines.add(nick);
                    end;                    j:=j+1;
               end;end;

Answer 4

show create table nc_user_copy
show create table nc_vip0_friends5000看一下你的表设置。

Answer 5

CREATE TABLE `NewTable` (
`nc_id`  int(4) NOT NULL AUTO_INCREMENT ,
`nc_username`  varchar(100) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL ,
`nc_nick`  varchar(200) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL ,
`nc_psw`  varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL ,
`nc_sex`  int(4) NOT NULL DEFAULT 0 ,
`nc_exp`  double(64,0) NOT NULL DEFAULT 0 ,
`nc_level`  int(4) UNSIGNED ZEROFILL NOT NULL DEFAULT 0000 ,
`nc_gold`  double(64,0) NOT NULL DEFAULT 0 ,
`nc_egold`  double(64,0) NOT NULL DEFAULT 0 ,
`nc_land`  int(4) NOT NULL DEFAULT 0 ,
`nc_vip`  int(4) NOT NULL DEFAULT 0 ,
`nc_tuiguangren`  varchar(200) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL ,
`nc_tuiguangcount`  int(4) NOT NULL DEFAULT 0 ,
`nc_touxiang`  varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL DEFAULT '..uploadimg/20099133112721741.jpg' ,
`nc_zxtime`  int(4) NOT NULL DEFAULT 0 COMMENT '每月在线时间，统计推广情况，推广人数100，所有推广人每月在线时间超过90小时，推广人可以获得1张会员卡，每月的1——5号领奖，己自得到30万金币。' ,
PRIMARY KEY (`nc_id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=gbk COLLATE=gbk_chinese_ci
AUTO_INCREMENT=12
ROW_FORMAT=COMPACT
;

Answer 6

CREATE TABLE `NewTable` (
`nc_id`  int(4) NOT NULL AUTO_INCREMENT ,
`nc_friend`  varchar(200) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL ,
`nc_nick`  varchar(200) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL ,
`nc_sex`  int(4) NOT NULL DEFAULT 0 ,
`nc_exp`  double(64,0) NOT NULL ,
`nc_gold`  double(64,0) NOT NULL ,
`nc_egold`  double(64,0) NOT NULL ,
`nc_land`  int(4) NOT NULL ,
`nc_vip`  tinyint(4) NOT NULL ,
`nc_touxiang`  varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL DEFAULT '..uploadimg/20099133112721741.jpg' ,
PRIMARY KEY (`nc_id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=gbk COLLATE=gbk_chinese_ci
AUTO_INCREMENT=1
ROW_FORMAT=COMPACT
;

Answer 7

对下面这个表却读写正常：
CREATE TABLE `NewTable` (
`cs_id`  int(6) NOT NULL AUTO_INCREMENT ,
`cs_username`  varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,
`cs_mima`  varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,
`cs_nicheng`  varchar(200) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,
`cs_exp`  double NOT NULL DEFAULT 0 ,
`cs_email`  varchar(30) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,
`cs_zhenshiname`  varchar(200) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,
`cs_shenfenzhengcode`  varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '0' ,
`cs_sex`  varchar(6) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '' ,
`cs_isqiye`  varchar(6) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '¸öÈË' ,
`cs_vip`  int(4) NOT NULL DEFAULT 0 ,
`adddate`  datetime NOT NULL DEFAULT '2009-12-01 18:58:54' ,
`cs_lastlogin`  datetime NOT NULL DEFAULT '2009-12-01 18:58:54' ,
`cs_logins`  int(4) UNSIGNED ZEROFILL NOT NULL DEFAULT 0000 ,
`cs_tuiguang`  varchar(200) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`cs_tuiguangshu`  int(4) UNSIGNED ZEROFILL NOT NULL DEFAULT 0000 ,
`cs_gold`  double NOT NULL ,
`cs_yuanbao`  double UNSIGNED ZEROFILL NOT NULL DEFAULT 0000000000000000000000 ,
`cs_land`  int(4) NOT NULL DEFAULT 0 ,
`cs_touxiang`  varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT 'uploadimg/20099133112721741.jpg' ,
`cs_shangdianname`  varchar(200) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`cs_sfztupian`  varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'sfzimg/2009815854894657.jpg' ,
`cs_yanzheng`  int(4) UNSIGNED ZEROFILL NOT NULL DEFAULT 0000 ,
`cs_zxtime`  double(10,3) UNSIGNED ZEROFILL NOT NULL DEFAULT 000000.000 ,
`cs_isno`  varchar(8) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'ÀëÏß' ,
`cs_tel`  varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '0' ,
`cs_haoping`  int(4) UNSIGNED ZEROFILL NOT NULL DEFAULT 0000 ,
`cs_chaping`  int(4) UNSIGNED ZEROFILL NOT NULL DEFAULT 0000 ,
`cs_qq`  varchar(30) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
PRIMARY KEY (`cs_id`, `cs_nicheng`)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET=gbk COLLATE=gbk_chinese_ci
AUTO_INCREMENT=8346
CHECKSUM=0
ROW_FORMAT=DYNAMIC
DELAY_KEY_WRITE=0
;

Answer 8

怎么表名都是newtable ?show create table nc_user_copy
show create table nc_vip0_friends5000你的程序中操作了这两个表，需要看这两个表的

Answer 9

上面那个是nc_user_copy,下面是nc_vip0_friends5000 
第三个的虽然能读写，但是中文是乱码

Answer 10

mysql> show create table nc_user_copy ;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| nc_user_copy | CREATE TABLE `nc_user_copy` (
  `nc_id` int(4) NOT NULL auto_increment,
  `nc_username` varchar(100) NOT NULL,
  `nc_nick` varchar(200) NOT NULL,
  `nc_psw` varchar(50) NOT NULL,
  `nc_sex` int(4) NOT NULL default '0',
  `nc_exp` double(64,0) NOT NULL default '0',
  `nc_level` int(4) unsigned zerofill NOT NULL default '0000',
  `nc_gold` double(64,0) NOT NULL default '0',
  `nc_egold` double(64,0) NOT NULL default '0',
  `nc_land` int(4) NOT NULL default '0',
  `nc_vip` int(4) NOT NULL default '0',
  `nc_tuiguangren` varchar(200) default NULL,
  `nc_tuiguangcount` int(4) NOT NULL default '0',
  `nc_touxiang` varchar(50) NOT NULL default '..uploadimg/20099133112721741.jpg',
  `nc_zxtime` int(4) NOT NULL default '0' COMMENT '每月在线时间，统计推广情况，推广人数100，所有推广人每月在线时间超过90小时，推广人可以获得1张会员卡，每月的1——5号领奖，己自得到30万金币。',
  PRIMARY KEY  (`nc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in setmysql> 

Answer 11

mysql> show create table nc_vip0_friends5000 ;
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table               | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| nc_vip0_friends5000 | CREATE TABLE `nc_vip0_friends5000` (
  `nc_id` int(4) NOT NULL auto_increment,
  `nc_friend` varchar(200) NOT NULL,
  `nc_nick` varchar(200) NOT NULL,
  `nc_sex` int(4) NOT NULL default '0',
  `nc_exp` double(64,0) NOT NULL,
  `nc_gold` double(64,0) NOT NULL,
  `nc_egold` double(64,0) NOT NULL,
  `nc_land` int(4) NOT NULL,
  `nc_vip` tinyint(4) NOT NULL,
  `nc_touxiang` varchar(50) NOT NULL default '..uploadimg/20099133112721741.jpg',
  PRIMARY KEY  (`nc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set