CODEWITHSUNDEEP
mysqlsql
DoubleTrouble
DoubleTrouble

Reputation: 902

Character set and collation in database

I am a beginner when it comes to databases, so please bear with me. I'm trying to set up a database and import some tables from a file tables.sql. Some of the Columns in tables.sql have Swedish letters in them (Ä, Ö) and the problem is that I get the following:

Ä = ä
Ö = ö

First I begin to check the character set of the server:

mysql> show variables like 'character_set_server';

The server is configured to character set 'Latin-1'. I must mention that I have no control over the server more than to create a database. So I guess I have to create my database and specify the character set of the database.

This is how I proceed:

mysql> create database db;

mysql> alter database db character set utf8 collate utf8_swedish_ci;

I double checked that my tables.sql have charset utf-8 by executing:

file -bi allsok_tables.sql

And then I load it into the database by:

$ mysql -u [username] -h [hostname] -P [port] -p db < tables.sql

when I create my tables in tables.sql I use engine = InnoDB (don't know if this is relevant or not). However if I now select everything from the table TableTest

mysql> select * from TableTest

I get these weird characters instead of the Swedish characters. I appreciate any help right now.

Thanks in advance!

UPDATE:

If I insert a value manually into a table it works e.g.

mysql> insert into TableTest values ('åäö');

So the problem seems to be with the .sql-file. Right?

Upvotes: 0

Views: 269

Answers (1)

deceze
deceze

Reputation: 521995

$ mysql ... --default-character-set=utf8 < tables.sql
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^

MySQL needs to know what encoding the data you're sending it is in. To do this, you need to set the connection encoding. When connecting to MySQL from a client, you usually run a SET NAMES query or use an equivalent call on your API of choice to do so. On the command line, the --default-character-set option does this. It needs to be set to whatever encoding your file is in.

Upvotes: 1

Related Questions