Mysql change default table charset to database charset

Question

I have problem with mysql table charset. Every table in my database has default charset. For example:

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

I want to remove DEFAULT CHARSET block, so table could use database default 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

Is there any way to do this without recreating the table?

JYelton · Answer 1 · 2011-08-12T15:28:20.563

11

To change a table's character set, from MySQL Documentation:

If you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

If you want to convert an entire database to use a different default character set, you can issue this statement: (from Default Character Set and Collation)

ALTER DATABASE db_name DEFAULT CHARACTER SET charset_name;

edited Aug 12 '11 at 15:28

answered Aug 11 '11 at 15:32

JYelton

35,664
27
132
191

I know, but when I do so, `DEFAULT CHARSET` block is not removed from my table definition – Ris90 Aug 12 '11 at 06:07
You may want to change the entire database then. I've added more info to the answer. I suspect that the default charset and the active charset may be different for your table, but it depends on how you are generating the definition. `SHOW CREATE TABLE tbl_name` should work. After I execute an `ALTER TABLE` statement to change the character set, the `SHOW CREATE TABLE...` query shows the new character set as the DEFAULT CHARSET. To get a list of charsets/collations, use [`SHOW COLLATION`](http://dev.mysql.com/doc/refman/5.1/en/show-collation.html). – JYelton Aug 12 '11 at 15:37
I wannt to completely remove DEFAULT CHARSET block from table definition, not to chage default charset for table, but remove this block to make each table to use the database encoding – Ris90 Aug 16 '11 at 14:51
I don't believe you can *remove* the default charset. See [9.1.3. Specifying Character Sets and Collations](http://dev.mysql.com/doc/refman/5.1/en/charset-syntax.html) for more info, sorry I'm not sure what else to recommend. – JYelton Aug 16 '11 at 15:53

score 3 · Answer 2 · answered Dec 08 '12 at 10:09

3

You can use the following to change collation and default character set for tables and schemas:

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

answered Dec 08 '12 at 10:09

Abdo

13,549
10
79
98

Mysql change default table charset to database charset

2 Answers2

Linked