>>>> 2014/02/11 18:14 -0500, Larry Martell >>>>
set LIMIT = sign(LIMIT) * 100 * floor(0.000001 + (sign(LIMIT) * LIMIT
* ratio/100)
<<<<<<<<
The function TRUNCATE can be useful here:
set LIMIT = TRUNCATE(LIMIT * ratio + 0.000001 * sign(LIMIT), -2)
, if it works as advertized. In any case,
ABS(LIMIT) = sign(LIMIT) * LIMIT
.
As for limiting the value, see this (clipping can be useful to you):
11.2.6. Out-of-Range and Overflow Handling
When MySQL stores a value in a numeric column that is outside the permissible
range of the column data type, the result depends on the SQL mode in effect at
the time:
* If strict SQL mode is enabled, MySQL rejects the out-of-range value with
an error, and the insert fails, in accordance with the SQL standard.
* If no restrictive modes are enabled, MySQL clips the value to the
appropriate endpoint of the range and stores the resulting value instead. When
an out-of-range value is assigned to an integer column, MySQL stores the value
representing the corresponding endpoint of the column data type range. If you
store 256 into a
<file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/data-types.html#integer-types>TINYINT
or TINYINT UNSIGNED column, MySQL stores 127 or 255, respectively.
When a floating-point or fixed-point column is assigned a value that
exceeds the range implied by the specified (or default) precision and scale,
MySQL stores the value representing the corresponding endpoint of that range.
Column-assignment conversions that occur due to clipping when MySQL is not
operating in strict mode are reported as warnings for
<file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#alter-table>ALTER
TABLE,
<file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#load-data>LOAD
DATA INFILE,
<file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#update>UPDATE,
and multiple-row
<file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/sql-syntax.html#insert>INSERT
statements. In strict mode, these statements fail, and some or all the values
will not be inserted or changed, depending on whether the table is a
transactional table and other factors. For details, see
<file:///C:/PROGRA%7E1/MySQL/MYSQLD%7E1.14/HTML/server-administration.html#server-sql-mode>Section
5.1.7, Server SQL Modes.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
