useridhasbeenused是什么意思_百度知道mysql - Data Truncated for Column - Database Administrators Stack Exchange
to customize your list.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. J it only takes a minute:
Here's how it works:
Anybody can ask a question
Anybody can answer
The best answers are voted up and rise to the top
DECLARE float_one, float_two, my_result NUMERIC(7,2)
my_result = CONVERT(float_one/float_two, DECIMAL(7,2));
In this mysql query, I do this type of operation in a stored procedure, but the Linux environment phpmyadmin throws the following warning:
Note: #1265 Data truncated for column 'float_one' at row 5
Does anyone have an idea how can I solve this problem?
CREATE TABLE IF NOT EXISTS `float_sample` (
`id` int(11) NOT NULL auto_increment,
`first_number` float(10,3) default NULL,
`second_number` float(10,3) default NULL,
PRIMARY KEY
) ENGINE=MyISAM
DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `float_sample` (`id`, `first_number`, `second_number`)
(1, 2.900, 1.900),
(2, 3.100, 22.100);
and the procedure
DELIMITER $$
DROP PROCEDURE IF EXISTS float_test$$
CREATE PROCEDURE float_test(IN my_ID INT)
DECLARE first_float, second_float DECIMAL(10,3);
DECLARE done INT DEFAULT 0;
DECLARE myCursor CURSOR FOR
SELECT `first_number`, `second_number`
FROM `float_sample`
WHERE `id` = my_ID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
my_loop:LOOP
FETCH myCursor INTO first_float, second_
IF done = 1 THEN
END LOOP my_
-- SELECT first_float, second_
DELIMITER ;
and the result
1 70 10:41:36
CALL mytests.float_test(1) 0 row(s) affected, 2 warning(s):
1265 Data truncated for column 'first_float' at row 2
1265 Data truncated for column 'second_float' at row 2
What is the the value of float_one at the time of conversion ???
Note this example from MySQL 5.5.12 in Windows
mysql& select convert(20000,decimal(7,2));
+-----------------------------+
| convert(20000,decimal(7,2)) |
+-----------------------------+
20000.00 |
+-----------------------------+
1 row in set (0.00 sec)
mysql& select convert(200000,decimal(7,2));
+------------------------------+
| convert(200000,decimal(7,2)) |
+------------------------------+
99999.99 |
+------------------------------+
1 row in set, 1 warning (0.00 sec)
+---------+------+-----------------------------------------------------------------------+
| Code | Message
+---------+------+-----------------------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'convert(200000,decimal(7,2))' at row 1 |
+---------+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
It may be possible that data was truncated if a number bigger than 99999.99 was in float_one. Perhaps, mysql was converting float_one and float_two to DECIMAL(7,2) individually before performing division. Try using DECIMAL(10,2) or greater to accommodate large values.
There are definite truncation problem going on here
According to
DECIMAL and NUMERIC values are stored as strings, rather than as
binary floating point numbers, in order to preserve the decimal
precision of those numbers. One character is used for each digit of
the value, the deciaml point (if scale > 0) and the - sign (for
negative numbers). If the scale is 0, DECIMAL and NUMERIC values
contain no decimal point or fractional part.
The maximum range of DECIMAL and NUMERIC is the same as for DOUBLE,
but the actual range for the given DECIMAL or NUMERIC column can be
constrainted by the precision and scale for a give column, When such a
column is assigned a value with more digits following the decimal
point than are allowed by the specified scale, the value is rounded to
that scale. When a DECIMAL or NUMERIC column is assigned a value whose
magnitude exceeds the range implied by the specified (or defaulted)
precision and scale, MySQL stores the value representing the
corresponding endpoint of that range.
You need to accommodate a larger precision and/or scale.
Here is an example as to why
I wrote this stored procedure using your specifications for DECMIAL and NUMERIC.
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`NumTest` $$
CREATE PROCEDURE `test`.`NumTest` (num1 NUMERIC(7,2), num2 NUMERIC(7,2))
DECLARE float_one,float_two,my_result NUMERIC(7,2);
DECLARE f1,f2 DOUBLE(7,2);
SET f1 = num1;
SET f2 = num2;
SET float_one = num1;
SET float_two = num2;
SELECT f1 / f2;
SELECT float_one / float_
SELECT CONVERT(float_one / float_two,DECIMAL(7,2));
SET my_result = CONVERT(float_one / float_two,DECIMAL(7,2));
SELECT my_
DELIMITER ;
I used two values: 290.0 and 14.5 for this test.
Before calling the NumTest stored procedure, I manually calculated 290.0 / 14.5
mysql& select 290.0 / 14.5;
+--------------+
| 290.0 / 14.5 |
+--------------+
20.00000 |
+--------------+
1 row in set (0.00 sec)
I divided each number by 100, 10000, and 1000000 and tried again
mysql& select 2.9 / .145;
+------------+
| 2.9 / .145 |
+------------+
20.00000 |
+------------+
1 row in set (0.00 sec)
mysql& select .029 / .00145;
+---------------+
| .029 / .00145 |
+---------------+
20.0000000 |
+---------------+
1 row in set (0.00 sec)
mysql& select .00029 / .0000145;
+-------------------+
| .00029 / .0000145 |
+-------------------+
+-------------------+
1 row in set (0.00 sec)
So far, so good !!! Now for the stored procedure.
mysql& call numtest(290.0,14.5);
+-----------+
+-----------+
| 20.000000 |
+-----------+
1 row in set (0.00 sec)
+-----------------------+
| float_one / float_two |
+-----------------------+
20.000000 |
+-----------------------+
1 row in set (0.00 sec)
+---------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(7,2)) |
+---------------------------------------------+
+---------------------------------------------+
1 row in set (0.00 sec)
+-----------+
| my_result |
+-----------+
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
So my orignal two numbers work fine. Let's divide them by 100 and try again.
mysql& call numtest(2.9,0.145);
+-----------+
+-----------+
| 19.333333 |
+-----------+
1 row in set (0.00 sec)
+-----------------------+
| float_one / float_two |
+-----------------------+
19.333333 |
+-----------------------+
1 row in set (0.00 sec)
+---------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(7,2)) |
+---------------------------------------------+
+---------------------------------------------+
1 row in set (0.00 sec)
+-----------+
| my_result |
+-----------+
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
+-------+------+-------------------------------------------+
| Level | Code | Message
+-------+------+-------------------------------------------+
| 1265 | Data truncated for column 'num2' at row 2 |
+-------+------+-------------------------------------------+
1 row in set (0.00 sec)
WAIT, we lost some precision. What happened ??? How did this happen ??? You need to accommodate more decmial digits (> 2)
I substituted (7,2) with (10,7) in the stored procedure and got back the proper precision
mysql& call numtest(2.9,0.145);
+----------------+
+----------------+
+----------------+
1 row in set (0.00 sec)
+-----------------------+
| float_one / float_two |
+-----------------------+
+-----------------------+
1 row in set (0.01 sec)
+----------------------------------------------+
| CONVERT(float_one / float_two,DECIMAL(10,7)) |
+----------------------------------------------+
20.0000000 |
+----------------------------------------------+
1 row in set (0.03 sec)
+------------+
| my_result
+------------+
| 20.0000000 |
+------------+
1 row in set (0.05 sec)
Query OK, 0 rows affected (0.06 sec)
106k15137272
From what I can see your stored procedure just loops through the rows - it does not 'save the data' anywhere and you have commented out the select:
-- SELECT first_float, second_
Yes you are getting warnings when the float(10,3) fields are converted to decimal(10,3). Whether they are a problem or not depends on what you want to do with the fields.
Is this just a sample of the real code? You may need to post some more...
20.4k958106
If you r using if-condition you should close the if condition and u should put an else condition. its must..
Then execute it, it will work Good..
(eg:) IN MY CODE
if($cid =='')
$consumer_detail = db_insert('student')
->fields(array(
'name' =& $name,
'age' =& $age,
'country' =& $country,
'place' =& $place,
'id_no' =& $id_no,
'reg_no' =& $reg_no,
'state' =& $state,
'hick1' =& $hick1,
'hick2' =& $hick2,
'hick3' =& $hick3,
->execute();
drupal_set_message('Product Details Inserted Successfully.');
drupal_goto("form");
echo "It's not working...";
You Should End with a Else Part..
If It work's good Let me know ...
If there Any ?.....
Your Answer
Sign up or
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Post as a guest
By posting your answer, you agree to the
Not the answer you're looking for?
Browse other questions tagged
Database Administrators Stack Exchange works best with JavaScript enabledapi_tutorial_图文_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
api_tutorial
上传于||文档简介
&&a​p​i
阅读已结束，如果下载本文需要使用0下载券
想免费下载更多文档？
定制HR最喜欢的简历
下载文档到电脑，查找使用更方便
还剩22页未读，继续阅读
定制HR最喜欢的简历
你可能喜欢FemasAPI常见问题手册_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
FemasAPI常见问题手册
上传于||文档简介
&&中​金​所​飞​马​a​p​i​培​训
阅读已结束，如果下载本文需要使用1下载券
想免费下载本文？
定制HR最喜欢的简历
下载文档到电脑，查找使用更方便
还剩20页未读，继续阅读
定制HR最喜欢的简历
你可能喜欢}