Help with data type in MySQL

Navigation
2

This is the database script, everything is inserted correctly except the part of the precio , when viewing it in a php file (in which all the data is in a table) are presented as: 

31.84
38.74

and 

32.51

that is, the last number that is 0 is omitted in the three cases, I need to be able to see the figures as I inserted them, but I do not know if it is due to the type of data, or because of an error when inserting them. 

CREATE DATABASE IF NOT EXISTS 'catalogo_audi' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE 'catalogo_audi';
CREATE TABLE IF NOT EXISTS'audi'(
  'idAudi' int,
  'modelo' varchar(30) ,
  'imagen' varchar(30) ,

  'descripcion' TEXT,
  'precio' float

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO 'audi' ('idAudi', 'modelo','imagen', 'descripcion', 'precio') VALUES
(1, 'Audi A4','P01.jpg','Tipo de motor: Diésel, 4 cilindros en línea, 16 válvulas, TDI con turbo TGV e intercooler, DOHC
Sistema de inyección common-rail, 1.800 bares con inyectores piezoeléctricos, inyección directa con inyectores de 8 orificios, turboalimentación VTG con refrigeración del aire de sobrealimentación; canales de admisión de turbulencia y tangenciales, canal de turbulencia regulado; Bosch EDC; regulación del caudal y del inicio de inyección, control de presión de sobrealimentación y de recircirculación de gases de escape a baja temperatura.
Velocidad máxima: 215 km/h
Aceleración: 9,5 s',31.840),
(2, 'AUDI A6','P02.jpg','Tipo de motor: 4 cilindros en línea, inyección directa TFSI - 16 válvulas
Sistema de gestión del motor totalmente electrónico. Bosch MED 9.1; inyección directa, regulación lambda adaptable, encendido con distribución estática de alta tensión, regulación de picado selectiva y adaptable.
Velocidad máxima: 228 km/h
Aceleración: 8,2 s',38.740),
(3, 'Audi A8','P03.jpg','Tipo de motor: 8 cilindros en V, 32 válvulas
Sistema de gestión del motor totalmente electrónico, Bosch MED 17.1.1; inyección directa FSI con 120 bar de presión del sistema.
Velocidad máxima: 250 km/h
Aceleración: 6,9 s',0),
(4, 'aUDI TT','P04.jpg','Tipo de motor: 4 cilindros en línea, inyección directa TFSI - 16 válvulas
Gestión electrónica con control electrónico del acelerador, inyección directa; regulación lambda adaptable; encendido con distribución estática de alta tensión, regulación de picado selectiva y adaptable por cilindro; medición de la masa de aire.
Velocidad máxima: 226 km/h
Aceleración: 7,2 s',32.510);
    
asked by ale 12.01.2018 в 13:49
source

1 answer

1

To save prices MySQL recommends the data type DECIMAL .

It is what can be deduced by reading the documentation:

  

The DECIMAL and NUMERIC types store numeric data values   exact. These types are used when it is important to preserve the   Accurate accuracy, for example, with monetary data.

     

MySQL stores values DECIMAL in binary format. See the Section   12.21, "Precision Mathematics" .

     

In a column declaration DECIMAL , precision and scale   they can (and generally should) be specified; for example: 

salario DECIMAL (5,2)
     

In this example, 5 is the precision and 2 is the scale. The precision   represents the number of significant digits that are stored for   values, and the scale represents the number of digits that can be   store after the decimal point.

     

Standard SQL requires that DECIMAL (5,2) can store any   value with five digits and two decimals, so the values that are   can store in the range of the salary column of -999.99 to   999.99.

     

In standard SQL, the syntax DECIMAL (M) is equivalent to DECIMAL (M, 0) . Similarly, the DECIMAL syntax is equivalent to   DECIMAL (M, 0) , where the implementation allows to decide the value of    M MySQL supports both variant forms of syntax DECIMAL . The   default value of M is 10 .

     

If the scale is 0 , the DECIMAL values do not contain a decimal point   or fractional part.

     

The maximum number of digits for DECIMAL is 65 , but the actual range   for a given DECIMAL column may be restricted by the   precision or scale of a given column. When to said column   it is assigned a value with more digits after the decimal point that   those allowed by the specified scale, the value becomes that   scale. (The precise behavior is system specific   operative, but generally the effect is truncation to the number   allowed of digits).

     

MySQL Documentation

Conclusion

If possible, change the data type from float to decimal , applying a security and data verification policy if the table in question is already in production.

Why not use float ?

Because float values are vulnerable to rounding errors and also depend on many other factors, so accuracy is at stake.

The documentation says the following:

  

Because the floating point values are approximate and do not   store as accurate values, attempts to treat them as accurate   in the comparisons they can generate problems. They are also subject to   platform dependencies or implementation. to get more   For information, see the Section B.5.4.8, "Problems with values   floating point ".

     

float in the MySQL documentation

    
answered by 12.01.2018 в 14:02
Multiply two variables that contain ranges Generate .TXT file with Stored Procedure