程序员David
6/27/2025
Hey MySQL folks! 👋 I'm stuck with this super weird Error 1690 that's driving me nuts. Here's the deal:
I've got a table with a STEP_ID column (BIGINT UNSIGNED) and I'm trying to do a simple subtraction:
SELECT `NAME`, 202000 - STEP_ID FROM my_table WHERE `Name` = 'aaa';
But MySQL keeps yelling at me:
Error 1690: BIGINT UNSIGNED value is out of range in '(202000 -
my_db.my_table.STEP_ID)'
Here's the crazy part - if I manually plug in the actual value (which is 0 btw), it works fine! 🤯 But using the column name triggers the error. What gives?
I've tried ALL the casting combinations I could think of:
-- Attempt 1: Casting just the column SELECT `NAME`, 202000 - CAST(STEP_ID AS UNSIGNED) FROM my_table... -- Attempt 2: Casting the literal SELECT `NAME`, CAST(202000 AS UNSIGNED) - STEP_ID FROM my_table... -- And like 5 more variations that all failed miserably 😅
This seems like some weird MySQL type conversion voodoo. Anyone run into this before? I'm on a deadline and this is blocking my progress. Help a fellow dev out! 🙏
PS: The table structure and data look fine (added screenshots in the original post), so I'm really puzzled why this simple math operation is causing such drama.
极客小李
6/27/2025
Hey there! 👋 Oh man, I feel your pain* with this one! I remember banging my head against this exact same Error 1690 just last year - it's one of those MySQL quirks that makes you question reality when the manual value works but the column reference doesn't. 😅
Here's the deal - MySQL gets super strict with unsigned math, and it's doing something sneaky called "silent type conversion" behind the scenes. The issue isn't your data, it's that MySQL is worried about negative results in unsigned operations.
The solution? You need to tell MySQL it's okay to handle potential negatives by using SIGNED casting (not UNSIGNED like you tried). Here's what works:
-- The magic fix! ✨ SELECT `NAME`, 202000 - CAST(STEP_ID AS SIGNED) FROM my_table WHERE `Name` = 'aaa';
Or alternatively:
-- This works too! SELECT `NAME`, 202000 - (STEP_ID + 0) FROM my_table WHERE `Name` = 'aaa';
Pro tip from my own battle scars: When working with BIGINT UNSIGNED columns in MySQL, always:
Watch out for these common gotchas:
You got this! 💪 Once you understand MySQL's type conversion quirks, these errors become much easier to debug. If you hit any other snags with your database operations, shout out - happy to help troubleshoot!
SEO keywords naturally included: MySQL Error 1690, BIGINT UNSIGNED, type conversion, signed vs unsigned, MySQL math operations, database troubleshooting