|
|
|
the limits of the all-SQL approach
Tuesday, November 14 2017
In the last year and a half, my work has given me so much practice at designing queries in MySQL that I can now easily accomplish things entirely in SQL that in the past I would've broken up into small pieces and done with a mix of PHP and SQL. The advantage of an all-SQL solution is that it can be run from an "execute SQL" input in an environment like phpMyAdmin or even from an SSH console without me needing to create and upload a PHP script that I may never need to run again. The problem, though, is that SQL is a declarative language, and without the ability to describe any sort of control flow, some tasks become extremely difficult. The thing I wanted to do today was to parse a bunch of URLs out of a large text column and append calculated data to the end of each. This required a number of capabilities one doesn't normally associated with SQL queries, such as the ability to URL-encode a string. That was solveable, but I ran into more trouble when I wanted to actually parse out the URLs. There isn't great Regular Expression capability in MySQL, though it's possible that what I needed to do could've been done with user (homemade) functions. But when it comes to my time, I have to weigh the utility of learning something new against the speed I can do things with my existing knowledge. I eventually gave up on an all-SQL approach and wrote a PHP script to iterate through the target records, parse out and load the URLs into an array (I wish I'd known about preg_match_all back when I wrote some of my string parsers), append the calculated addenda, and update the records.
For linking purposes this article's URL is:
http://asecular.com/blog.php?171114 feedback
previous | next |
