CODEWITHSUNDEEP
mysql
Ris90
Ris90

Reputation: 841

Mysql change default table charset to database charset

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?

Upvotes: 6

Views: 13199

Answers (2)

Abdo
Abdo

Reputation: 14051

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;

Upvotes: 3

JYelton
JYelton

Reputation: 36512

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;

Upvotes: 11

Related Questions