Rename table column not working

[siabanie]

Hi all,

I like to rename my table's column.
My table name is: utf8_Alter
Field: Branch

In the Branch field I have few columns name

I tried to change the name with this query: But it didn't work.

ALTER TABLE `utf8_Alter` CHANGE `ゴルダーズグリーン` `ーン ノースロンドン` varchar( 64 )

MySQL said: Documentation
#1054 - Unknown column 'ゴルダーズグリーン' in 'utf8_Alter' 

The Collation in phphMyAdmin is :utf8_general_ci

Can anyone assist me what I did wrong here?
Thanks in advance.

[bradgrafelman]

Did you try executing the query:

SET NAMES 'utf8';

before sending Unicode-encoded characters?

[siabanie]

bradgrafelman;10985786 wrote:

Did you try executing the query:

SET NAMES 'utf8';

before sending Unicode-encoded characters?

I am doing it in phpMyAdmin SQL - How can I run that query by SET them first? As you know that phpMyAdmin only purposed for writing a query not sure if I can do e.g

SET NAMES 'utf8';
ALTER TABLE `utf8_Alter` CHANGE `ゴルダーズグリーン` `ーン ノースロンドン` varchar( 64 )

And what is NAMES here? is it the field name?

[bradgrafelman]

siabanie;10985792 wrote:

As you know that phpMyAdmin only purposed for writing a query

Er... what? phpMyAdmin is designed to help manage a MySQL server by providing a graphical interface. It's used for a lot more than just "writing a query."

siabanie;10985792 wrote:

not sure if I can do e.g

SET NAMES 'utf8';
ALTER TABLE `utf8_Alter` CHANGE `ゴルダーズグリーン` `ーン ノースロンドン` varchar( 64 )

Why couldn't you? (Hint: You can.)

However, I would assume phpMyAdmin has its own character encoding setting as well (not sure since I don't have access to PMA at the moment).

siabanie;10985792 wrote:

And what is NAMES here? is it the field name?

Nope, it's the keyword that follows "SET" in the SET query syntax. From the Unicode Support page in the MySQL manual:

MySQL Manual wrote:

Client applications that need to communicate with the server using Unicode should set the client character set accordingly; for example, by issuing a SET NAMES 'utf8' statement.

[siabanie]

Well I have tried doing that but it gave me an error:

SQL query:

SET NAMES 'utf8';

ALTER TABLE `utf8_Alter` CHANGE `ゴルダーズグリーン` `ーン ノースロンドン` varchar( 64 ) ;

MySQL said: Documentation
#1054 - Unknown column 'ゴルダーズグリーン' in 'utf8_Alter' 

In phpMyadmin the Collation is SET as utf8_general_ci (multilingual) which I think is correct.

Any idea?

[johanafm]

siabanie;10985782 wrote:

In the Branch field I have few columns name

No, you don't. Column and field are two different names for the exact same thing. You can say that your table has a few fields or that your table has a few columns and it means the exact same thing.
But you can NOT say that a column has many columns, or since these names are interchangeable, that a field has many fields. And once again, since they are interchangeable, you can NOT say that a field has many columns or a column has many fields either.

siabanie;10985782 wrote:

My table name is: utf8_Alter
Field: Branch

ALTER TABLE `utf8_Alter` CHANGE `ゴルダーズグリーン` `ーン ノースロンドン` varchar( 64 )

Well, is the column you would like to change called "Branch" or is it called "ゴルダーズグリーン"? If it is called "Branch", then you would write

ALTER TABLE `utf8_Alter` CHANGE Branch ...

siabanie;10985782 wrote:

MySQL said: Documentation
#1054 - Unknown column 'ゴルダーズグリーン' in 'utf8_Alter'

The reason you get 'ゴルダーズグリーン' rather than ゴルダーズグリーン is because phpMyAdmin doesn't handle your character set properly. I don't know if there's a setting for this, but personally I'd ditch phpMyAdmin and use mySQLWorkbench. Even if my only option was the CLI, I'd use that instead of phpMyAdmin. CLI really does work fine for a lot of things.

siabanie;10985782 wrote:

The Collation in phphMyAdmin is :utf8_general_ci

No, it's not. phpMyAdmin does not have a collation, as far as I know. If it does not allow you to reorder your result sets after they are sent to the database. The collation deal with how to order things: does 'e' come before or after 'é', or are they treated the same? For example, in swedish 'o' and 'ö' are two distinct characters, and utf8_swedish_ci means that 'ö' is treated as the last letter of the alphabet, whereas in utf8_general_ci 'o' and 'ö' are treated as the same character. But wether phpMyAdmin does or doesn't have a collation doesn't matter, because it is still unrelated to the issue at hand.

The word you are looking for is character set (utf-8), but since you are talking about your DBMS general setting, your DB schema default setting, a specific table's default setting or the actual setting of a column, it is NOT phpMyAdmin that "has this setting", it is:
1. the column "Branch"
2. If no character set was specified for "Branch", use the default setting from "utf8_Alter"
3. If no character set was specified for "utf8_Alter", use the default setting from your DB Schema
4. If no character set was specified for your db schema, use the default setting for your DBMS.

And since you somewhere mentioned the character set for table "utf8_Alter" (or if it was for column "Branch", you should also be aware that you are still looking in the wrong place.

The place that matters when it comes to column names is the schema "information_schema", table "COLUMNS", field "COLUMN_NAME". If this field uses utf-8 (and it's entirely possible that it always does - I don't know), then you can use utf-8 characters for column names. If this field doesn't use utf-8, then you can't use utf-8 characters for column names, no matter what character set the specific column uses - that only relates to the character set for the data saved in this column.

However, as always when it comes to character encodings, it's important that the same encoding is used all the way, or that the proper translations from one encoding to another takes place. As such, you have to know what character set is used for a specific column, what character set is used by phpMyAdmin, and thereafter use this knowledge to properly configure your database settings for character_set_client, character_set_connection and character_set_results. You specify the character set to use for all 3 of these using "SET NAMES ...".

[siabanie]

johanafm;10985864 wrote:

Well, is the column you would like to change called "Branch" or is it called "ゴルダーズグリーン"? If it is called "Branch", then you would write

ALTER TABLE `utf8_Alter` CHANGE Branch ...

Thanks for your explanation johanafm, sorry to be confused between column and field.

Well let me try to describe bit detail (instead of using different character I use alphabet to explain) and I hope this will helps.

I have table called: utf8_Alter
One of the fields are called: Branch

In the field branch I have values:

A
B
C

and each of them have a record.

Now I like to rename the A to D.

I tried to run the query as:

ALTER TABLE `utf8_Alter` CHANGE `A` `D` varchar( 64 )

But it did not work maybe it is because the phpmyadmin did not recognise the character or maybe the query that I tried to request is not correct?

Any idea?

[Derokorian]

UPDATE utf8_Alter SET branch='D' WHERE branch='A'

[johanafm]

And the reason is that ALTER TABLE is used to changed the table STRUCTURE, such as what fields it contains, what fields are indexed, what datatypes are used etc (just like CREATE TABLE is used to create a table in the first place).

To manipulate data stored in a table, rather than the table structure, you use SELECT to retrieve data, INSERT to store data, UPDATE to modify data and DELETE to remove data (while you'd use DROP TABLE to get rid of the actual table - which of course would also get rid of the table's content).

See SQL Syntax (MySQL 5.5) for more information about this. The things related to table structure are in the section called data definition statements and the things related to table contents is in the section called data manipulation statements