UK collation sequence

I am working on a MySQL 5.0 database that automatically sets the
default collation sequence to latin1_swedish_ci. It seems to be given
some accented characters and messing up the UK currency symbol.

The server is a shared hosting server and I don't think I can configure
my own settings

What is the default collation sequence should I use for trouble free UK
oriented work when creating a table?

Are there some additional settings I can use on the client side when
connecting? I am using PHP, Microsoft Access and SQLYog clients.

voipfc wrote:
> I am working on a MySQL 5.0 database that automatically sets the
> default collation sequence to latin1_swedish_ci. It seems to be given
> some accented characters and messing up the UK currency symbol.
>
> The server is a shared hosting server and I don't think I can configure
> my own settings
>
> What is the default collation sequence should I use for trouble free UK
> oriented work when creating a table?
>
> Are there some additional settings I can use on the client side when
> connecting? I am using PHP, Microsoft Access and SQLYog clients.

A collation sequence only effects collation, that is the order in which
data is returned when an ORDER BY clause is included in the SELECT.

voipfc ¼g¹D¡G

> I am working on a MySQL 5.0 database that automatically sets the
> default collation sequence to latin1_swedish_ci. It seems to be given
> some accented characters and messing up the UK currency symbol.
>
> The server is a shared hosting server and I don't think I can configure
> my own settings
>
> What is the default collation sequence should I use for trouble free UK
> oriented work when creating a table?
>
> Are there some additional settings I can use on the client side when
> connecting? I am using PHP, Microsoft Access and SQLYog clients.

if you are not sure the servers' settings, you can execute the query:

SET NAMES 'charset_name' COLLATE 'collation_name'
http://dev.mysql.com/doc/refman/5.0/...onnection.html

which make sure you are working with the correct settings.

howa wrote:
> voipfc ¼g¹D¡G
>
> > I am working on a MySQL 5.0 database that automatically sets the
> > default collation sequence to latin1_swedish_ci. It seems to be given
> > some accented characters and messing up the UK currency symbol.
> >
> > The server is a shared hosting server and I don't think I can configure
> > my own settings
> >
> > What is the default collation sequence should I use for trouble free UK
> > oriented work when creating a table?
> >
> > Are there some additional settings I can use on the client side when
> > connecting? I am using PHP, Microsoft Access and SQLYog clients.
>
> if you are not sure the servers' settings, you can execute the query:
>
> SET NAMES 'charset_name' COLLATE 'collation_name'
> http://dev.mysql.com/doc/refman/5.0/...onnection.html
>
> which make sure you are working with the correct settings.


These are the values I obtain when I check the character set and
collation sequences on the server.

mysql> show variables like 'character_set%';
+--------------------------+-----------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /data/mysql/kyong/share/mysql/charsets/ |
+--------------------------+-----------------------------------------+
7 rows in set (0.01 sec)

mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.02 sec)

Is there any info here that can help me further with this issue?

The collation sequence doesn't seem to be the issue, could the
character set system being utf8 instead of latin1 be the cause of the
problem?

In the example set names command shown above would
SET NAMES 'utf8' COLLATE 'latin_swedish_ci' be the answer?

What is the basis of choosing between utf8, ascii and latin1 and what
are the benefits in real world practice?

voipfc ¼g¹D¡G


> What is the basis of choosing between utf8, ascii and latin1 and what
> are the benefits in real world practice?


it depends on your need, if you only need latin characters, there is no
need to use utf8 as it is waste of storage

howa wrote:
> voipfc ¼g¹D¡G
>
>
> > What is the basis of choosing between utf8, ascii and latin1 and what
> > are the benefits in real world practice?
>
>
> it depends on your need, if you only need latin characters, there is no
> need to use utf8 as it is waste of storage

When I use SQLYog to export a database the UK ¢G signs are changed to
A¢G. Prior to that I was getting ? characters in the export until I
set the SET NAMES in the import file from utf8 to latin1.

After exporting again the ? signs are gone but the A¢G appears.

Any idea what may be causing this?