Culled from the Certification Study Guide:
mysql> SELECT * FROM test; +----------------+ | data | +----------------+ | This is a test | +----------------+ 1 row in set (0.00 sec) mysql> SELECT UPPER(data) FROM test; +----------------+ | UPPER(data) | +----------------+ | This is a test | +----------------+ 1 row in set (0.03 sec)
How’s that work?
Alpha called today. She had a mission for me.
I accepted.
They thought I was crazy.
The insertion plan was a launch from the VE-79. The cyborg handlers strapped me in.
It’s no wonder they thought I was crazy. This system was new, and the test pilots had reported negative results.
Atmospheric re-entry can be particularly rough. Some agents never come back down.
Captain was emotional. He’s always like that before one of his own goes into the field.
At impact minus five I deployed the Mission Optional Mandated Standardized Landing Assist Protocol.
The hard part would be getting past the guards. They don’t stand for any nonsense.
I took the subterranean route.
It’s exactly like the training grounds. I can do it with my eyes closed and while holding my breath.
I spotted my objective. Queen Kama-huna-maca-nana-ooboo, intelligence had informed us, was in grave peril. Her prime minister was plotting an assasination during the annual Feast of the Salamander Moon.
Even from my position suction-cupped to the ceiling, I could smell the explosive in the national dish, and the fuse was already burning.
Transcript ends. Duration of the recording remains classified.
A bookmarklet to show the URL of the current page, unescaped, and with the ? and & replaced with line breaks. Handy for viewing the key/value pairs in long query strings.
Tech support, this is Eden.
Yeah, uh, hi. It’s me again.
Oh. You. Yes, how can I help you?
Uh, I’m having trouble with my computer.
What seems to be the problem this time?
The text is too small to read.
Try moving a little closer, like we talked about last time.
Ok, well, now it’s all black.
The screen is black?
No, everything. I can’t see a thing.
Is the power out?
No, of course not. I can maybe see a little bit if I look way down.
What about if you tilt your head sideways?
Now I can see off to the left.
Ma’am, I think you have your hat pulled down over your eyes. I suggest you take it off and put it back on.
Oh, hey, that works great! How do you know all this stuff?
It’s just common sense.
Hm. Where can I get some of that?
Let me check if we have any in stock.
Looks like all we have left is some that someone sent in for a refurb. Let me get the lid screwed back on and I’ll overnight it to you.
So you’ve follow Arjen’s suggestion and are storing all your IP addresses in a nice integer format, having converted them with INET_ATON and selecting them with INET_NTOA.
But now you want to use one of the octets, maybe to find out how many connections you get from 192.x.x.x.
One way is to turn the integer back into a string, and use one of the substring functions to grab the parts you need. Let’s try that out for speed (I’ll use 192.168.1.1 in my examples, which translates to 3232235777):
mysql> SELECT BENCHMARK(10000000, SUBSTRING_INDEX(INET_NTOA(3232235777), '.', 1) = '192'); +-----------------------------------------------------------------------------+ | BENCHMARK(10000000, SUBSTRING_INDEX(INET_NTOA(3232235777), '.', 1) = '192') | +-----------------------------------------------------------------------------+ | 0 | +-----------------------------------------------------------------------------+ 1 row in set (6.28 sec)
Is there a better way? Of course; I’d hardly be writing this blog entry if there weren’t. According to the manual, the calculation for INET_ATON is
192x256^3 + 168x256^2 + 1x256^1 + 1x256^0
Notice all those 256s. 256 is 2^8, so all we have to do is shift the bits to the right by the appropriate multiple of 8, and then throw away anything higher than the 8th bit. We’ll check real quick that gives us the right number:
mysql> SELECT
-> (3232235777 >> 24) & 255 AS oct1,
-> (3232235777 >> 16) & 255 AS oct2,
-> (3232235777 >> 8) & 255 AS oct3,
-> (3232235777 >> 0) & 255 AS oct4;
+------+------+------+------+
| oct1 | oct2 | oct3 | oct4 |
+------+------+------+------+
| 192 | 168 | 1 | 1 |
+------+------+------+------+
Looks like the right answer. Let’s see how it compares.
mysql> SELECT BENCHMARK(10000000, (3232235777 >> 24) & 255 = 192); +-----------------------------------------------------+ | BENCHMARK(10000000, (3232235777 >> 24) & 255 = 192) | +-----------------------------------------------------+ | 0 | +-----------------------------------------------------+ 1 row in set (1.61 sec)
Noticeably faster, and you get to use those bitwise operators that you’ve always wondered about but never had a reason to use.
Powered by WordPress