CODEWITHSUNDEEP
mysqlutf-8
MESSIAH
MESSIAH

Reputation: 19

Non ASCII colum name in mysql

Can I use UTF-8 names in column name on data base? Like example here:

$zapytaj = mysql_query("SELECT * FROM users WHERE `użytkownicy` = '$nazwaużytkownika' ");

This give me error:

Unknown column 'użytkownicy' in 'where clause'

Can someone explain why this is not working?

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+-------------
| Variable_name            | Value
+--------------------------+-------------
| character_set_client     | utf8mb4
| character_set_connection | utf8mb4
| character_set_database   | utf8mb4
| character_set_filesystem | binary
| character_set_results    | utf8mb4
| character_set_server     | latin1
| character_set_system     | utf8

mysql> SELECT COLUMN_NAME, HEX(COLUMN_NAME)
    FROM information_schema.columns WHERE table_name = "so31349641";
+--------------+--------------------------+
| COLUMN_NAME  | HEX(COLUMN_NAME)         |
+--------------+--------------------------+
| id           | 6964                     |
| użytkownicy  | 75C5BC79746B6F776E696379 | -- Note the C5BC for ż
| hasło        | 686173C5826F             | -- and the C582 for ł
+--------------+--------------------------+

If I delete `` from użytkownicy I see this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '�ytkownicy = 'xxx'' at line 1 Maybe PHP file are don't have UTF8 coding? How to check this file in PHPStorm?

!SOLUTION!

If You have this error just change mysql to PDO that should fix Your problem.

Upvotes: 0

Views: 843

Answers (2)

Rick James
Rick James

Reputation: 142208

To answer your stated question, column names are utf8:

mysql> SHOW CREATE TABLE information_schema.columns\G
*************************** 1. row ***************************
       Table: COLUMNS
Create Table: CREATE TEMPORARY TABLE `COLUMNS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',           -- NOTE --
  `ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `COLUMN_DEFAULT` longtext,
  `IS_NULLABLE` varchar(3) NOT NULL DEFAULT '',
  `DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
  `DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
  `COLLATION_NAME` varchar(32) DEFAULT NULL,
  `COLUMN_TYPE` longtext NOT NULL,
  `COLUMN_KEY` varchar(3) NOT NULL DEFAULT '',
  `EXTRA` varchar(30) NOT NULL DEFAULT '',
  `PRIVILEGES` varchar(80) NOT NULL DEFAULT '',
  `COLUMN_COMMENT` varchar(1024) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8

To get to the root of the implied question ("Why does the query fail"), let's see

SHOW VARIABLES LIKE 'character%';

Edit

Well, something un-obvious going on. This works for me:

mysql> create table so31349641 (
    id int(11) NOT NULL AUTO_INCREMENT, 
    użytkownicy varchar(24) NOT NULL, 
    hasło varchar(24) NOT NULL, PRIMARY KEY (id)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysql> INSERT INTO so31349641 VALUES (1, 'a', 'b');

mysql> SELECT * FROM so31349641 WHERE użytkownicy = 'a';
+----+--------------+--------+
| id | użytkownicy  | hasło  |
+----+--------------+--------+
|  1 | a            | b      |
+----+--------------+--------+

This seems ordinary:

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+-------------
| Variable_name            | Value
+--------------------------+-------------
| character_set_client     | utf8
| character_set_connection | utf8
| character_set_database   | latin1
| character_set_filesystem | binary
| character_set_results    | utf8
| character_set_server     | latin1
| character_set_system     | utf8

Looking in the IS:

mysql> SELECT COLUMN_NAME, HEX(COLUMN_NAME)
    FROM information_schema.columns WHERE table_name = "so31349641";
+--------------+--------------------------+
| COLUMN_NAME  | HEX(COLUMN_NAME)         |
+--------------+--------------------------+
| id           | 6964                     |
| użytkownicy  | 75C5BC79746B6F776E696379 | -- Note the C5BC
| hasło        | 686173C5826F             | -- and the C582 for ł
+--------------+--------------------------+

That is as I would expect it.

My char% values are different than yours, but I think we are both "OK" for this situation.

Try a SELECT on the information_schema similar to what I did.

Next, what is your client? PHP? Something else? Perhaps the encoding is incorrect in the client.

(Rather than trying to use HTML tags in a Comment, Edit your original question with the added info.)

Upvotes: 0

Snappawapa
Snappawapa

Reputation: 2012

It looks like UTF-8 in SQL is not default, but tables/databases can be changed to be so.

Some potentially helpful links:

The mySQL documentation on charsets: https://dev.mysql.com/doc/refman/5.0/en/charset.html

A SO question on determining the charset: determining the character set of a table / database?

On changing the charset: http://makandracards.com/makandra/2529-show-and-change-mysql-default-character-set

Hope this helps.

Upvotes: 0

Related Questions