Specifying UTF8 Charset

When creating a database when I try to specify a utf8 charset I am not able to. Can someone help me please?

I guess you're missing a dropdown or something in the CREATE-Database dialog. As it's not implemented yet, you will have to create the database via the query memo. Write a query and press F9:

CREATE DATABASE `test` DEFAULT CHARACTER SET latin1

Remember that specifying a character set requires a 4.1 (or newer) server.

I was able to run that query and I changed the character set to UTF8 and it executed fine. Would you please look at this code from an exported database. I attempted to copy an existing database and in doing so specify a UTF8 charset, but I get an error in Heidisql. Again your sql query worked fine but when I run this with a modification to the character set I get an error.


# HeidiSQL Dump
#
# --------------------------------------------------------
# Host: localhost
# Database: montparnasse
# Server version: 5.0.27-community-nt
# Server OS: Win32
# max_allowed_packet: 1048576
# HeidiSQL version: 3.0 RC4 Revision: 334
# --------------------------------------------------------

/*!40100 SET CHARACTER SET latin1;*/


#
# Database structure for database 'montparnasse'
#

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `monty` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE monty;


#
# Table structure for table 'gallery'
#

CREATE TABLE /*!32312 IF NOT EXISTS*/ `gallery` (
`id` int(11) NOT NULL auto_increment,
`image` varchar(30) NOT NULL default '',
`category` varchar(20) NOT NULL default '',
`status` varchar(20) NOT NULL default '',
`title1` varchar(30) NOT NULL default '',
`desc1` text NOT NULL,
`title2` varchar(30) default NULL,
`desc2` text,
`title3` varchar(30) default NULL,
`desc3` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I was able to run that query and I changed the character set to UTF8 and it executed fine.

Fine!

I attempted to copy an existing database and in doing so specify a UTF8 charset, but I get an error in Heidisql. Again your sql query worked fine but when I run this with a modification to the character set I get an error.

Which error? Please post the complete message here.

I know this is an old post, but I just have to reply. The above won't work, because latin1 is not UTF-8! The latin1 character set corresponds to cp1252 West European encoding. This will not work with Asian characters or other Unicode characters (I live in Thailand, so I run into this problem often...)

To my knowledge HeidiSQL does not support Unicode/UTF-8 at this time.

Actually it should be fairly easy to change that and make Unicode/UTF-8 encoding the default. I think the Delphi components support UTF-8 natively. When a session is opened with HeidiSQL, HeidiSQL sends a "SET names latin1". For the connection to be able to handle Unicode, this should be changed to "SET names 'utf8'" (notice the missing '-' between 'utf' and '8' - that's a MySQL quirk). The DDL to create a proper Unicode table is:

CREATE TABLE `table_name` (
...
) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Note that the standard Unicode collation is utf8_unicode_ci, not utf8_general_ci (the MySQL default value). If you are dealing with existing tables, you also might also need to change the CHARSET/COLLATE properties for each text column (of type CHAR, VARCHAR, TEXT, etc.), since MySQL allows you to set character set encoding individually per column.

Cheers, SQareLogic