MySQL Problem with large numbersfind and insert row to another table using mysql triggercan this mysql query...
Creature in Shazam mid-credits scene?
Aragorn's "guise" in the Orthanc Stone
If a character has darkvision, can they see through an area of nonmagical darkness filled with lightly obscuring gas?
Request info on 12/48v PSU
A social experiment. What is the worst that can happen?
Why is so much work done on numerical verification of the Riemann Hypothesis?
Problem with TransformedDistribution
What if a revenant (monster) gains fire resistance?
Redundant comparison & "if" before assignment
How to implement a feedback to keep the DC gain at zero for this conceptual passive filter?
The screen of my macbook suddenly broken down how can I do to recover
Can someone explain how this makes sense electrically?
Is this toilet slogan correct usage of the English language?
I am looking for the correct translation of love for the phrase "in this sign love"
Can I sign legal documents with a smiley face?
Is a bound state a stationary state?
Not using 's' for he/she/it
Does the expansion of the universe explain why the universe doesn't collapse?
How could a planet have erratic days?
Argument list too long when zipping large list of certain files in a folder
Which one is correct as adjective “protruding” or “protruded”?
Why electric field inside a cavity of a non-conducting sphere not zero?
The IT department bottlenecks progress. How should I handle this?
What are the purposes of autoencoders?
MySQL Problem with large numbers
find and insert row to another table using mysql triggercan this mysql query be optimized further?MySQL: Why queries with subqueries are much faster than single query?Allow multiple users to see procedures and functionsOptimize speed on 400GB MyISAM tableIf I query records matching some value, why does InnoDB examine most of the records that had that value once, but have changed since then?How can I use MySQL variables in subqueries?max allowed packet keep changingError 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES)select MAX() from MySQL view (2x INNER JOIN) is slow
I'm trying to put together a function to convert a size from one type to another (e.g. bytes >> gigabytes).
An extract is below
DROP FUNCTION fc_convSize;
DELIMITER $$
CREATE FUNCTION `fc_convSize`(inVal BIGINT)
RETURNS DECIMAL(25,2)
NO SQL
BEGIN
-- bytes 2 exabytes
SET @inVal := inVal/1024/1024/1024/1024/1024/1024;
RETURN @outVal;
END$$
DELIMITER ;
But it doesn't work with large numbers. e.g.
mysql> SET @input1 := (SELECT @@global.max_binlog_cache_size);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT fc_convSize(@input1);
ERROR 1264 (22003): Out of range value for column 'inVal' at row 1
But from what I can tell, max_binlog_cache_size is 184467440737095*47520*
and the maximum for a BIGINT is 184467440737095*51615*
so it should fit, right?
But it gets stranger.
mysql> SET @input1 := (SELECT @@global.max_binlog_cache_size);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @input1/1024/1024/1024/1024/1024/1024;
+---------------------------------------+
| @input1/1024/1024/1024/1024/1024/1024 |
+---------------------------------------+
| -0.000000000000003552713679 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SET @input2 := (18446744073709547520);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @input2/1024/1024/1024/1024/1024/1024;
+---------------------------------------+
| @input2/1024/1024/1024/1024/1024/1024 |
+---------------------------------------+
| -0.000000000000003552713679 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT 18446744073709547520/1024/1024/1024/1024/1024/1024;
+----------------------------------------------------+
| 18446744073709547520/1024/1024/1024/1024/1024/1024 |
+----------------------------------------------------+
| 15.999999999999996447286321 |
+----------------------------------------------------+
1 row in set (0.00 sec)
What's going on???
mysql
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I'm trying to put together a function to convert a size from one type to another (e.g. bytes >> gigabytes).
An extract is below
DROP FUNCTION fc_convSize;
DELIMITER $$
CREATE FUNCTION `fc_convSize`(inVal BIGINT)
RETURNS DECIMAL(25,2)
NO SQL
BEGIN
-- bytes 2 exabytes
SET @inVal := inVal/1024/1024/1024/1024/1024/1024;
RETURN @outVal;
END$$
DELIMITER ;
But it doesn't work with large numbers. e.g.
mysql> SET @input1 := (SELECT @@global.max_binlog_cache_size);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT fc_convSize(@input1);
ERROR 1264 (22003): Out of range value for column 'inVal' at row 1
But from what I can tell, max_binlog_cache_size is 184467440737095*47520*
and the maximum for a BIGINT is 184467440737095*51615*
so it should fit, right?
But it gets stranger.
mysql> SET @input1 := (SELECT @@global.max_binlog_cache_size);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @input1/1024/1024/1024/1024/1024/1024;
+---------------------------------------+
| @input1/1024/1024/1024/1024/1024/1024 |
+---------------------------------------+
| -0.000000000000003552713679 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SET @input2 := (18446744073709547520);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @input2/1024/1024/1024/1024/1024/1024;
+---------------------------------------+
| @input2/1024/1024/1024/1024/1024/1024 |
+---------------------------------------+
| -0.000000000000003552713679 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT 18446744073709547520/1024/1024/1024/1024/1024/1024;
+----------------------------------------------------+
| 18446744073709547520/1024/1024/1024/1024/1024/1024 |
+----------------------------------------------------+
| 15.999999999999996447286321 |
+----------------------------------------------------+
1 row in set (0.00 sec)
What's going on???
mysql
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I'm trying to put together a function to convert a size from one type to another (e.g. bytes >> gigabytes).
An extract is below
DROP FUNCTION fc_convSize;
DELIMITER $$
CREATE FUNCTION `fc_convSize`(inVal BIGINT)
RETURNS DECIMAL(25,2)
NO SQL
BEGIN
-- bytes 2 exabytes
SET @inVal := inVal/1024/1024/1024/1024/1024/1024;
RETURN @outVal;
END$$
DELIMITER ;
But it doesn't work with large numbers. e.g.
mysql> SET @input1 := (SELECT @@global.max_binlog_cache_size);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT fc_convSize(@input1);
ERROR 1264 (22003): Out of range value for column 'inVal' at row 1
But from what I can tell, max_binlog_cache_size is 184467440737095*47520*
and the maximum for a BIGINT is 184467440737095*51615*
so it should fit, right?
But it gets stranger.
mysql> SET @input1 := (SELECT @@global.max_binlog_cache_size);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @input1/1024/1024/1024/1024/1024/1024;
+---------------------------------------+
| @input1/1024/1024/1024/1024/1024/1024 |
+---------------------------------------+
| -0.000000000000003552713679 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SET @input2 := (18446744073709547520);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @input2/1024/1024/1024/1024/1024/1024;
+---------------------------------------+
| @input2/1024/1024/1024/1024/1024/1024 |
+---------------------------------------+
| -0.000000000000003552713679 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT 18446744073709547520/1024/1024/1024/1024/1024/1024;
+----------------------------------------------------+
| 18446744073709547520/1024/1024/1024/1024/1024/1024 |
+----------------------------------------------------+
| 15.999999999999996447286321 |
+----------------------------------------------------+
1 row in set (0.00 sec)
What's going on???
mysql
I'm trying to put together a function to convert a size from one type to another (e.g. bytes >> gigabytes).
An extract is below
DROP FUNCTION fc_convSize;
DELIMITER $$
CREATE FUNCTION `fc_convSize`(inVal BIGINT)
RETURNS DECIMAL(25,2)
NO SQL
BEGIN
-- bytes 2 exabytes
SET @inVal := inVal/1024/1024/1024/1024/1024/1024;
RETURN @outVal;
END$$
DELIMITER ;
But it doesn't work with large numbers. e.g.
mysql> SET @input1 := (SELECT @@global.max_binlog_cache_size);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT fc_convSize(@input1);
ERROR 1264 (22003): Out of range value for column 'inVal' at row 1
But from what I can tell, max_binlog_cache_size is 184467440737095*47520*
and the maximum for a BIGINT is 184467440737095*51615*
so it should fit, right?
But it gets stranger.
mysql> SET @input1 := (SELECT @@global.max_binlog_cache_size);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @input1/1024/1024/1024/1024/1024/1024;
+---------------------------------------+
| @input1/1024/1024/1024/1024/1024/1024 |
+---------------------------------------+
| -0.000000000000003552713679 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SET @input2 := (18446744073709547520);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @input2/1024/1024/1024/1024/1024/1024;
+---------------------------------------+
| @input2/1024/1024/1024/1024/1024/1024 |
+---------------------------------------+
| -0.000000000000003552713679 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT 18446744073709547520/1024/1024/1024/1024/1024/1024;
+----------------------------------------------------+
| 18446744073709547520/1024/1024/1024/1024/1024/1024 |
+----------------------------------------------------+
| 15.999999999999996447286321 |
+----------------------------------------------------+
1 row in set (0.00 sec)
What's going on???
mysql
mysql
asked Sep 2 '16 at 13:34
IGGtIGGt
9971027
9971027
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Do you have to declare the argument as a bigint?
CREATE OR REPLACE FUNCTION fc_convSize(inVal decimal(40,0))
RETURNS DECIMAL(25,2)
NO SQL
BEGIN
return inVal/1024/1024/1024/1024/1024/1024;
END$$
select fc_convSize(18446744073709547520000000123333232323);
+-----------------------------------------------------+
| fc_convSize(18446744073709547520000000123333232323) |
+-----------------------------------------------------+
| 15999999999999996447.29 |
+-----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
However:
select fc_convSize(10000*18446744073709547520);
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(10000 * 18446744073709547520)'
MySQL tries to fit the result of the multiplication in a bigint which fails. You can cast one of the arguments to void this:
select fc_convSize(10000*cast(18446744073709547520 as decimal(40,0)));
+----------------------------------------------------------------+
| fc_convSize(10000*cast(18446744073709547520 as decimal(40,0))) |
+----------------------------------------------------------------+
| 160000.00 |
+----------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
Note that I just grabbed decimal(40,0) out of thin air, just pick something that is large enough for your needs.
add a comment |
Bytes to gigabytes on only 3 divides:
/1024/1024/1024
A simpler (and faster) way to do this
SET @input1 := (SELECT @@global.max_binlog_cache_size);
is
SELECT @input1 := @@global.max_binlog_cache_size;
The problem with SET @input2 := (18446744073709547520);
is that the number is bigger than can fit in a BIGINT SIGNED
. So it (somewhat erroneously) stores the 64 bits blindly; this turns into a negative number.
Consider CAST
:
SELECT CAST(@@global.max_binlog_cache_size AS UNSIGNED)/1024/1024/1024;
--> 17179869183.999996185303
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f148669%2fmysql-problem-with-large-numbers%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Do you have to declare the argument as a bigint?
CREATE OR REPLACE FUNCTION fc_convSize(inVal decimal(40,0))
RETURNS DECIMAL(25,2)
NO SQL
BEGIN
return inVal/1024/1024/1024/1024/1024/1024;
END$$
select fc_convSize(18446744073709547520000000123333232323);
+-----------------------------------------------------+
| fc_convSize(18446744073709547520000000123333232323) |
+-----------------------------------------------------+
| 15999999999999996447.29 |
+-----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
However:
select fc_convSize(10000*18446744073709547520);
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(10000 * 18446744073709547520)'
MySQL tries to fit the result of the multiplication in a bigint which fails. You can cast one of the arguments to void this:
select fc_convSize(10000*cast(18446744073709547520 as decimal(40,0)));
+----------------------------------------------------------------+
| fc_convSize(10000*cast(18446744073709547520 as decimal(40,0))) |
+----------------------------------------------------------------+
| 160000.00 |
+----------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
Note that I just grabbed decimal(40,0) out of thin air, just pick something that is large enough for your needs.
add a comment |
Do you have to declare the argument as a bigint?
CREATE OR REPLACE FUNCTION fc_convSize(inVal decimal(40,0))
RETURNS DECIMAL(25,2)
NO SQL
BEGIN
return inVal/1024/1024/1024/1024/1024/1024;
END$$
select fc_convSize(18446744073709547520000000123333232323);
+-----------------------------------------------------+
| fc_convSize(18446744073709547520000000123333232323) |
+-----------------------------------------------------+
| 15999999999999996447.29 |
+-----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
However:
select fc_convSize(10000*18446744073709547520);
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(10000 * 18446744073709547520)'
MySQL tries to fit the result of the multiplication in a bigint which fails. You can cast one of the arguments to void this:
select fc_convSize(10000*cast(18446744073709547520 as decimal(40,0)));
+----------------------------------------------------------------+
| fc_convSize(10000*cast(18446744073709547520 as decimal(40,0))) |
+----------------------------------------------------------------+
| 160000.00 |
+----------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
Note that I just grabbed decimal(40,0) out of thin air, just pick something that is large enough for your needs.
add a comment |
Do you have to declare the argument as a bigint?
CREATE OR REPLACE FUNCTION fc_convSize(inVal decimal(40,0))
RETURNS DECIMAL(25,2)
NO SQL
BEGIN
return inVal/1024/1024/1024/1024/1024/1024;
END$$
select fc_convSize(18446744073709547520000000123333232323);
+-----------------------------------------------------+
| fc_convSize(18446744073709547520000000123333232323) |
+-----------------------------------------------------+
| 15999999999999996447.29 |
+-----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
However:
select fc_convSize(10000*18446744073709547520);
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(10000 * 18446744073709547520)'
MySQL tries to fit the result of the multiplication in a bigint which fails. You can cast one of the arguments to void this:
select fc_convSize(10000*cast(18446744073709547520 as decimal(40,0)));
+----------------------------------------------------------------+
| fc_convSize(10000*cast(18446744073709547520 as decimal(40,0))) |
+----------------------------------------------------------------+
| 160000.00 |
+----------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
Note that I just grabbed decimal(40,0) out of thin air, just pick something that is large enough for your needs.
Do you have to declare the argument as a bigint?
CREATE OR REPLACE FUNCTION fc_convSize(inVal decimal(40,0))
RETURNS DECIMAL(25,2)
NO SQL
BEGIN
return inVal/1024/1024/1024/1024/1024/1024;
END$$
select fc_convSize(18446744073709547520000000123333232323);
+-----------------------------------------------------+
| fc_convSize(18446744073709547520000000123333232323) |
+-----------------------------------------------------+
| 15999999999999996447.29 |
+-----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
However:
select fc_convSize(10000*18446744073709547520);
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(10000 * 18446744073709547520)'
MySQL tries to fit the result of the multiplication in a bigint which fails. You can cast one of the arguments to void this:
select fc_convSize(10000*cast(18446744073709547520 as decimal(40,0)));
+----------------------------------------------------------------+
| fc_convSize(10000*cast(18446744073709547520 as decimal(40,0))) |
+----------------------------------------------------------------+
| 160000.00 |
+----------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
Note that I just grabbed decimal(40,0) out of thin air, just pick something that is large enough for your needs.
edited Sep 2 '16 at 15:00
answered Sep 2 '16 at 14:47
LennartLennart
13.3k21243
13.3k21243
add a comment |
add a comment |
Bytes to gigabytes on only 3 divides:
/1024/1024/1024
A simpler (and faster) way to do this
SET @input1 := (SELECT @@global.max_binlog_cache_size);
is
SELECT @input1 := @@global.max_binlog_cache_size;
The problem with SET @input2 := (18446744073709547520);
is that the number is bigger than can fit in a BIGINT SIGNED
. So it (somewhat erroneously) stores the 64 bits blindly; this turns into a negative number.
Consider CAST
:
SELECT CAST(@@global.max_binlog_cache_size AS UNSIGNED)/1024/1024/1024;
--> 17179869183.999996185303
add a comment |
Bytes to gigabytes on only 3 divides:
/1024/1024/1024
A simpler (and faster) way to do this
SET @input1 := (SELECT @@global.max_binlog_cache_size);
is
SELECT @input1 := @@global.max_binlog_cache_size;
The problem with SET @input2 := (18446744073709547520);
is that the number is bigger than can fit in a BIGINT SIGNED
. So it (somewhat erroneously) stores the 64 bits blindly; this turns into a negative number.
Consider CAST
:
SELECT CAST(@@global.max_binlog_cache_size AS UNSIGNED)/1024/1024/1024;
--> 17179869183.999996185303
add a comment |
Bytes to gigabytes on only 3 divides:
/1024/1024/1024
A simpler (and faster) way to do this
SET @input1 := (SELECT @@global.max_binlog_cache_size);
is
SELECT @input1 := @@global.max_binlog_cache_size;
The problem with SET @input2 := (18446744073709547520);
is that the number is bigger than can fit in a BIGINT SIGNED
. So it (somewhat erroneously) stores the 64 bits blindly; this turns into a negative number.
Consider CAST
:
SELECT CAST(@@global.max_binlog_cache_size AS UNSIGNED)/1024/1024/1024;
--> 17179869183.999996185303
Bytes to gigabytes on only 3 divides:
/1024/1024/1024
A simpler (and faster) way to do this
SET @input1 := (SELECT @@global.max_binlog_cache_size);
is
SELECT @input1 := @@global.max_binlog_cache_size;
The problem with SET @input2 := (18446744073709547520);
is that the number is bigger than can fit in a BIGINT SIGNED
. So it (somewhat erroneously) stores the 64 bits blindly; this turns into a negative number.
Consider CAST
:
SELECT CAST(@@global.max_binlog_cache_size AS UNSIGNED)/1024/1024/1024;
--> 17179869183.999996185303
answered Sep 3 '16 at 1:33
Rick JamesRick James
43.6k22259
43.6k22259
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f148669%2fmysql-problem-with-large-numbers%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown