Mysql将默认的表字符集更改为数据库字符集

Question

我有mysql表字符集的问题。数据库中的每个表都有默认的字符集。例如:

CREATE TABLE privacy_settings (
  id_privacy_setting int(11) NOT NULL AUTO_INCREMENT,
  id_account int(11) NOT NULL,
  setting_name varchar(255) NOT NULL DEFAULT '0',
  privacy_level int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (id_privacy_setting),
  KEY fk_privacy_settings_accounts (id_account),
  CONSTRAINT fk_privacy_settings_accounts FOREIGN KEY (id_account) REFERENCES accounts (id_account) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8  

我想删除默认的CHARSET块，所以表可以使用数据库默认的CHARSET:

CREATE TABLE privacy_settings (
      id_privacy_setting int(11) NOT NULL AUTO_INCREMENT,
      id_account int(11) NOT NULL,
      setting_name varchar(255) NOT NULL DEFAULT '0',
      privacy_level int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (id_privacy_setting),
      KEY fk_privacy_settings_accounts (id_account),
      CONSTRAINT fk_privacy_settings_accounts FOREIGN KEY (id_account) REFERENCES accounts (id_account) ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB   

有没有办法在不重新创建表的情况下做到这一点?

Answer 1

改变一个表的字符集，来自MySQL文档:

如果你想改变表的默认字符集和所有字符列(CHAR, VARCHAR, TEXT)为一个新的字符集，使用这样的语句:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

---

如果要将整个数据库转换为使用不同的默认字符集，可以发出以下语句:(from default character set and Collation)

ALTER DATABASE db_name DEFAULT CHARACTER SET charset_name;

Answer 2

可以使用以下命令更改表和模式的排序规则和默认字符集:

alter table TABLENAME convert to CHARACTER SET utf8 COLLATE utf8_unicode_ci;
alter database SCHEMA default character set utf8 COLLATE utf8_unicode_ci;