|
I create a table and a VIEW on in:
CREATE OR REPLACE TABLE t1 (
|
i1 INT NOT NULL DEFAULT 0,
|
i3 BIGINT UNSIGNED NOT NULL DEFAULT 0xFFFFFFFFFFFFFFFF,
|
s1 VARCHAR(10) CHARACTER SET latin1 NOT NULL DEFAULT 's1def',
|
d1 DECIMAL(31,1) NOT NULL DEFAULT 111111111122222222223333333333.9,
|
t1 DATETIME(6) NOT NULL DEFAULT '2001-01-01 10:20:30.123456',
|
e1 ENUM('a','b') NOT NULL DEFAULT 'a'
|
);
|
CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1;
|
Now I run these two queries:
SHOW FIELDS IN t1;
|
SHOW FIELDS IN v1;
|
Both return the same result:
+-------+---------------------+------+-----+----------------------------------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------+---------------------+------+-----+----------------------------------+-------+
|
| i1 | int(11) | NO | | 0 | |
|
| i3 | bigint(20) unsigned | NO | | 18446744073709551615 | |
|
| s1 | varchar(10) | NO | | s1def | |
|
| d1 | decimal(31,1) | NO | | 111111111122222222223333333333.9 | |
|
| t1 | datetime(6) | NO | | 2001-01-01 10:20:30.123456 | |
|
| e1 | enum('a','b') | NO | | a | |
|
+-------+---------------------+------+-----+----------------------------------+-------+
|
So far so good.
Now I test mysql_list_fields() for t1 and v1.
This is the test client program:
#include <stdio.h>
|
#include <mysql/mysql.h>
|
|
static int list_fields(MYSQL *mysql, const char *table_name)
|
{
|
int i;
|
MYSQL_RES *res;
|
unsigned int field_cnt;
|
|
res= mysql_list_fields(mysql, table_name, "%");
|
if (!res)
|
{
|
printf("Error: %s\n", mysql_error(mysql));
|
return 1;
|
}
|
field_cnt= mysql_num_fields(res);
|
printf("Number of columns: %d\n", field_cnt);
|
|
for (i=0; i < field_cnt; ++i)
|
{
|
/* col describes i-th column of the table */
|
MYSQL_FIELD *col = mysql_fetch_field_direct(res, i);
|
printf ("Column[%d] name=%s type=%d charsetnr=%d def='%s'\n",
|
i, col->name, col->type, col->charsetnr, col->def);
|
}
|
mysql_free_result(res);
|
return 0;
|
}
|
|
int main(int ac, char **av)
|
{
|
MYSQL mysql;
|
mysql_init(&mysql);
|
if (!mysql_real_connect(&mysql, "localhost", "root", NULL, "test",
|
3306, "/tmp/mysql.sock", 0))
|
{
|
printf("Error: could not connect\n");
|
return 1;
|
}
|
mysql_set_character_set(&mysql, "utf8");
|
list_fields(&mysql, av[1] ? av[1] : "t1");
|
mysql_close(&mysql);
|
return 0;
|
}
|
I compile the above program and run:
Number of columns: 6
|
Column[0] name=i1 type=3 length=11 charsetnr=63 def='0'
|
Column[1] name=i3 type=8 length=20 charsetnr=63 def='18446744073709551615'
|
Column[2] name=s1 type=253 length=30 charsetnr=33 def='s1def'
|
Column[3] name=d1 type=246 length=33 charsetnr=63 def='111111111122222222223333333333.9'
|
Column[4] name=t1 type=12 length=26 charsetnr=63 def='2001-01-01 10:20:30.123456'
|
Column[5] name=e1 type=254 length=3 charsetnr=33 def='a'
|
Number of columns: 6
|
Column[0] name=i1 type=3 length=11 charsetnr=63 def='0'
|
Column[1] name=i3 type=8 length=20 charsetnr=63 def='18446744073709552000'
|
Column[2] name=s1 type=253 length=30 charsetnr=33 def='0'
|
Column[3] name=d1 type=246 length=33 charsetnr=63 def='111111111122222220000000000000'
|
Column[4] name=t1 type=12 length=26 charsetnr=63 def='20010101102030'
|
Column[5] name=e1 type=254 length=3 charsetnr=33 def='1'
|
All default values in the table are fine.
Obvervations about default values in the view:
- i1 is fine
- i3 lost precision (the 4 rightmost digits are wrong)
- s1 returned 0 instead of s1def
- d1 lost precision and lost fractional digits
- t1 lost datetime formatting and lost fractional digits
- e1 lost the original string value and returned its numeric equivalent
|