Description:
For getting collation of char/varchar/text columns , we tried using charsetnr field in MYSQL_FIELD structure on MySQL 5.6. This field should provide the collation id of that field ( column ).
But charsetnr always found to contain the collation value corresponding to either utf8 or utf8mb4 ( 33 or 45 respectively ). This is irrespective of any server charset / db charset / table charset / column charset. Sample example is given below.
1. create table a ( blah char(20) charset gbk ) ;
2. code to get value of charsetnr.
#include <stdio.h>
#include <stdlib.h>
#include <mysql.h>
MYSQL conn;
int main(int argc,char *argv[])
{
// run with username port as arguments
char *tabname="a";
char *db="test1";
char *u;
char *p;
MYSQL_RES *result;
MYSQL_FIELD *field;
u=argv[1]; p=argv[2];
mysql_init(&conn);
if (!mysql_real_connect(&conn, NULL,u,p,db,0,NULL,0) );
result = mysql_list_fields (&conn, tabname, NULL);
field = mysql_fetch_field (result);
printf("Field =%s character set=%d\n", field->name, field->charsetnr);
}
Here we always get a value of filed->charsetnr as 33 or 45 irrespective of server/db/table/column charset. We have tested it on MySQL 5.6.
But when I try query " show full columns from db.table like 'column name'" , it gives me the correct collation for that column which is the third column of the result set returned.
Can you please help in finding the correct collation using the mysql_list_fields() API as well ? We do not want to use the query to find out that.
Thanks,
Ashish
How to repeat:
1. create table a ( blah char(20) charset gbk ) ;
2. code to get value of charsetnr.
#include <stdio.h>
#include <stdlib.h>
#include <mysql.h>
MYSQL conn;
int main(int argc,char *argv[])
{
// run with username port as arguments
char *tabname="a";
char *db="test1";
char *u;
char *p;
MYSQL_RES *result;
MYSQL_FIELD *field;
u=argv[1]; p=argv[2];
mysql_init(&conn);
if (!mysql_real_connect(&conn, NULL,u,p,db,0,NULL,0) );
result = mysql_list_fields (&conn, tabname, NULL);
field = mysql_fetch_field (result);
printf("Field =%s character set=%d\n", field->name, field->charsetnr);
}
Here we always get a value of filed->charsetnr as 33 or 45 irrespective of server/db/table/column charset. We have tested it on MySQL 5.6.
Description: For getting collation of char/varchar/text columns , we tried using charsetnr field in MYSQL_FIELD structure on MySQL 5.6. This field should provide the collation id of that field ( column ). But charsetnr always found to contain the collation value corresponding to either utf8 or utf8mb4 ( 33 or 45 respectively ). This is irrespective of any server charset / db charset / table charset / column charset. Sample example is given below. 1. create table a ( blah char(20) charset gbk ) ; 2. code to get value of charsetnr. #include <stdio.h> #include <stdlib.h> #include <mysql.h> MYSQL conn; int main(int argc,char *argv[]) { // run with username port as arguments char *tabname="a"; char *db="test1"; char *u; char *p; MYSQL_RES *result; MYSQL_FIELD *field; u=argv[1]; p=argv[2]; mysql_init(&conn); if (!mysql_real_connect(&conn, NULL,u,p,db,0,NULL,0) ); result = mysql_list_fields (&conn, tabname, NULL); field = mysql_fetch_field (result); printf("Field =%s character set=%d\n", field->name, field->charsetnr); } Here we always get a value of filed->charsetnr as 33 or 45 irrespective of server/db/table/column charset. We have tested it on MySQL 5.6. But when I try query " show full columns from db.table like 'column name'" , it gives me the correct collation for that column which is the third column of the result set returned. Can you please help in finding the correct collation using the mysql_list_fields() API as well ? We do not want to use the query to find out that. Thanks, Ashish How to repeat: 1. create table a ( blah char(20) charset gbk ) ; 2. code to get value of charsetnr. #include <stdio.h> #include <stdlib.h> #include <mysql.h> MYSQL conn; int main(int argc,char *argv[]) { // run with username port as arguments char *tabname="a"; char *db="test1"; char *u; char *p; MYSQL_RES *result; MYSQL_FIELD *field; u=argv[1]; p=argv[2]; mysql_init(&conn); if (!mysql_real_connect(&conn, NULL,u,p,db,0,NULL,0) ); result = mysql_list_fields (&conn, tabname, NULL); field = mysql_fetch_field (result); printf("Field =%s character set=%d\n", field->name, field->charsetnr); } Here we always get a value of filed->charsetnr as 33 or 45 irrespective of server/db/table/column charset. We have tested it on MySQL 5.6.