a MYSQL script to convert the column names to uppercase

Question

This is the opposite of this question.

I am migrating from a lowercase_names db server to a case sensitive setup in which I require UPPER CASE names for tables and column (they are stored uppercase in code).

I have read in this answer that using sed is a good way to replace names to lower case, which is not what I want.

So starting from the regex

s/`\(\w\+\)`/\L&/g

I changed it to

s/`\(\w\+\)`/\U&/g

Thinking that it would convert to upper case. Then I tried to run this one-liner after having imported the uppercase DDL

mysqldump --skip-triggers --compact --no-create-info -hxx -uxx -pxx source | sed s/`\(\w\+\)`/\U&/g | mysql -hxx -uxx -pxx target

But that failed

-bash: /g: No such file or directory
-bash: (w+): command not found
sed: -e expression #1, char 4: unterminated `s' command

How can I run this uppercase-converter in Bash?

score 2 · Answer 1 · edited May 23 '17 at 10:25

2

You need to use single quotes around your sed command, otherwise some of the parts will be interpreted by the shell:

mysqldump ... | sed 's/`\(\w\+\)`/\U&/g'

In case you're interested, the & is interpreted as an instruction to run the command in the background (so /g is processed as a separate command) and the backticks create a subshell. Escaping the parentheses means that they are interpreted literally. Escaping \w and \+ has no effect in this context, so the command (w+) is run.

As you're using & in the replacement string to get the full matched patttern rather than a specific capture group (e.g. \1), you can also remove the parentheses as pointed out by Avinash:

mysqldump ... | sed 's/`\w\+`/\U&/g'

In response to your concerns about other data being affected, it's a pretty broad regex (captures any word characters within backticks) so could apply to a lot of things. Depending on the size of your data, you might like to use sed -n 's/`\w\+`/\U&/gp' which will show you the parts that are being affected by the substitution. Using that information you could possibly change your regex. However, it's likely that there are better ways to do what you want in SQL itself.

edited May 23 '17 at 10:25

Community

1
1

answered Feb 09 '15 at 14:02

Tom Fenech

72,334
12
107
141

Hahahahahahahahaha. Really trivial! Works great now! – usr-local-ΕΨΗΕΛΩΝ Feb 09 '15 at 14:06
1

And you don't need to use capturing group. – Avinash Raj Feb 09 '15 at 14:07
Actually now that I think about it the Shellshock exploits used such parentheses syntax – usr-local-ΕΨΗΕΛΩΝ Feb 09 '15 at 14:08
Could the sed damage geolocation data? I found an error " Column count doesn't match value count at row 42". Meanwhile I'll dump the DML to a file to check the exact line – usr-local-ΕΨΗΕΛΩΝ Feb 09 '15 at 14:27
@djechelon responded in my answer – Tom Fenech Feb 09 '15 at 14:35
Altering the information_schema is risky when your db features foreign keys, views and routines. We did a tremendous mistake by the first time we migrated from lowercase=0 to lowercase=1 (you understand I'm migrating from lowercase=1 to lowercase=0) – usr-local-ΕΨΗΕΛΩΝ Feb 09 '15 at 14:43

a MYSQL script to convert the column names to uppercase

1 Answers1