MySQL에서 작은 따옴표, 큰 따옴표 및 백틱을 사용하는 경우

MySQL에서 작은 따옴표, 큰 따옴표 및 백틱을 사용하는 경우

---

쿼리를 작성하는 가장 좋은 방법을 배우려고합니다. 나는 또한 일관성의 중요성을 이해합니다. 지금까지 저는 실제 생각없이 작은 따옴표, 큰 따옴표, 백틱을 무작위로 사용했습니다.

예:

$query = 'INSERT INTO table (id, col1, col2) VALUES (NULL, val1, val2)';

또한, 상기 예에서, 그 고려 table, col1, val1등의 변수 일 수있다.

이것의 기준은 무엇입니까? 너 뭐하니?

_{여기에서 비슷한 질문에 대한 답을 약 20 분 동안 읽었지만이 질문에 대한 확실한 답은없는 것 같습니다.}

---

백틱은 테이블 및 열 식별자에 사용되지만 식별자가 MySQL 예약 키워드 이거나 식별자에 공백 문자 또는 제한된 집합 (아래 참조)을 초과하는 문자가 포함 된 경우에만 필요합니다 (아래 참조) 예약 된 키워드를 사용하지 않는 것이 좋습니다. 인용 문제를 방지하기 위해 가능한 경우 열 또는 테이블 식별자로 사용합니다.

VALUES()목록 에서와 같이 문자열 값에는 작은 따옴표를 사용해야 합니다. MySQL은 문자열 값에 대해서도 큰 따옴표를 지원하지만 다른 RDBMS에서는 작은 따옴표를 더 널리 사용하므로 큰 따옴표 대신 작은 따옴표를 사용하는 것이 좋습니다.

MySQL은 또한 기대 DATE와 DATETIME리터럴 값은 단일 인용와 같은 문자열로 수 '2001-01-01 00:00:00'. 자세한 내용 은 날짜 및 시간 리터럴 문서를 참조하세요. 특히 -날짜 문자열에서 하이픈 을 세그먼트 구분 기호로 사용하는 대신 사용할 수 있습니다.

따라서 귀하의 예를 사용하여 PHP 문자열을 큰 따옴표로 묶고 값에 작은 따옴표를 사용합니다 'val1', 'val2'. NULLMySQL 키워드이고 특수 (비) 값이므로 따옴표가 없습니다.

이러한 테이블 또는 열 식별자는 예약어가 아니거나 인용이 필요한 문자를 사용하지 않지만 어쨌든 백틱으로 인용했습니다 (이 내용은 나중에 자세히 설명합니다 ...).

RDBMS에 고유 한 함수 (예 : NOW()MySQL)는 인수가 이미 언급 된 동일한 문자열 또는 식별자 인용 규칙을 따르지만 인용해서는 안됩니다.

백틱 (`)
표 및 칼럼 ───────┬─────┬──┬──┬──┬────┬──┬────┬──┬────┬──┬ ───────┐
                      ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓
$ 쿼리 = " INSERT INTO`table` (`id`,`col1`,`col2`,`date`,`updated`)
                       값 (NULL, 'val1', 'val2', '2001-01-01', NOW ()) ";
                               ↑↑↑↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑↑↑↑↑ 
인용되지 않은 키워드 ─────┴┴┴┘ │ │ │ │ │ │ │││││
작은 따옴표 ( ') 문자열 ───────────┴────┴──┴────┘ │ │ │││││
작은 따옴표 ( ') DATE ───────────────────────────┴──────────┘ ││││ │
인용되지 않은 함수 ─────────────────────────────────────────┴┴┴┴┘    

가변 보간

변수에 대한 인용 패턴은 변경되지 않지만 문자열에 직접 변수를 삽입하려는 경우 PHP에서 큰 따옴표로 묶어야합니다. SQL에서 사용하기 위해 변수를 적절하게 이스케이프했는지 확인하십시오. ( 대신 SQL 주입에 대한 보호로 준비된 명령문을 지원하는 API를 사용하는 것이 좋습니다 ).

// 일부 변수 대체와 동일
// 여기서 변수 테이블 이름 $ table은 역 따옴표로 묶여 있고 변수
// VALUES 목록의 작은 따옴표 
$ 쿼리 = "INSERT INTO `$ table` (`id`,`col1`,`col2`,`date`) 가치 (NULL, '$ val1과' , '$ val2만큼' , '$ 날짜' )";

준비된 진술

준비된 명령문으로 작업 할 때 문서를 참조하여 명령문의 자리 표시자를 인용해야하는지 여부를 결정하십시오. PHP, PDO 및 MySQLi에서 사용할 수있는 가장 인기있는 API는 다른 언어로 작성된 대부분의 준비된 문 API와 마찬가지로 인용되지 않은 자리 표시자를 예상 합니다.

// PDO example with named parameters, unquoted
$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (:id, :col1, :col2, :date)";

// MySQLi example with ? parameters, unquoted
$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (?, ?, ?, ?)";

식별자에서 역 따옴표 인용이 필요한 문자 :

MySQL 문서에 따르면 다음 문자 세트를 사용하여 식별자를 인용 (백틱) 할 필요가 없습니다.

ASCII : [0-9,a-z,A-Z$_](기본 라틴 문자, 숫자 0-9, 달러, 밑줄)

You can use characters beyond that set as table or column identifiers, including whitespace for example, but then you must quote (backtick) them.

---

There are two types of quotes in MySQL:

1. ' for enclosing string literals
2. ` for enclosing identifiers such as table and column names

And then there is " which is a special case. It could be used for one of above-mentioned purposes at a time depending on MySQL server's sql_mode:

1. By default the " character can be used to enclose string literals just like '
2. In ANSI_QUOTES mode the " character can be used to enclose identifiers just like `

The following query will produce different results (or errors) depending on SQL mode:

SELECT "column" FROM table WHERE foo = "bar"

ANSI_QUOTES disabled

The query will select the string literal "column" where column foo is equal to string "bar"

ANSI_QUOTES enabled

The query will select the column column where column foo is equal to column bar

When to use what

• I suggest that you avoid using " so that your code becomes independent of SQL modes
• Always quote identifiers since it is a good practice (quite a few questions on SO discuss this)

---

(There are good answers above regarding the SQL nature of your question, but this may also be relevant if you are new to PHP.)

Perhaps it is important to mention that PHP handles single and double quoted strings differently...

Single-quoted strings are 'literals' and are pretty much WYSIWYG strings. Double-quoted strings are interpreted by PHP for possible variable-substitution (backticks in PHP are not exactly strings; they execute a command in the shell and return the result).

Examples:

$foo = "bar";
echo 'there is a $foo'; // There is a $foo
echo "there is a $foo"; // There is a bar
echo `ls -l`; // ... a directory list

---

Backticks are generally used to indicate an identifier and as well be safe from accidentally using the Reserved Keywords.

For example:

Use `database`;

Here the backticks will help the server to understand that the database is in fact the name of the database, not the database identifier.

Same can be done for the table names and field names. This is a very good habit if you wrap your database identifier with backticks.

Check this answer to understand more about backticks.

---

Now about Double quotes & Single Quotes (Michael has already mentioned that).

But, to define a value you have to use either single or double quotes. Lets see another example.

INSERT INTO `tablename` (`id, `title`) VALUES ( NULL, title1);

Here I have deliberately forgotten to wrap the title1 with quotes. Now the server will take the title1 as a column name (i.e. an identifier). So, to indicate that it's a value you have to use either double or single quotes.

INSERT INTO `tablename` (`id, `title`) VALUES ( NULL, 'title1');

---

Now, in combination with PHP, double quotes and single quotes make your query writing time much easier. Let's see a modified version of the query in your question.

$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

Now, using double quotes in the PHP, you will make the variables $val1, and $val2 to use their values thus creating a perfectly valid query. Like

$val1 = "my value 1";
$val2 = "my value 2";
$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

will make

INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, 'my value 1', 'my value 2')

---

In MySQL, these symbols are used to delimit a query ` ," ,' and () .

1. " or ' are used for enclosing string-like values "26-01-2014 00:00:00" or '26-01-2014 00:00:00' . These symbols are only for strings, not aggregate functions like now, sum, or max.

2. ` is used for enclosing table or column names, e.g. select `column_name` from `table_name` where id='2'

3. ( and ) simply enclose parts of a query e.g. select `column_name` from `table_name` where (id='2' and gender='male') or name='rakesh' .

---

The string literals in MySQL and PHP are the same.

A string is a sequence of bytes or characters, enclosed within either single quote (“'”) or double quote (“"”) characters.

So if your string contains single quotes, then you could use double quotes to quote the string, or if it contains double quotes, then you could use single quotes to quote the string. But if your string contains both single quotes and double quotes, you need to escape the one that used to quote the string.

Mostly, we use single quotes for an SQL string value, so we need to use double quotes for a PHP string.

$query = "INSERT INTO table (id, col1, col2) VALUES (NULL, 'val1', 'val2')";

And you could use a variable in PHP's double-quoted string:

$query = "INSERT INTO table (id, col1, col2) VALUES (NULL, '$val1', '$val2')";

But if $val1 or $val2 contains single quotes, that will make your SQL be wrong. So you need to escape it before it is used in sql; that is what mysql_real_escape_string is for. (Although a prepared statement is better.)

---

In combination of PHP and MySQL, double quotes and single quotes make your query-writing time so much easier.

$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";

Now, suppose you are using a direct post variable into the MySQL query then, use it this way:

$query = "INSERT INTO `table` (`id`, `name`, `email`) VALUES (' ".$_POST['id']." ', ' ".$_POST['name']." ', ' ".$_POST['email']." ')";

This is the best practice for using PHP variables into MySQL.

---

There has been many helpful answers here, generally culminating into two points.

1. BACKTICKS(`) are used around identifier names.
2. SINGLE QUOTES(') are used around values.

AND as @MichaelBerkowski said

Backticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.

There is a case though where an identifier can neither be a reserved keyword or contain whitespace or characters beyond limited set but necessarily require backticks around them.

EXAMPLE

123E10 is a valid identifier name but also a valid INTEGER literal.

[Without going into detail how you would get such an identifier name], Suppose I want to create a temporary table named 123456e6.

No ERROR on backticks.

DB [XXX]> create temporary table `123456e6` (`id` char (8));
Query OK, 0 rows affected (0.03 sec)

ERROR when not using backticks.

DB [XXX]> create temporary table 123451e6 (`id` char (8));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '123451e6 (`id` char (8))' at line 1

However, 123451a6 is a perfectly fine identifier name (without back ticks).

DB [XXX]> create temporary table 123451a6 (`id` char (8));
Query OK, 0 rows affected (0.03 sec)

This is completely because 1234156e6 is also an exponential number.

---

If table cols and values are variables then there are two ways:

With double quotes "" the complete query:

$query = "INSERT INTO $table_name (id, $col1, $col2)
                 VALUES (NULL, '$val1', '$val2')";

Or

 $query = "INSERT INTO ".$table_name." (id, ".$col1.", ".$col2.")
               VALUES (NULL, '".$val1."', '".$val2."')";

With single quotes '':

$query = 'INSERT INTO '.$table_name.' (id, '.$col1.', '.$col2.')
             VALUES (NULL, '.$val1.', '.$val2.')';

Use back ticks `` when a column/value name is similar to a MySQL reserved keyword.

Note: If you are denoting a column name with a table name then use back ticks like this:

`table_name`. `column_name` <-- Note: exclude . from back ticks.

---

Single quotes should be used for string values like in the VALUES() list.

Backticks are generally used to indicate an identifier and as well be safe from accidentally using the reserved keywords.

In combination of PHP and MySQL, double quotes and single quotes make your query writing time so much easier.

---

Besides all of the (well-explained) answers, there hasn't been the following mentioned and I visit this Q&A quite often.

In a nutshell; MySQL thinks you want to do math on its own table/column and interprets hyphens such as "e-mail" as e minus mail.

---

Disclaimer: So I thought I would add this as an "FYI" type of answer for those who are completely new to working with databases and who may not understand the technical terms described already.

---

SQL servers and MySQL, PostgreySQL, Oracle don't understand double quotes("). Thus your query should be free from double quotes(") and should only use single quotes(').

Back-trip(`) is optional to use in SQL and is used for table name, db name and column names.

If you are trying to write query in your back-end to call MySQL then you can use double quote(") or single quotes(') to assign query to a variable like:

let query = "select id, name from accounts";
//Or
let query = 'select id, name from accounts';

If ther's a where statement in your query and/or trying to insert a value and/or an update of value which is string use single quote(') for these values like:

let querySelect = "select id, name from accounts where name = 'John'";
let queryUpdate = "update accounts set name = 'John' where id = 8";
let queryInsert = "insert into accounts(name) values('John')";

//Please not that double quotes are only to be used in assigning string to our variable not in the query
//All these below will generate error

let querySelect = 'select id, name from accounts where name = "John"';
let queryUpdate = 'update accounts set name = "John" where id = 8';
let queryInsert = 'insert into accounts(name) values("John")';

//As MySQL or any SQL doesn't understand double quotes("), these all will generate error.

If you want to stay out of this confusion when to use double quotes(") and single quotes('), would recommend to stick with single quotes(') this will include backslash() like:

let query = 'select is, name from accounts where name = \'John\'';

Problem with double(") or single(') quotes arise when we had to assign some value dynamic and perform some string concatenation like:

let query = "select id, name from accounts where name = " + fName + " " + lName;
//This will generate error as it must be like name = 'John Smith' for SQL
//However our statement made it like name = John Smith

//In order to resolve such errors use
let query = "select id, name from accounts where name = '" + fName + " " + lName + "'";

//Or using backslash(\)
let query = 'select id, name from accounts where name = \'' + fName + ' ' + lName + '\'';

If need further clearance do follow quotes in JavaScript

참고URL : https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql