Чем чревато хранить цифры в char-полях mysql?

Vladislav Vetrov написал(а) к All в Oct 18 22:45:08 по местному времени:

Нello All!

Столкнулся с такой проблемой, что цифры в БД записываются текстом, в char-поля, а не цифрой, как положено.

Но оператор select работает нормально (в поле name char-текст "55.5"):

SELECT * FROM `new_table` WНERE name > 54.2;

Ответ:
id name
1 55.5

В общем - сабж. Какие аргументы? Почему цифры лучше хранить, как цифры, а не как текст? Невозможно провести индексацию? Потеря производительности на преобразование типов? Что ещё?

Vladislav

... -= - <<< - >>> - =-
--- GoldED+/LNX 1.1.5-b20160201 by ASA

Andrew Kant написал(а) к Vladislav Vetrov в Oct 18 07:38:39 по местному времени:

Нello Vladislav!

Wednesday October 10 2018 22:45, Vladislav Vetrov wrote to All:

VV> Столкнулся с такой проблемой, что цифры в БД
Ну вот совсем не понимаю, причем тут Linux? Что, БД есть только на это платформе?

VV> записываются текстом, в
VV> char-поля, а не цифрой, как положено.

VV> Но оператор select работает нормально (в поле name char-текст "55.5"):

VV> SELECT * FROM `new_table` WНERE name > 54.2;

VV> Ответ:
VV> id name
VV> 1 55.5

VV> В общем - сабж. Какие аргументы? Почему цифры лучше хранить, как цифры,
VV> а не как текст? Невозможно провести индексацию? Потеря
VV> производительности на преобразование типов? Что ещё?

Основная проблема - корректность сравнения, и, как следствие, порядок сортировки. Сравнение идёт символьное, а не цифровое. Опять-же, неоднозначность представления. '5' как соотносится с '5 ' и с ' 5' ? Как ты думаешь, будут ли равны эти значения?

Ну и по мелочи - обычно цифровой формат компактнее, то есть занимает меньше места при прочих равных, так как на одну десятичную цифру нужно не один байт, а примерно 4 бита.

Good bye!
Andrew

--- GoldED+/W32 1.1.4.7

Vladislav Vetrov написал(а) к Andrew Kant в Oct 18 09:47:14 по местному времени:

Нello Andrew!

11 окт 18 07:38, you wrote to me:

VV>> В общем - сабж. Какие аргументы? Почему цифры лучше хранить, как цифры,
VV>> а не как текст? Невозможно провести индексацию? Потеря
VV>> производительности на преобразование типов? Что ещё?
AK>
AK> Основная проблема - корректность сравнения, и, как следствие, порядок
AK> сортировки. Сравнение идёт символьное, а не цифровое. Опять-же,
AK> неоднозначность представления. '5' как соотносится с '5 ' и с ' 5' ? Как ты
AK> думаешь, будут ли равны эти значения?
AK>
AK> Ну и по мелочи - обычно цифровой формат компактнее, то есть занимает меньше
AK> места при прочих равных, так как на одну десятичную цифру нужно не один
AK> байт, а примерно 4 бита.

Благодарю за ответ. Сортировку цифр, записанных текстом, оказывается можно решить следующим хаком: к тексту добавляем любую цифру, например 0.0, затем сортируем.

Пример:

Создаём таблицу:
create table worker (
id INT AUTO_INCREMENT PRIMARY KEY,
name varchar(20),
dept varchar(10),
salary varchar(10)
);

Наполняем данными:
insert into worker values(100,'Dima','Sales','5000');
insert into worker values(200,'AndreyEx','IT','5.500');
insert into worker values(300,'Boris','IT','7.0');
insert into worker values(400,'Anna','Marketing','9.5');
insert into worker values(500,'Anton','IT','951');
insert into worker values(600,'Vasya','IT','70');

Смотрим, что получилось:

mysql> select * from worker;

+-----+----------+-----------+--------+
| id | name | dept | salary |
+-----+----------+-----------+--------+
| 100 | Dima | Sales | 5000 |
| 200 | AndreyEx | IT | 5.500 |
| 300 | Boris | IT | 7.0 |
| 400 | Anna | Marketing | 9.5 |
| 500 | Anton | IT | 951 |
| 600 | Vasya | IT | 70 |
+-----+----------+-----------+--------+

Пробуем сортировать по последнему столбцу salary:

mysql> SELECT name, salary FROM worker ORDER BY salary;

+----------+--------+
| name | salary |
+----------+--------+
| AndreyEx | 5.500 |
| Dima | 5000 |
| Boris | 7.0 |
| Vasya | 70 |
| Anna | 9.5 |
| Anton | 951 |
+----------+--------+

Пробуем сортировать, добавив к текстовому столбцу 0.0:

mysql> SELECT name, salary FROM worker ORDER BY salary+0.0;

+----------+--------+
| name | salary |
+----------+--------+
| AndreyEx | 5.500 |
| Boris | 7.0 |
| Anna | 9.5 |
| Vasya | 70 |
| Anton | 951 |
| Dima | 5000 |
+----------+--------+

Но индексы в таком столбце (salary) работать не будут, т.к. индекс - это сортировка, плюс механизм быстрого доступа (поиска) нужного значения. В столбце salary сортировка будет в алфавитном порядке и хак с добавлением 0.0 уже не прокатит.

В связи с чем вопрос - на сколько нужны индексы в БД? Слышал, что если идёт выборка в MySQL через условие больше или меньше, то дальше индексы уже не работают. Вот этот момент мне не очень понятен. Почему индексы дальше уже не работают после первой операции сравнения?

Также интересно, на сколько отказ от индексов замедлит работу БД?

Vladislav

... -= - <<< - >>> - =-
--- GoldED+/LNX 1.1.5-b20170303 by ASA

Andrey Ostanovsky написал(а) к Vladislav Vetrov в Oct 18 11:12:54 по местному времени:

Нello Vladislav!

17 Oct 18 09:47, you wrote to Andrew Kant:

VV> В связи с чем вопрос - на сколько нужны индексы в БД? Слышал, что если
VV> идёт выборка в MySQL через условие больше или меньше, то дальше
VV> индексы уже не работают. Вот этот момент мне не очень понятен. Почему
VV> индексы дальше уже не работают после первой операции сравнения?

Это дело мутное: с какого момента/объема базы индекс начинает работать быстрее, чем линейный поиск. И никто этого популярно объяснить не может...

VV> Также интересно, на сколько отказ от индексов замедлит работу БД?

В mysql тоже есть анализатор запросов. :)

Andrey

--- GoldED+/BSD 1.1.5-b20070503

Andrew Kant написал(а) к Vladislav Vetrov в Oct 18 11:06:57 по местному времени:

Нello Vladislav!

Wednesday October 17 2018 09:47, Vladislav Vetrov wrote to Andrew Kant:

VV>>> В общем - сабж. Какие аргументы? Почему цифры лучше хранить, как
VV>>> цифры, а не как текст? Невозможно провести индексацию?
VV>>> Потеря производительности на преобразование типов? Что ещё?
AK>>
AK>> Основная проблема - корректность сравнения, и, как следствие,
AK>> порядок сортировки. Сравнение идёт символьное, а не цифровое.
AK>> Опять-же, неоднозначность представления. '5' как соотносится с '5 '
AK>> и с ' 5' ? Как ты думаешь, будут ли равны эти значения?
AK>>
AK>> Ну и по мелочи - обычно цифровой формат компактнее, то есть
AK>> занимает меньше места при прочих равных, так как на одну десятичную
AK>> цифру нужно не один байт, а примерно 4 бита.

VV> Благодарю за ответ. Сортировку цифр, записанных текстом, оказывается
VV> можно решить следующим хаком: к тексту добавляем любую цифру, например
VV> 0.0, затем сортируем.

VV> Пример:

VV> Пробуем сортировать по последнему столбцу salary:

mysql>> SELECT name, salary FROM worker ORDER BY salary;

VV> Пробуем сортировать, добавив к текстовому столбцу 0.0:

mysql>> SELECT name, salary FROM worker ORDER BY salary+0.0;

В первом случае ты используешь лексикографическую сортировку (символьный тип), во втором у тебя итоговое выражение цифрового типа (неявное преобразование в намерик). Но зачем делать неявное преобразование, причем по каждой строке (а может и не раз), когда можно изначально сохранить в таблице значение цифрового типа?

VV> Но индексы в таком столбце (salary) работать не будут, т.к. индекс - это
VV> сортировка, плюс механизм быстрого доступа (поиска) нужного значения. В
VV> столбце salary сортировка будет в алфавитном порядке и хак с добавлением
VV> 0.0 уже не прокатит.

Не помню, есть-ли в mysql функциональные индексы, но в других БД точно есть. И там можно делать индекс не по столбцу (salary), а по выражению (salary+0.0). Если в клаузе WНERE используется такое выражение, то возможно использовать соответствующий индекс. Но, опять-же, зачем? Очень много зависит от правильного проектирования структуры данных, и разные типы придуманы как раз для того, чтоб выбрать именно тот, который лучше подходит в каждом конкретном случае. Но можно все делать и через ж, и при этом даже оно может быть будет хорошо работать на начальном этапе (как обычно говорят разработчики - а у меня все работает, на 5 записях :)

VV> В связи с чем вопрос - на сколько нужны индексы в БД? Слышал, что если
VV> идёт выборка в MySQL через условие больше или меньше, то дальше индексы
VV> уже не работают. Вот этот момент мне не очень понятен. Почему
VV> индексы дальше уже не работают после первой операции сравнения?

Вопрос почему используется или не используется тот или иной индекс, причем в каждой конкретной версии каждой конкретной БД - это тот самый misterious magic, и разбираться в нём - искусство :) Поэтому, используй KISS-прицнип, чем проще - тем лучше и объяснимее.

VV> Также интересно, на сколько отказ от индексов замедлит работу БД?
Всё зависит от конкретного набора данных и от самого приложения, их использующего.

Опять-же, надо помнить, что индексы могут ускорить только выборку - если ты знаешь теорию, то должен знать, что полный перебор имеет сложность порядка N [где N-число записей в таблице], а поиск по индексу - сложность порядка log(N), при малых значениях N полный перебор может быть быстрее поиска по индексу, но при увеличении всегда найдётся такое конечное значение N, после которого индексный поиск будет эффективнее, и эта эффективность будет увеличиваться с возрастанием N; при выборке из двух таблиц всё ещё сложнее, в зависимости от типа связки, при полном переборе по двум таблицам сложность соизмерима с NM, что обычно существенно хуже, чем N*log(M) при использовании индекса. А вот что лучше, N*log(M) или log(N)M (то есть по какой таблице перебераем, а в какой ищем по индексу) - зависит от конкретных данных. Но есть ещё и разные merge и hashjoin, которые могут ускорять процесс, а могут и тормозить :) Так что всё сложно.

Но при этом индексы всегда тормозят любые обновления данных, от которых зависят (так как требуется поддержка индексов в актуальном состоянии).

Так что "думайте сами, решайте сами..."

Good bye!
Andrew

--- GoldED+/W32 1.1.4.7

Semen Panevin написал(а) к Vladislav Vetrov в Oct 18 08:20:10 по местному времени:

Доброго здоровьица тебе, Vladislav!

Wednesday October 10 2018 22:45, Vladislav Vetrov писал All:

VV> Но оператор select работает нормально (в поле name char-текст "55.5"):

VV> SELECT * FROM `new_table` WНERE name > 54.2;

VV> Ответ:
VV> id name
VV> 1 55.5
А попробуй ради спортивного интереса
select * from new_table where name > 55.45

Лично меня результат вряд-ли удивит :) хотя я больше по mssql...

С наилучшими пожеланиями, Семён.

... Век живи, век лечись...
--- GoldED+/LNX 1.1.5-b20170303 (Linux 4.1.12-gentoo iF6M10)

Vladislav Vetrov написал(а) к Semen Panevin в Oct 18 23:34:06 по местному времени:

Нello Semen!

18 окт 18 08:20, you wrote to me:

VV>> Но оператор select работает нормально (в поле name char-текст "55.5"):
SP>
VV>> SELECT * FROM `new_table` WНERE name > 54.2;
SP>
VV>> Ответ:
VV>> id name
VV>> 1 55.5
SP>
SP> А попробуй ради спортивного интереса
SP> select * from new_table where name > 55.45

mysql> SELECT * FROM worker;

+-----+----------+-----------+--------+
| id | name | dept | salary |
+-----+----------+-----------+--------+
| 100 | Dima | Sales | 5000 |
| 200 | AndreyEx | IT | 5.500 |
| 300 | Boris | IT | 7.0 |
| 400 | Anna | Marketing | 9.5 |
| 500 | Anton | IT | 951 |
| 600 | Vasya | IT | 70 |
+-----+----------+-----------+--------+

mysql> select * from worker where name > 5;

Empty set, 6 warnings (0,00 sec)

mysql> select * from worker where name < 5;

+-----+----------+-----------+--------+
| id | name | dept | salary |
+-----+----------+-----------+--------+
| 100 | Dima | Sales | 5000 |
| 200 | AndreyEx | IT | 5.500 |
| 300 | Boris | IT | 7.0 |
| 400 | Anna | Marketing | 9.5 |
| 500 | Anton | IT | 951 |
| 600 | Vasya | IT | 70 |
+-----+----------+-----------+--------+
6 rows in set, 6 warnings (0,00 sec)

mysql> select * from worker where name = 0;

+-----+----------+-----------+--------+
| id | name | dept | salary |
+-----+----------+-----------+--------+
| 100 | Dima | Sales | 5000 |
| 200 | AndreyEx | IT | 5.500 |
| 300 | Boris | IT | 7.0 |
| 400 | Anna | Marketing | 9.5 |
| 500 | Anton | IT | 951 |
| 600 | Vasya | IT | 70 |
+-----+----------+-----------+--------+

mysql> select * from worker where name > 0;

Empty set, 6 warnings (0,00 sec)

mysql> select * from worker where name = 1;

Empty set, 6 warnings (0,00 sec)

Всем, кто хочет попробовать самостоятельно:

mysql -u root -p
create database test;
use test;

### Создаём таблицу:
create table worker (
id INT AUTO_INCREMENT PRIMARY KEY,
name varchar(20),
dept varchar(10),
salary varchar(10)
);

### Наполняем данными:
insert into worker values(100,'Dima','Sales','5000');
insert into worker values(200,'AndreyEx','IT','5.500');
insert into worker values(300,'Boris','IT','7.0');
insert into worker values(400,'Anna','Marketing','9.5');
insert into worker values(500,'Anton','IT','951');
insert into worker values(600,'Vasya','IT','70');

Vladislav

... -= - <<< - >>> - =-
--- GoldED+/LNX 1.1.5-b20170303 by ASA

Vladislav Vetrov написал(а) к Semen Panevin в Oct 18 23:51:16 по местному времени:

Нello Semen!

18 окт 18 08:20, you wrote to me:

SP> Доброго здоровьица тебе, Vladislav!
SP>
SP> Wednesday October 10 2018 22:45, Vladislav Vetrov писал All:
SP>
VV>> Но оператор select работает нормально (в поле name char-текст "55.5"):
SP>
VV>> SELECT * FROM `new_table` WНERE name > 54.2;
SP>
VV>> Ответ:
VV>> id name
VV>> 1 55.5
SP>
SP> А попробуй ради спортивного интереса
SP> select * from new_table where name > 55.45

Ещё раз:

mysql> select * from worker where name < 0;

Empty set, 6 warnings (0,00 sec)

mysql> select * from worker where name > 0;

Empty set, 6 warnings (0,00 sec)

mysql> select * from worker where name = 0;

+-----+----------+-----------+--------+
| id | name | dept | salary |
+-----+----------+-----------+--------+
| 100 | Dima | Sales | 5000 |
| 200 | AndreyEx | IT | 5.500 |
| 300 | Boris | IT | 7.0 |
| 400 | Anna | Marketing | 9.5 |
| 500 | Anton | IT | 951 |
| 600 | Vasya | IT | 70 |
+-----+----------+-----------+--------+

Интересно, почему? :)


Vladislav

... -= - <<< - >>> - =-
--- GoldED+/LNX 1.1.5-b20170303 by ASA

Semen Panevin написал(а) к Vladislav Vetrov в Oct 18 10:55:40 по местному времени:

Доброго здоровьица тебе, Vladislav!

Thursday October 18 2018 23:34, Vladislav Vetrov писал Semen Panevin:

VV>>> Но оператор select работает нормально (в поле name char-текст
VV>>> "55.5"):
SP>>
VV>>> SELECT * FROM `new_table` WНERE name > 54.2;
SP>>
VV>>> Ответ:
VV>>> id name
VV>>> 1 55.5
SP>>
SP>> А попробуй ради спортивного интереса
SP>> select * from new_table where name > 55.45

mysql>> select * from worker where name < 5;
И причём тут name < 5? У тебя вообще в name чисел нет.
Я привёл пример запроса, который на приведённых тобой выше данных (конкретно - name = "55.5") должен был показать разницу в строковой сортировке. А ты в ответ на моё предложение запускаешь совсем другие запросы на совсем других данных.

С наилучшими пожеланиями, Семён.

... Учиться, учиться и учиться! (с) Ленин
--- GoldED+/LNX 1.1.5-b20170303 (Linux 4.1.12-gentoo iF6M10)

Vladislav Vetrov написал(а) к Semen Panevin в Oct 18 21:22:50 по местному времени:

Нello Semen!

20 окт 18 10:55, you wrote to me:

SP> И причём тут name < 5? У тебя вообще в name чисел нет.
SP> Я привёл пример запроса, который на приведённых тобой выше данных (конкретно
SP> - name = "55.5") должен был показать разницу в строковой сортировке. А ты в
SP> ответ на моё предложение запускаешь совсем другие запросы на совсем других
SP> данных.

Прости я не понял. Просто у меня два примера были, я спутал со вторым. Твоя просьба относилась к первому. Попробую, напишу.


Vladislav

... -= - <<< - >>> - =-
--- GoldED+/LNX 1.1.5-b20170303 by ASA