Rudy,
there is an explanation to this behaviour:
on escaped data you could un-escape almost everything, one can escape almost any
character if wanted.. .. so \\ gets \ and \* gets *, \% gets % and \N gets... N.
mysql> select "\A\B\C\J\N";
+-----+
|ABCJN|
+-----+
|ABCJN|
+-----+
1 row in set (0.03 sec)
In your situation the field type is char(1), and this 'bug' typically affects this
field. why ? you'll have to ask one of the developers.
Fix:
Guess the fix would be to remove the word 'optionally' from 'optionally enclosed by
'"' '.
regards,
rene
On Fri, 24 Aug 2001 15:36:41 +0200
Rudy Metzger <[EMAIL PROTECTED]> wrote:
> Dear all,
>
> I have the following problem with load data... Please excuse layout, but the
> linewidth is just too short....
>
> // ---------------------------------------
> // Here is my table definition....
> // ---------------------------------------
>
> mysql> desc customer;
> +-------------------+----------------------+------+-----+---------+-------+-
> --------------------------------+
> | Field | Type | Null | Key | Default | Extra |
> Privileges |
> +-------------------+----------------------+------+-----+---------+-------+-
> --------------------------------+
> | SK | int(10) unsigned | | PRI | 0 | |
> select,insert,update,references |
> | ShopCode | char(3) | | | | |
> select,insert,update,references |
> | PasNr | smallint(5) unsigned | | | 0 | |
> select,insert,update,references |
> | OriginalShopCode | char(3) | YES | | NULL | |
> select,insert,update,references |
> | OriginalPasNr | smallint(6) | YES | | NULL | |
> select,insert,update,references |
> | Titel | varchar(15) | YES | | NULL | |
> select,insert,update,references |
> | FirstName | varchar(10) | YES | | NULL | |
> select,insert,update,references |
> | Voorvoegsel | varchar(10) | YES | | NULL | |
> select,insert,update,references |
> | SurName | varchar(20) | YES | | NULL | |
> select,insert,update,references |
> | Street | varchar(25) | YES | | NULL | |
> select,insert,update,references |
> | StreetNumber | varchar(8) | YES | | NULL | |
> select,insert,update,references |
> | ZIP | varchar(10) | YES | | NULL | |
> select,insert,update,references |
> | City | varchar(35) | YES | | NULL | |
> select,insert,update,references |
> | Telephone | varchar(15) | YES | | NULL | |
> select,insert,update,references |
> | Gender | char(1) | YES | | NULL | |
> select,insert,update,references |
> | BirthDate | date | YES | | NULL | |
> select,insert,update,references |
> | AirMiles | char(1) | YES | | NULL | |
> select,insert,update,references |
> | AirMilesTeller | smallint(6) | YES | | NULL | |
> select,insert,update,references |
> | Lid | char(1) | YES | | NULL | |
> select,insert,update,references |
> | JaarWeekNrMutatie | smallint(6) | YES | | NULL | |
> select,insert,update,references |
> | WeekNrMutatie | tinyint(4) | YES | | NULL | |
> select,insert,update,references |
> | DatumMutatie | datetime | YES | | NULL | |
> select,insert,update,references |
> | EmailAddress | varchar(40) | YES | | NULL | |
> select,insert,update,references |
> | Mailing | char(1) | YES | | NULL | |
> select,insert,update,references |
> | PostCodeCheck | char(1) | YES | | NULL | |
> select,insert,update,references |
> +-------------------+----------------------+------+-----+---------+-------+-
> --------------------------------+
> 25 rows in set (0.01 sec)
>
> // -------------------------------------------------------------------------
> // this is the file i am trying to load (all one line in reality...)
> // -------------------------------------------------------------------------
>
> 1 "01 " 100 \N \N "MEVR. " "B.G. "
> \N "VEEN V.D. **** " "****** ********* " "10 "
> "****** " "********** " "023-5440320 "
> "V" 1956-08-30 00:00:00 "J" 46 "A" 2000 31
> 2000-08-05 00:00:00 \N "J" \N
>
> //
> ----------------------------------------------------------------------------
> // the syntax for the load data.....
> //
> ----------------------------------------------------------------------------
>
> load data infile '/home/rmetzger/mysql/import/klant.txt' into table customer
>
> fields terminated by '\t' optionally enclosed by '"' escaped by '\\';
>
> //
> ----------------------------------------------------------------------------
> --
> // and this is what i end up with ....
> //
> ----------------------------------------------------------------------------
> -
>
> mysql> select * from customer where sk = 1 and postcodecheck = 'N';
> +----+----------+-------+------------------+---------------+-------+--------
> ---+-------------+----------------+------------------+--------------+-------
> -+------------+-------------+--------+------------+----------+--------------
> --+------+-------------------+---------------+---------------------+--------
> ------+---------+---------------+
> | SK | ShopCode | PasNr | OriginalShopCode | OriginalPasNr | Titel |
> FirstName | Voorvoegsel | SurName | Street | StreetNumber |
> ZIP | City | Telephone | Gender | BirthDate | AirMiles |
> AirMilesTeller | Lid | JaarWeekNrMutatie | WeekNrMutatie | DatumMutatie
> | EmailAddress | Mailing | PostCodeCheck |
> +----+----------+-------+------------------+---------------+-------+--------
> ---+-------------+----------------+------------------+--------------+-------
> -+------------+-------------+--------+------------+----------+--------------
> --+------+-------------------+---------------+---------------------+--------
> ------+---------+---------------+
> | 1 | 01 | 100 | NULL | NULL | MEVR. | B.G.
> | NULL | VEEN V.D. **** | ****** ********* | 10 | ****** |
> ********** | 023-5440320 | V | 1956-08-30 | J | 46 |
> A | 2000 | 31 | 2000-08-05 00:00:00 | NULL
> | J | N |
> +----+----------+-------+------------------+---------------+-------+--------
> ---+-------------+----------------+------------------+--------------+-------
> -+------------+-------------+--------+------------+----------+--------------
> --+------+-------------------+---------------+---------------------+--------
> ------+---------+---------------+
> 1 row in set (0.03 sec)
>
> // ------------------------------------------------------------------
> Now my question:
> Why is the last field "PostCodeCheck" now suddenly 'N' and not NULL as
> expected???
>
> Anyone any ideas? Bug? Workaround?
>
> I am greateful for any information!!!
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
