Skip to content

# Quoting text JavaScript/Python style

MySQL SQL Syntax November 15, 2011November 15, 2011

Unless your MySQL is configured to use ANSI_QUOTES in sql_mode, you are able to quote your text in one of two forms: using single quotes or double quotes:

>     UPDATE world.Country SET HeadOfState = 'Willy Wonka' WHERE Code='USA'
>     UPDATE world.Country SET HeadOfState = "Willy Wonka" WHERE Code="USA"

This makes for JavaScript- or Python-style quoting: you quote by your needs. Say you have a text which includes single quotes:

> It is what you read when you don’t have to that determines what you will be when you can’t help it. – Oscar Wilde

You wish to insert this text to some tables. You could go through the trouble of escaping it:

>     INSERT INTO quotes (quote, author) VALUES (
>       'It is what you read when you don\'t have to that determines what you will be when you can\'t help it.', 'Oscar Wilde');

or you could just wrap it in double quotes:

>     INSERT INTO quotes (quote, author) VALUES (
>       "It is what you read when you don't have to that determines what you will be when you can't help it.", 'Oscar Wilde');

I find this useful when using SQL to generate queries. Take, for example, eval() for MySQL: the statement:

>     CALL eval('select concat(\'KILL \',id) from information_schema.processlist where user=\'webuser\'');

is just so more easily written this way:

>     CALL eval("select concat('KILL ',id) from information_schema.processlist where user='webuser'");

I don’t suggest one should use this method throughout her application code. Application code works great with auto-escaping string literals. But for the handy DBA or developer, who needs to work some quick queries by hand, this makes for an easier syntax to use.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
2
Leave a Reply

2 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
2 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Daniël van Eeden

String escaping between MySQL and PostgreSQL is unfortunately not very compatible. As of PostgreSQL 9.1 standard_conforming_strings is on by default. Which means you have to use E’some string with backslash escapes.’ which is not supported in MySQL. So much for SQL standards and different implementations…

Guest
Roland Bouman

Actually, to the best of my knowledge, the standard way to escape single quotes is by using two single quotes. I don’t think there are any other escapes

(I should check what it says on characters like \t\f\r\n etc. I think you’re supposed to enter them literal, as is in standard SQL)

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.