The decimal value in the mysql stored procedure is automatically rounded to this pitfall, mysqldecimal

The decimal value in the mysql stored procedure is automatically rounded to this pitfall, mysqldecimal

Let me say a few other things: I use Microsoft for my daily work. The SQL service stored procedure is very powerful and I am also very used to using the stored procedure. It was related to MySQL five years ago. For some reason, the company required an open-source free database. In many cases, mysql programmers do not use stored procedures. In addition to debugging troubles, there are other minor problems. To be honest, MySQL has developed rapidly over the years, but the gap with SQL SERVICE is still very large. All right, let alone nonsense.

 

First, describe my scenarios

Assume that we have a t_order table with the following structure:

Field name	Type	Description
Id	Int (11) <auto_increment>	Id, auto-increment, primary key
OrderNo	Varchar (20)	Order Number, unique constraint
Price	Decimal (10, 2)	Price

 

We should not tangle the integrity of this table, nor do we need to worry about why the price does not directly use int to represent the amount of points. Now let's write a storage process for generating orders:

Create procedure 'P _ order_create '(IN' _ OrderNo 'tinytext, IN' _ price' decimal, OUT '_ res' int) BEGIN/* purpose: Generate order parameters: IN '_ OrderNo' tinytext, IN '_ price' decimal _ res: execution result. 0 indicates failure. The order id */insert into t_order ('orderno ', 'price') VALUES (_ OrderNo, _ price); select @ Identity INTO _ res; END;

When we call the API, we input a decimal number to the price, for example:

CALL p_order_create('abc',5.8,@res)

 

We will find that the price in the t_order table is 6 and rounded down ...., to be honest, I have always used int to represent the smallest unit of money (the smallest unit of the current business). For example, if RMB is one dollar, I will store an integer of 100, so I have never noticed the MySQL pitfalls. Of course, the solution to this pitfall is also very simple. We only need to specify the precision of decimal when declaring it, for example, decimal (), that is, the stored procedure can be changed:

Create procedure 'P _ order_create '(IN' _ OrderNo 'tinytext, IN' _ price' decimal (), OUT '_ res' int) BEGIN/* usage: order generation parameter: IN '_ orderno' tinytext, IN' _ price' decimal _ res: execution result. 0 indicates failure, return the order id */insert into t_order ('orderno', 'price') VALUES (_ orderNo, _ price); select @ Identity INTO _ res; END;

 

 

This trap is recorded here. It is also a reminder to you!