Mysql - What data types should be in quotes?

Asked

Viewed 1,572 times

4

I’m developing a script for backup/dump from my database via PHP, but when saving the file, I need to put, or not, quotes according to the type of the column.

So far so good! But what types of data should be in quotes?

Example:

  • Whole: 123 - INT()

  • String: '123' - VARCHAR()

I need a complete list of data types and whether they should be in quotes or not, and I haven’t found a satisfactory list in my searches.

A more detailed example of where I’m going:

mysql> explain teste;
+-----------+--------------+------+-----+---------------------+----------------+
| Field     | Type         | Null | Key | Default             | Extra          |
+-----------+--------------+------+-----+---------------------+----------------+
| controle  | int(10)      | NO   | PRI | NULL                | auto_increment |
| inteiro   | int(1)       | NO   |     | 0                   |                |
| flutuante | float        | NO   |     | 0.1                 |                |
| string    | varchar(150) | NO   |     | fubá                |                |
| datahora  | datetime     | NO   |     | 0000-00-00 00:00:00 |                |
+-----------+--------------+------+-----+---------------------+----------------+

In the final archive (dump), the rows to insert the data from this table looks like this:

INSERT INTO `teste` VALUES (1,0,0.1,'fubá','0000-00-00 00:00:00');

Notice that the guys INT and FLOAT are not in quotes. Already the types VARCHAR and DATETIME are in quotes.

mysql

1 answer

4


Data of the numerical type don’t need quotation marks, date/time and string types (string) need be defined with quotation marks!

Among the main:

QUOTE-FREE (Numerical): TINYINT, SMALLINT, MEDIUMINT, INT, INTEGER, BIGINT, FLOAT, DOUBLE, DOUBLE PRECISION, REAL, DECIMAL and NUMERIC.

QUOTATION MARKS (Date/time and chain): DATE, DATETIME, TIMESTAMP, TIME, YEAR, CHAR, VARCHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT, ENUM and SET.

Has everyone in Mysql documentation (in English).

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.