LOAD DATA Statement By contrast, nonrestrictive data interpretation uses these rules: •If an input line has too many fields, the extra fields are ignored and the number of warnings is incremented. •If an input line has too few fields, the columns for which input fields are missing are assigned their default values. Default value assignment is described in Section 11.6, “Data Type Default Values” . •Assigning  NULL  (that is,  \N ) to a non- NULL  column results in assignment of the implicit default value for the column data type. Implicit default values are described in Section 11.6, “Data Type Default Values” . •Invalid values produce warnings rather than errors, and are converted to the “closest”  valid value for the column data type. Examples: •A value such as  ’x’  for a numeric column results in conversion to 0. •An out-of-range numeric or temporal value is clipped to the closest endpoint of the range for the column data type. •An invalid value for a  DATETIME ,  DATE , or  TIME  column is inserted as the implicit default value, regardless of the SQL mode  NO_ZERO_DATE  setting. The implicit default is the appropriate “zero” value for the type ( ’0000-00-00 00:00:00’ ,  ’0000-00-00’ , or  ’00:00:00’ ). See Section 11.2, “Date and Time Data Types” . • LOAD DATA  interprets an empty field value differently from a missing field: •For string types, the column is set to the empty string. •For numeric types, the column is set to  0 . •For date and time types, the column is set to the appropriate “zero”  value for the type. See Section 11.2, “Date and Time Data Types” . These are the same values that result if you assign an empty string explicitly to a string, numeric, or date or time type explicitly in an  INSERT  or  UPDATE  statement. TIMESTAMP  columns are set to the current date and time only if there is a  NULL  value for the column (that is,  \N ) and the column is not declared to permit  NULL  values, or if the  TIMESTAMP  column default value is the current timestamp and it is omitted from the field list when a field list is specified. LOAD DATA  regards all input as strings, so you cannot use numeric values for  ENUM  or  SET  columns the way you can with  INSERT  statements. All  ENUM  and  SET  values must be specified as strings. BIT  values cannot be loaded directly using binary notation (for example,  b’011010’ ). To work around this, use the  SET  clause to strip off the leading  b’  and trailing  ’  and perform a base-2 to base-10 conversion so that MySQL loads the values into the  BIT  column properly: shell>  cat /tmp/bit_test.txt b’10’ b’1111111’ shell>  mysql test mysql>  LOAD DATA INFILE ’/tmp/bit_test.txt’ INTO TABLE bit_test (@var1) SET b = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-3), 2, 10) AS UNSIGNED); Query OK, 2 rows affected (0.00 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql>  SELECT BIN(b+0) FROM bit_test; +----------+ | BIN(b+0) | 2649