SQL переносимость получилась
В моей компании я работаю над завершением серверной части для Oracle для Python ORM. Я поражен тем, насколько по-разному RDBMS делают вещи даже для простых вещей. Я многое узнал о различиях между Oracle и другими СУБД. Просто из чистого любопытства я хотел бы узнать больше.
Каковы некоторые распространенные ошибки в терминах переноса SQL с одной платформы на другую?
Пожалуйста, только один гоча за ответ.
17 ответов
У Oracle, похоже, нет проблем с курсорами, это огромная проблема с производительностью на SQL-сервере.
На самом деле практически все настройки производительности зависят от базы данных (именно поэтому стандартный код ANSII часто работает очень плохо по сравнению с лучшими методами, разработанными для специфики SQL, специфичной для базы данных).
Даты - другая вещь, которая, кажется, обрабатывается очень по-разному от базы данных к базе данных.
Типы данных также не эквивалентны. Одна вещь, которая имеет тенденцию привлекать новичков в SQL Server, заключается в том, что тип данных временной метки не имеет абсолютно никакого отношения к датам и времени и не может быть преобразован в значение времени данных.
У Oracle другой подход к кавычкам, в отличие от MySQL.
MySQL: `object_name`, 'string', "string"
Oracle: "object_name", 'string'
Кроме того, побег отличается.
MySQL: 'It\'s easy'
Oracle: 'It''s slightly confusing'
(Обратите внимание, что для экранирования чего-либо, кроме кавычек в Oracle, вы можете использовать в своем запросе директиву ESCAPE; SELECT * FROM testTable WHERE процент = '50\%' ESCAPE '\')
Oracle не разрешает оператор выбора без предложения FROM. Таким образом, вы не можете делать такие запросы:
SELECT 1
Вместо этого вы должны сказать, что запрос от DUAL Таблица:
SELECT 1 FROM DUAL
Oracle не позволит вам вставлять пустые строки: они автоматически преобразуются в NULL.
Многозначные запросы предложения IN. Я использовал их все время в Oracle и с удивлением обнаружил, что вы не можете сделать это в SQL Server. Например, этот запрос:
SELECT * FROM mytable WHERE (col1, col2) IN ( SELECT col1, col2 FROM othertable )
Другой пример - генерация уникальных (обычно суррогатных) первичных ключей.
Многие базы данных, такие как SQL Server и sqlite, позволяют объявлять столбец как идентификатор: обычно, если значение для этого столбца отсутствует при вставке, база данных создает уникальное значение для столбца.
Oracle, напротив, заставляет вас создать последовательность, отдельную от таблицы, а затем использовать nextval в последовательности для генерации следующего значения:
CREATE SEQUENCE test_seq;
SELECT test_seq.nextval FROM dual;
Или, более типично:
INSERT INTO foo(id, title) VALUES (test_seq.nextval, 'bar');
Каковы некоторые распространенные ошибки в терминах переноса SQL с одной платформы на другую?
То же, что и попытки перевести с English в Russian подставляя словарные слова прямо во фразы.
Работает на " привет" и " до свидания", но не может, потому что у Мэри был маленький ягненок, не говоря уже о Шекспире.
Разные RDBMSесть разные культуры, несмотря на SQL во имя
Например, ограничение строки.
В Oracle:
WHERE rownum = 1
В SQL Server:
SELECT TOP 1
В MySQL а также PostgreSQL:
LIMIT 1
В DB2:
SELECT * ... FETCH FIRST 1 ROW ONLY
Четыре разных пункта.
Список несоответствий между SQL Server и Oracle, с которыми я столкнулся при портировании:
Думаете, что стандарт ANSI SQL полностью переносим между базами данных? Подумай еще раз.
Проблемы с производительностью очень важны. Например, представления в Oracle, AFAIK, почти такие же быстрые, как таблицы. Это было не так с SQLServer, когда я должен был их использовать. Представления фактически убивали производительность, замедляя те же выборки на порядок или более (запрос прямо из таблиц занимал, скажем, 0,5 с, в то время как использование представления могло занимать минуту). Также было много ограничений на их использование, например, не все функции SQL можно было использовать в представлениях.
Обратите внимание, что это было 5-6 лет назад, я не знаю, улучшила ли Microsoft это с тех пор.
Я вспоминаю странную проблему с Оракулом, которая совершенно не понравилась мне. Я не уверен, что это была конфигурация экземпляра или настройки по умолчанию, но мы не могли иметь более 1000 элементов в выражении IN. Таким образом, мы должны были обмануть это, делая то, что хотели:
SELECT Col1,Col2
FROM Table
WHERE Code IN (1,2,3,...,1000)
OR Code IN (1001,1002,1003,...,2000)
и т.п.
Ужасно, но это сработало.
(Прежде чем кто-либо указывает на очевидное решение подзапроса или встроенного представления, запрос был сгенерирован в совершенно другой системе)
Чтобы получить общее представление о проблемах, связанных с конкретным продуктом, необходимо изучить разницу между логическим проектированием базы данных и физическим проектированием базы данных.
Логический дизайн базы данных в основном связан с особенностями таблиц. Особенности таблиц включают в себя столбцы и ограничения. Хотя сами таблицы являются физическими, дизайн таблиц обычно очень переносим из одной системы баз данных в другую. Существуют различия в том, как работают некоторые типы данных, и несколько различий в синтаксисе, например, может ли подчеркивание использоваться в имени таблицы или нет. Но хороший логический дизайн должен портировать с одной системы на другую с незначительными изменениями или без изменений.
Физическое проектирование базы данных в основном связано с особенностями инфраструктуры, на которой основывается структура таблиц. Почти все системы поддерживают индексы, и тип индекса по умолчанию - B-дерево, хотя его можно назвать как-то иначе. Но с этого момента каждая система имеет свои физические особенности, которые могут полностью отличаться от одной системы к другой. Типичная физическая особенность Oracle - табличные пространства. С табличными пространствами тесно связано отображение между таблицами и табличными пространствами. Физический дизайн должен быть сделан на системной основе.
Кроме того, какую систему RDM вы используете, вам необходимо учитывать в своем проекте объем данных, нагрузку, требования к времени отклика и системные ресурсы, такие как диски. Хорошей новостью является то, что можно внести много изменений в физический дизайн без изменений в коде приложения. Это известно как физическая независимость данных. Это означает, что вы можете свободно настраивать и настраивать физический дизайн после того, как вы написали некоторый код приложения и некоторые данные загружены.
Возможно, вы захотите взглянуть на некоторые книги по проектированию баз данных, чтобы получить более глубокое представление о логическом и физическом дизайне и разнице между ними. Некоторые популярные авторы - CJ Date и Joe Celko.
Непонятный синтаксис соединений, такой как синтаксис Oracle (+) для внешних объединений. В компании, в которой я работал, этот синтаксис использовался повсеместно, а не стандартным синтаксисом LEFT OUTER JOIN / LEFT JOIN, что делало перенос некоторых вещей на MySQL довольно болезненным.
Временные таблицы - Oracle против SQL Server/MySQL. Переход с Oracle на MS/MySQL, без проблем. Наоборот, немного по-другому.
Oracle не позволяет иметь несколько вставок в одном запросе. MySQL позволяет это:
INSERT INTO test(id, name) VALUES (1, 'foo'),(2, 'bar');
Установить поддержку оператора.
Помимо UNION / UNION ALL, поддержка операторов множеств довольно разрозненна для всех баз данных. Oracle и SQL-сервер поддерживают большинство из них, но Oracle поддерживает операцию MINUS, а также эквивалентную стандартную операцию EXCEPT DISTINCT. AFIK, MySQL поддерживает только UNION (без поддержки INTERSECT или EXCEPT).
Я не уверен, что это так в драйверах, которые поставляются с Python, но в версии "Horrable", которую я использовал, если столбец в наборе результатов возвращает NULL для всех значений в столбце, сам столбец не возвращается в структура набора результатов. Это могло (и часто приводило) приводить к ошибкам в работе, которые невозможно было воспроизвести при разработке.
Поскольку вы уходите от Oracle, это не должно вызывать беспокойства, но разработчики используют ключевое слово, чтобы смягчить эту "особенность", которая, я сомневаюсь, поддерживается в MySQL. Я забыл, что это такое, и Google не помогает.
Различные базы данных обрабатывают двоичные данные немного по-разному. Так, например, это будет работать под MySQL:
mysql> CREATE TABLE t (c BINARY(3));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t SET c = 'z';
Query OK, 1 row affected (0.01 sec)
Тем не менее, Oracle полагается на шестнадцатеричные значения:
SQL> CREATE TABLE t (c RAW(3));
Table created.
SQL> INSERT INTO t VALUES ('z');
INSERT INTO t VALUES ('z')
*
ERROR at line 1:
ORA-01465: invalid hex number
Вместо этого мы должны преобразовать его в гекс:
SQL> INSERT INTO t VALUES (rawtohex('z'));
1 row created.