Member Avatar for yanwick

Hi,

I would like to know how to set a character set once and permanently, since when i alter the character_set% variables
that are shown on 'show varables like 'character_set%', when i alter them the next day they are back with the old values?

Thank you very much

  • 2 Contributors
  • 2 Replies
  • 245 Views
  • 5 Hours Discussion Span
  • Latest Post Latest Post by yanwick

Dani AI

Generated

As noted, MySQL’s character set is layered (server → database → table → column, plus client/connection). Changing the variables at runtime (SET ...) only affects the current server instance; to make a change persistent the server config must be edited and the server restarted. Also, changing defaults does not alter existing tables or columns — those must be converted explicitly.

Edit the server config (my.cnf / my.ini) to set permanent defaults, then restart the server. Example:

# my.cnf / my.ini
[mysqld]
character-set-server = latin1
collation-server = latin1_swedish_ci

[client]
default-character-set = latin1

[mysql]
default-character-set = latin1

To set database defaults or create a new DB with latin1:

CREATE DATABASE mydb CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER DATABASE mydb DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

To convert existing tables/columns (note: this rewrites data — backup first):

ALTER TABLE mytable CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
-- or change a single column:
ALTER TABLE mytable MODIFY col VARCHAR(200) CHARACTER SET latin1;

Quick checks and cautions: find non-latin1 columns with a query on INFORMATION_SCHEMA.COLUMNS, for example:

SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,CHARACTER_SET_NAME
FROM information_schema.COLUMNS
WHERE CHARACTER_SET_NAME IS NOT NULL AND CHARACTER_SET_NAME <> 'latin1';

Remember that SET NAMES 'latin1' affects only the current connection (client/connection variables). Application drivers (JDBC/PDO/mysqli) must be configured to use the same charset to avoid mojibake. Backup and test conversions on a copy before applying to production. If multilingual data is needed, consider migrating to UTF-8 (utf8mb4) instead of latin1.

Recommended Answers

All 2 Replies

Member Avatar for smantscheff

The character set is a property of the server, the database, the table and the field - in that order. Each has a default which can be overwritten by the following. You can change it using the alter table syntax.
Then there is the client and the connection character set which define how the character set is translated on its way from the database to the end user. You can alter those by setting defaults in my.ini (or my.cnf) or by starting the mysql server with explicit values for those variables:

character_set_client
character_set_connection
character_set_database
character_set_filesystem
character_set_results
character_set_server
character_set_system
character_sets_dir

Member Avatar for yanwick

thanks a lot

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.