Export Table Data With Null Fields

[matrix]

I'm using SQLyog v5.22a and I have a problem when I use “Export table data” (from table menu) or export from a view “Result” (data reading from a query) and saving as SQL. If a table have fields with null values, in the sql file they are writing like '' (empty string). This is a problem when I want to import this sql file into other database in the same table with the same structure. The import insert empty strings (this is incorrect) and not null values.

Thanks.

[peterlaursen]

This is fixed in 5.23 and 5.24.  Please upgrade!

BTW:  You should consider the difference of 'backup as SQL dump' and 'Export as SQL'.  Use 'backup as SQL dump' if you want to make a backup and want all table details etc.  'Export as SQL' is not intended for backup purposes.  It basically only 'embeds' data in INSERT statements.

[matrix]

peterlaursen wrote on Mar 8 2007, 09:13 AM:

This is fixed in 5.23 and 5.24. Please upgrade!

BTW: You should consider the difference of 'backup as SQL dump' and 'Export as SQL'. Use 'backup as SQL dump' if you want to make a backup and want all table details etc. 'Export as SQL' is not intended for backup purposes. It basically only 'embeds' data in INSERT statements.

Now I have a 5.24 version and work fine. Thank you very much.

Other question: in an export as sql, if you select “structure and data”, the structure of table is not correct because not print the atributtes of fields like permit null. I don't need this function but I think it is bad. I can use the “backup as sql dump” but is a good idea to export from result of query and that export include the “create table”, I think.

[peterlaursen]

Just for your info: 'Export as SQL' is mainly intended to be used from the RESULT tab, 'Backup as SQL dump' from the DATA tab.  'Export as SQL' was introduced lately due to user requests that wanted an option to export a RESULT (not a TABLE) as (some kind of) SQL.  Of course there cannot be any additional information like charset, engine, indexes, comments etc. here as there is no such information in the RESULT.

We will (have to) make this more clear with the next release, I think!

But the issue with NULL was a bug, of course.

I do not understand you when you write “because not print the atributtes of fields like permit null”.  

Please post a detailed example? screenshots, dumps are welcome.

[matrix]

peterlaursen wrote on Mar 8 2007, 01:26 PM:

Just for your info: 'Export as SQL' is mainly intended to be used from the RESULT tab, 'Backup as SQL dump' from the DATA tab. 'Export as SQL' was introduced lately due to user requests that wanted an option to export a RESULT (not a TABLE) as (some kind of) SQL. Of course there cannot be any additional information like charset, engine, indexes, comments etc. here as there is no such information in the RESULT.

We will (have to) make this more clear with the next release, I think!

But the issue with NULL was a bug, of course.

I do not understand you when you write “because not print the atributtes of fields like permit null”.

Please post a detailed example? screenshots, dumps are welcome.

Sorry, my english is very bad. I will try to explain it.

if you use “Backup as SQL dump” and select “structure and data”, the “create” show like this:

CREATE TABLE `guf_mensajes` (

`COD_MENSAJE` decimal(10,0) NOT NULL default '0',

`COD_IDIOMA` decimal(10,0) NOT NULL default '0',

`COD_PORTAL` decimal(10,0) NOT NULL default '0',

`TEXTO` text,

PRIMARY KEY (`COD_MENSAJE`,`COD_IDIOMA`,`COD_PORTAL`),

KEY `MENSAJES_PORTALES_FK` (`COD_PORTAL`),

KEY `MENSAJES_IDIOMAS_FK` (`COD_IDIOMA`),

CONSTRAINT `guf_mensajes_ibfk_1` FOREIGN KEY (`COD_PORTAL`) REFERENCES `guf_portales` (`COD_PORTAL`) ON DELETE CASCADE,

CONSTRAINT `guf_mensajes_ibfk_2` FOREIGN KEY (`COD_IDIOMA`) REFERENCES `guf_idiomas` (`COD_IDIOMA`) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

but, if you use “Export as SQL” from a “result tab” and select “structure and data”, the “create” show like this:

create table `guf_mensajes` (

`COD_MENSAJE` decimal (11),

`COD_IDIOMA` decimal (11),

`COD_PORTAL` decimal (11),

`TEXTO` blob

);

I know that the “Export as SQL” is not for backup, but if you can use it to create a file with the “create table” and a few records of table data (from a result of query), maybe it is a good funcionality. I don't need this funcionality now, but maybe in the future.

Don't worry. My problem with “null values” in data it's ok now with v5.24 version.

Thanks.

[Author]

Posts