一个项目中，开发同学使用了MySQL的非严格模式，并把表的日期字段缺省值设置成0，最近有个项目需要从PostgreSQL中访问MySQL的数据，使用的mysql_fdw扩展访问mysql中的数据，一遇到日期为0的数据就报错，又不能去修改MySQL库里的数据，万般无奈之下，分析了mysql_fdw的源码，mysql_fdw的数据转换是通过mysql_query.c中的mysql_convert_to_pg函数实现的，于是用了个比较low的方法，遇到日期为0的数据就改成0001-01-01，够用就好，记录下过程。
/** convert_mysql_to_pg:* Convert MySQL data into PostgreSQL's compatible data types*/Datummysql_convert_to_pg(Oidpgtyp, intpgtypmod, mysql_column*column) { Datumvalue_datum; DatumvalueDatum; regproctypeinput; HeapTupletuple; charstr[MAXDATELEN]; bytea*result; char*text_result=NULL; /* get the type's output function */tuple=SearchSysCache1(TYPEOID, ObjectIdGetDatum(pgtyp)); if (!HeapTupleIsValid(tuple)) elog(ERROR, "cache lookup failed for type%u", pgtyp); typeinput= ((Form_pg_type) GETSTRUCT(tuple))->typinput; ReleaseSysCache(tuple); switch (pgtyp) { /** MySQL gives BIT / BIT(n) data type as decimal value. The only way* to retrieve this value is to use BIN, OCT or HEX function in MySQL,* otherwise mysql client shows the actual decimal value, which could* be a non - printable character. For exmple in MySQL** CREATE TABLE t (b BIT(8));* INSERT INTO t SET b = b'1001';* SELECT BIN(b) FROM t;* +--------+* | BIN(b) |* +--------+* | 1001 |* +--------+** PostgreSQL expacts all binary data to be composed of either '0' or* '1'. MySQL gives value 9 hence PostgreSQL reports error. The* solution is to convert the decimal number into equivalent binary* string.*/caseBYTEAOID: result= (bytea*) palloc(column->length+VARHDRSZ); memcpy(VARDATA(result), VARDATA(column->value), column->length); SET_VARSIZE(result, column->length+VARHDRSZ); returnPointerGetDatum(result); caseBITOID: sprintf(str, "%d", dec_bin(*((int*) column->value))); valueDatum=CStringGetDatum((char*) str); break; caseTEXTOID: text_result= (char*) palloc(column->length+1); memcpy(text_result, (char*) column->value, column->length); text_result[column->length] ='\0'; valueDatum=CStringGetDatum((char*) text_result); break; /*以下是增加的代码*/caseDATEOID: caseTIMESTAMPOID: caseTIMESTAMPTZOID: text_result= (char*) palloc(column->length+1); memcpy(text_result, (char*) column->value, column->length); text_result[column->length] ='\0'; if(strstr(text_result, "0000-")) { memcpy(text_result, "0001-01-01", strlen("0001-01-01")); } valueDatum=CStringGetDatum((char*) text_result); break; default: valueDatum=CStringGetDatum((char* ) column->value); } value_datum=OidFunctionCall3(typeinput, valueDatum, ObjectIdGetDatum(pgtyp), Int32GetDatum(pgtypmod)); if (text_result) pfree(text_result); returnvalue_datum; }
