У меня есть строки версии прошивки в моей таблице (например, "4.2.2" или "4.2.16" )
Как я могу сравнивать, выбирать или сортировать их?
Я не могу использовать сравнение стандартных строк: "4.2.2" видно по SQL больше, чем "4.2.16"
Как строки версии, я хотел бы, чтобы 4.2.16 было больше 4.2.2
Я хотел бы рассмотреть, что версия прошивки может содержать в себе символы: 4.24a1, 4.25b3... для этого, как правило, подполе с символами имеет фиксированную длину.
как действовать?
Наконец, я нашел другой способ сортировки строк версии.
Я просто оправдываю строку перед хранением в базе данных таким образом, чтобы она была сортируемой. Поскольку я использую фреймворк Django python, я просто создал VersionField, который "кодирует" строку версии при хранении и "декодирует" ее во время чтения, так что она полностью прозрачна для приложения:
Здесь мой код:
The justify function :
def vjust(str,level=5,delim='.',bitsize=6,fillchar=' '):
"""
1.12 becomes : 1. 12
1.1 becomes : 1. 1
"""
nb = str.count(delim)
if nb < level:
str += (level-nb) * delim
return delim.join([ v.rjust(bitsize,fillchar) for v in str.split(delim)[:level+1] ])
The django VersionField :
class VersionField(models.CharField) :
description = 'Field to store version strings ("a.b.c.d") in a way it is sortable'
__metaclass__ = models.SubfieldBase
def get_prep_value(self, value):
return vjust(value,fillchar=' ')
def to_python(self, value):
return re.sub('\.+$','',value.replace(' ',''))
Если все ваши номера версий выглядят как любой из них:
X
X.X
X.X.X
X.X.X.X
где X - целое число от 0 до 255 (включительно), вы можете использовать функцию INET_ATON() для преобразования строк в целые числа, подходящие для сравнения.
Прежде чем применить эту функцию, вам нужно убедиться, что аргумент функции имеет форму X.X.X.X, добавив к ней необходимое количество '.0'. Для этого вам сначала нужно узнать, сколько . содержит уже содержащуюся строку, что можно сделать следующим образом:
CHAR_LENGTH(ver) - CHAR_LENGTH(REPLACE(ver, '.', '')
То есть количество периодов в строке - это длина строки минус ее длина после удаления периодов.
Полученный результат затем вычитается из 3 и вместе с '.0' передается функции REPEAT():
REPEAT('.0', 3 - CHAR_LENGTH(ver) + CHAR_LENGTH(REPLACE(ver, '.', ''))
Это даст нам подстроку, которая должна быть добавлена к исходному значению ver, чтобы соответствовать формату X.X.X.X. Таким образом, он, в свою очередь, будет передан функции CONCAT() вместе с ver. И результат этого CONCAT() теперь можно напрямую передать в INET_ATON(). Итак, вот что мы получим в итоге:
INET_ATON(
CONCAT(
ver,
REPEAT(
'.0',
3 - CHAR_LENGTH(ver) + CHAR_LENGTH(REPLACE(ver, '.', ''))
)
)
)
И это только для одного значения!:) Аналогичное выражение должно быть построено для другой строки, после чего вы можете сравнить результаты.
Литература:
Предполагая, что число групп равно 3 или меньше, вы можете рассматривать номер версии как два десятичных числа и сортировать ее соответствующим образом. Вот как:
SELECT
ver,
CAST(
SUBSTRING_INDEX(ver, '.', 2)
AS DECIMAL(6,3)
) AS ver1, -- ver1 = the string before 2nd dot
CAST(
CASE
WHEN LOCATE('.', ver) = 0 THEN NULL
WHEN LOCATE('.', ver, LOCATE('.', ver)+1) = 0 THEN SUBSTRING_INDEX(ver, '.', -1)
ELSE SUBSTRING_INDEX(ver, '.', -2)
END
AS DECIMAL(6,3)
) AS ver2 -- ver2 = if there is no dot then 0.0
-- else if there is no 2nd dot then the string after 1st dot
-- else the string after 1st dot
FROM
(
SELECT '1' AS ver UNION
SELECT '1.1' UNION
SELECT '1.01' UNION
SELECT '1.01.03' UNION
SELECT '1.01.04' UNION
SELECT '1.01.1' UNION
SELECT '1.11' UNION
SELECT '1.2' UNION
SELECT '1.2.0' UNION
SELECT '1.2.1' UNION
SELECT '1.2.11' UNION
SELECT '1.2.2' UNION
SELECT '2.0' UNION
SELECT '2.0.1' UNION
SELECT '11.1.1'
) AS sample
ORDER BY ver1, ver2
Вывод:
ver ver1 ver2
======= ====== ======
1 1.000 (NULL)
1.01 1.010 1.000
1.01.03 1.010 1.030
1.01.04 1.010 1.040
1.01.1 1.010 1.100
1.1 1.100 1.000
1.11 1.110 11.000
1.2.0 1.200 2.000
1.2 1.200 2.000
1.2.1 1.200 2.100
1.2.11 1.200 2.110
1.2.2 1.200 2.200
2.0 2.000 0.000
2.0.1 2.000 0.100
11.1.1 11.100 1.100
Примечания:
DECIMAL(6,3). Если вы ожидаете более трех цифр в младших номерах версий, то соответствующим образом изменяйте их.Это довольно сложно, поскольку SQL не предназначен для разделения нескольких значений из одного поля - это нарушение Первая нормальная форма. Предполагая, что у вас не будет более трех групп чисел, каждая из которых не должна превышать трех цифр, попробуйте:
cast(substring_index(concat(X,'.0.0.'), '.', 1) as float) * 1000000 +
cast(substring_index(substring_index(concat(X,'.0.0.'), '.', 2), '.', -1) as float) * 1000 +
cast(substring_index(substring_index(concat(X,'.0.0.'), '.', 3), '.', -1) as float)
Python может сравнивать списки по элементам точно так, как вы хотите сравнить версии, поэтому вы можете просто разделить на ".", вызвать int (x) для каждого элемента (со списком) для преобразовать строку в int, а затем сравнить
>>> v1_3 = [ int(x) for x in "1.3".split(".") ]
>>> v1_2 = [ int(x) for x in "1.2".split(".") ]
>>> v1_12 = [ int(x) for x in "1.12".split(".") ]
>>> v1_3_0 = [ int(x) for x in "1.3.0".split(".") ]
>>> v1_3_1 = [ int(x) for x in "1.3.1".split(".") ]
>>> v1_3
[1, 3]
>>> v1_2
[1, 2]
>>> v1_12
[1, 12]
>>> v1_3_0
[1, 3, 0]
>>> v1_3_1
[1, 3, 1]
>>> v1_2 < v1_3
True
>>> v1_12 > v1_3
True
>>> v1_12 > v1_3_0
True
>>> v1_12 > v1_3_1
True
>>> v1_3_1 < v1_3
False
>>> v1_3_1 < v1_3_0
False
>>> v1_3_1 > v1_3_0
True
>>> v1_3_1 > v1_12
False
>>> v1_3_1 < v1_12
True
>>>
Здесь много хороших решений, но мне нужна хранимая функция, которая будет работать с ORDER BY
CREATE FUNCTION standardize_version(version VARCHAR(255)) RETURNS varchar(255) CHARSET latin1 DETERMINISTIC NO SQL
BEGIN
DECLARE tail VARCHAR(255) DEFAULT version;
DECLARE head, ret VARCHAR(255) DEFAULT NULL;
WHILE tail IS NOT NULL DO
SET head = SUBSTRING_INDEX(tail, '.', 1);
SET tail = NULLIF(SUBSTRING(tail, LOCATE('.', tail) + 1), tail);
SET ret = CONCAT_WS('.', ret, CONCAT(REPEAT('0', 3 - LENGTH(CAST(head AS UNSIGNED))), head));
END WHILE;
RETURN ret;
END|
для проверки:
SELECT standardize_version(version) FROM (SELECT '1.2.33.444.5b' AS version UNION SELECT '1' UNION SELECT NULL) AS t;
отображает:
00001.00002.00033.00444.00005b
00001
(null)
И позволяет сравнивать практически любой набор версий, даже с буквами.
Я искал то же самое и вместо этого закончил это, но остался в mysql:
используя этот оператор
case when version is null then null
when '' then 0
else
preg_replace( '/[^.]*([^.]{10})[.]+/', '$1',
preg_replace('/([^".,\\/_ ()-]+)([".,\\/_ ()-]*)/','000000000$1.',
preg_replace('/(?<=[0-9])([^".,\\/_ ()0-9-]+)/','.!$1',version
)))
end
Я сломаю то, что это значит:
preg_replace - это функция, созданная библиотекой UDF. Поскольку это UDF, вы можете просто называть его любым пользователем или dbspace, подобным этому.^".,\\/_ () прямо сейчас я рассматриваю все эти символы как разделители или традиционные "точки" в версииpreg_replace('/(?<=[0-9])([^".,\\/_ ()0-9-]+)/','.!$1',version) означает замену всех не "точек" и не чисел, которым предшествует число, которому предшествует "точка" и восклицательный знак.preg_replace('/([^".,\\/_ ()-]+)([".,\\/_ ()-]*)/','000000000$1.', ...) означает дополнительно заменить все "точки" фактическими точками и заполнить все цифры с помощью 9 нулей. Также любые смежные точки будут уменьшены до 1.preg_replace( '/0*([^.]{10})[.]+/', '$1', ... ) означает, что дополнительно блокировать все числовые блоки до 10 цифр и сохранять как можно больше блоков. Я хотел заставить 6 блоков сохранить его под 64-байтами, но 7 блоков были на удивление распространены и, следовательно, были необходимы для моей точности. Также необходимы блоки из 10, поэтому 7 блоков из 9 не были вариантом. Но переменная длина работает хорошо для меня. - помните, что строки сравниваются слева направо.Итак, теперь я могу обрабатывать версии вроде:
1.2 < 1.10
1.2b < 1.2.0
1.2a < 1.2b
1.2 = 1.2.0
1.020 = 1.20
11.1.1.3.0.100806.0408.000 < 11.1.1.3.0.100806.0408.001
5.03.2600.2180 (xpsp_sp2_rtm.040803-2158)
A.B.C.D = a.B.C.D
A.A < A.B
Я выбрал восклицательный знак, потому что он сортирует в последовательностях сортировки (который я использую в любом случае) до 0. Это относительный сортировка до 0 позволяет использовать буквы типа b и a, когда они используются сразу же рядом с числом выше, которое должно обрабатываться как новое и сортировать до 0 - это дополнение, которое я использую.
Я использую 0 в качестве дополнения, чтобы продавец ошибся, как переход от фиксированного 3-значного блока к переменной, которую я не укусил.
Вы можете легко выбрать большее количество отступов, если хотите обрабатывать глупые версии, такие как "2.11.0 В разработке (неустойчивый) (2010-03-09)" - строка development составляет 11 байтов.
Вы можете легко запросить больше блоков в окончательной замене.
Я мог бы сделать больше, но я пытался сделать как можно меньше шагов с высокой степенью точности, так как у меня есть несколько миллионов записей для сканирования регулярно. Если кто-нибудь увидит оптимизацию, пожалуйста, repsond.
Я решил сохранить его в виде строки, а не вставлять в число, потому что у каста есть стоимость, а также буквы важны, как мы видели. Одна вещь, о которой я думал, - это тест на строку и возврат опции, которая не так много, или менее дорогая функция для более аккуратных случаев. как 11.1.1.3 является очень распространенным форматом
Это мое решение. Это не зависит от количества подрывных действий.
Например:
select SF_OS_VERSION_COMPARE('2016.10.1712.58','2016.9.1712.58');
возвращает 'HIGH'
select SF_OS_VERSION_COMPARE('2016.10.1712.58','2016.10.1712.58');
возвращает 'EQUAL'
delimiter //
DROP FUNCTION IF EXISTS SF_OS_VERSION_COMPARE //
CREATE FUNCTION SF_OS_VERSION_COMPARE(ver_1 VARCHAR(50), ver_2 VARCHAR(50)) RETURNS VARCHAR(5)
DETERMINISTIC
COMMENT 'Return "HIGH", "LOW" OR "EQUAL" comparing VER_1 with VER_2'
BEGIN
DECLARE v_ver1 VARCHAR(50);
DECLARE v_ver2 VARCHAR(50);
DECLARE v_ver1_num INT;
DECLARE v_ver2_num INT;
SET v_ver1 = ver_1;
SET v_ver2 = ver_2;
WHILE ( v_ver1 <> v_ver2 AND ( v_ver1 IS NOT NULL OR v_ver2 IS NOT NULL )) DO
SET v_ver1_num = CAST(SUBSTRING_INDEX(v_ver1, '.', 1) AS UNSIGNED INTEGER);
SET v_ver2_num = CAST(SUBSTRING_INDEX(v_ver2, '.', 1) AS UNSIGNED INTEGER);
IF ( v_ver1_num > v_ver2_num )
THEN
return 'HIGH';
ELSEIF ( v_ver1_num < v_ver2_num )
THEN
RETURN 'LOW';
ELSE
SET v_ver1 = SUBSTRING(v_ver1,LOCATE('.', v_ver1)+1);
SET v_ver2 = SUBSTRING(v_ver2,LOCATE('.', v_ver2)+1);
END IF;
END WHILE;
RETURN 'EQUAL';
END //
/**
function version_compare(version1, version2)
parameters
version1 first version number.
version2 second version number.
return values
-1: if version1 is less than version2;
1: if version1 is greater than version2,
0: if version1 equal version2.
example:
select version_compare('4.2.2','4.2.16') from dual;
version_compare('4.2.2','4.2.16')
-----------------------------------
-1
*/
drop function if exists version_compare;
delimiter @@
create function version_compare(version1 varchar(100), version2 varchar(100))
returns tinyint
begin
declare v_result tinyint;
declare version1_sub_string varchar(100);
declare version2_sub_string varchar(100);
declare version1_sub_int int;
declare version2_sub_int int;
declare version1_sub_end tinyint;
declare version2_sub_end tinyint;
if version1 = version2 then
set v_result = 0;
else
set version1_sub_string = version1;
set version2_sub_string = version2;
lp1 : loop
set version1_sub_end = locate('.', version1_sub_string);
set version2_sub_end = locate('.', version2_sub_string);
if version1_sub_end <> 0 then
set version1_sub_int = cast(substring(version1_sub_string, 1, version1_sub_end - 1) as signed);
set version1_sub_string = substring(version1_sub_string, version1_sub_end +1 );
else
set version1_sub_int = cast(version1_sub_string as signed);
end if;
if version2_sub_end <> 0 then
set version2_sub_int = cast(substring(version2_sub_string, 1, version2_sub_end - 1) as signed);
set version2_sub_string = substring(version2_sub_string, version2_sub_end + 1);
else
set version2_sub_int = cast(version2_sub_string as signed);
end if;
if version1_sub_int > version2_sub_int then
set v_result = 1;
leave lp1;
elseif version1_sub_int < version2_sub_int then
set v_result = -1;
leave lp1;
else
if version1_sub_end = 0 and version2_sub_end = 0 then
set v_result = 0;
leave lp1;
elseif version1_sub_end = 0 then
set v_result = -1;
leave lp1;
elseif version2_sub_end = 0 then
set v_result = 1;
leave lp1;
end if;
end if;
end loop;
end if;
return v_result;
[email protected]@
delimiter ;