MySQL имеет много типов данных для хранения строковых данных в таблице. VARCHAR и TEXT — два из них. Оба могут хранить максимум 65535 символов, но между этими типами данных, описанными в этом руководстве, есть некоторые различия.

VARCHAR против TEXT:

Существует много различий между типами данных VARCHAR и TEXT. Различия между этими типами данных описаны ниже.

Особенности VARCHAR и TEXT

Типы данных VARCHAR и TEXT по-разному хранят данные в таблице базы данных. Различные особенности этих типов данных описаны ниже.

Декларация VARCHAR и TEXT

Способы объявления типов данных VARCHAR и TEXT объясняются ниже путем создания двух таблиц с полем типов данных VARCHAR и TEXT. Длина требуется для определения типа данных VARCHAR, а длина не требуется для определения типа данных TEXT.

Выполните следующую инструкцию CREATE DATABASE, чтобы создать базу данных с именем test_db.

CREATE DATABASE test_db;

Запустите следующую инструкцию запроса, чтобы выбрать базу данных test_db перед созданием таблицы.

USE test_db;

Выполните следующую инструкцию CREATE TABLE, чтобы создать таблицу клиентов, содержащую пять полей. Здесь тип данных поля id — целое число, а тип данных полей name, email, address и contact_no — varchar, содержащий значение длины.

CREATETABLE customers(

id INTNOT NULL PRIMARY KEY,

name VARCHAR(30) NOT NULL,

email VARCHAR(50),

address VARCHAR(300),

contact_no VARCHAR(15));

Выполните следующую инструкцию DESCRIBE, чтобы проверить структуру таблицы клиентов.

DESCRIBE customers;

Выполните следующий запрос INSERT, чтобы вставить три записи в таблицу клиентов.

INSERT INTO `customers` (`id`, `name`, `email`, `address`, `contact_no`) VALUES ('01', 'Andrey Ex', 'Andrey@gmail.com', '25, aaaaaaaaaaaaaa, Bangladesh ', '0191275634'),

       ('02', 'Sasha Destroyer', 'Sasha@gmail.com', 'bbbbbbbbbbbbbbbbbb', '01855342357'),

       ('03', 'Masha Bubba', 'Masha@hotmail.com', 'cccccccccccccc', NULL);

Запустите следующую инструкцию SELECT, чтобы прочитать все записи таблицы клиентов.

SELECT * FROM customers;

Запустите следующую инструкцию CREATE TABLE, чтобы создать таблицу сотрудников, содержащую пять полей. Тип данных поля id — целое число. Тип данных полей name, email и contact_no — varchar. Тип данных адресного поля — текст. Здесь для поля адреса не объявлено значение длины из-за текстового типа данных.

CREATETABLE employees(

id INTNOT NULL PRIMARY KEY,

name VARCHAR(30) NOT NULL,

email VARCHAR(50),

address TEXT,

contact_no VARCHAR(15));

Выполните следующую инструкцию DESCRIBE, чтобы проверить структуру таблицы сотрудников.

DESCRIBE employees;

Выполните следующий запрос INSERT, чтобы вставить три записи с одинаковым содержимым таблицы клиентов в таблицу сотрудников.

INSERT INTO `employees` (`id`, `name`, `email`, `address`, `contact_no`) VALUES ('01', 'Andrey Ex', 'Andrey@gmail.com', 'aaaaaaaaaaaaaaaaaaaaaaa', '0191275634'),

       ('02', 'Sasha Destroyer', 'Sasha@gmail.com', 'bbbbbbbbbbbbbbbbbbbbbb', '01855342357'),

       ('03', 'Masha Bubba', 'Masha@hotmail.com', 'ccccccccccccccccccc', NULL);

Выполните следующую инструкцию SELECT, чтобы прочитать все записи таблицы сотрудников.

SELECT * FROM employees;

Производительность VARCHAR и TEXT

Ранее уже упоминалось, что тип данных TEXT работает быстрее, чем тип данных VARCHAR. Вы должны выбрать базу данных, содержащую таблицы с полем типа данных VARCHAR и TEXT, и включить профилирование текущего сеанса MySQL, чтобы проверить, какой тип данных быстрее между VARCHAR и TEXT.

База данных была выбрана здесь во время создания таблицы. Таким образом, нам не нужно выбирать его снова. Выполните следующую инструкцию SET, чтобы включить профилирование SESSION.

SET SESSION profiling = 1;

Выполните следующий запрос SELECT, чтобы прочитать все записи таблицы клиентов.

SELECT * FROM customers;

Выполните следующий запрос SELECT, чтобы прочитать все записи таблицы сотрудников.

SELECT * FROM employees;

Выполните следующую команду, чтобы проверить производительность двух предыдущих запросов SELECT.

SHOW PROFILES;

Следующий вывод появится после выполнения команды SHOW PROFILES. Согласно выходным данным, запрос SELECT для таблицы клиентов содержит поле адреса типа данных VARCHAR, требуемое 0,00101000 секунд, а запрос SELECT для таблицы сотрудников, содержащей поле адреса типа данных TEXT, требует 0,00078125 секунд. Это доказывает, что тип данных TEXT работает быстрее, чем тип данных VARCHAR для тех же данных.

Вы можете сравнить выходные данные следующих двух запросов, чтобы подробно проверить производительность типов данных VARCHAR и TEXT.

SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=1;



SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=2;

Вывод:

Оба типа данных VARCHAR и TEXT важны для создания таблиц в базе данных MySQL. Различия между этими типами данных были должным образом объяснены в этом руководстве с примерами, чтобы помочь пользователям MySQL выбрать правильный тип данных поля таблицы во время создания таблицы.

В MySQL существует множество встроенных функций для выполнения различных типов операций со строковыми данными. Иногда требуется разделить строковые данные на основе любого разделителя при выполнении запроса SELECT. Функция SUBSTRING_INDEX() используется для разделения строковых данных, полученных запросом, на основе определенного разделителя. В этой статье обсуждается способ использования этой функции в запросе SELECT.

Синтаксис SUBSTRING_INDEX():

Функция SUBSTRING_INDEX() принимает три аргумента и возвращает значение подстроки. Синтаксис функции SUBSTRING_INDEX() представлен ниже:

string SUBSTRING_INDEX(string, delimiter, count);

• Первый аргумент — это строковое значение, которое будет разделено.
• Второй аргумент — это разделитель, который будет использоваться для разделения строкового значения.
• Третий аргумент определяет количество вхождений разделителя. Оно может быть положительным или отрицательным. Если значение третьего аргумента положительное, то значение подстроки будет возвращено слева. Если значение третьего аргумента отрицательное, значение подстроки будет возвращено справа.

Разделить строку с помощью функции SUBSTRING_INDEX():

В этой части статьи показаны различные варианты использования функции SUBSTRING_INDEX().

Пример 1: Разделить строку на основе положительного значения счетчика

В этой части руководства показаны четыре варианта использования функции SUBSTRING_INDEX() с положительным значением счетчика и другим разделителем.

Запустите следующую инструкцию SELECT, которая использует функцию SUBSTRING_INDEX() с положительным значением счетчика, 1, и пробелом в качестве разделителя. Основная строка «Welcome to AndreyEx» состоит из трех слов. Таким образом, в выводе будет напечатано первое слово строки.

SELECT SUBSTRING_INDEX('Welcome to AndreyEx', ' ', 1);

Запустите следующую инструкцию SELECT, которая использует функцию SUBSTRING_INDEX() с положительным значением счетчика, 2, и символом «o» в качестве разделителя. Основная строка «Welcome to AndreyEx» содержит два раза символ «o». Второй раз «o» появилось во втором слове «to». Таким образом, вывод будет «Welcome to».

SELECT SUBSTRING_INDEX('Welcome to AndreyEx', 'o', 2);

SELECT SUBSTRING_INDEX('Welcome to AndreyEx', 'to', 1);

SELECT SUBSTRING_INDEX('Welcome to AndreyEx', 'to', 3);

SELECT SUBSTRING_INDEX('Welcome to AndreyEx', ' ', -1);

SELECT SUBSTRING_INDEX('Welcome to AndreyEx', 'e', -2);

SELECT SUBSTRING_INDEX('Welcome to AndreyEx', 'in', -2);

CREATE DATABASE test_db;

USE test_db;

CREATE TABLE customers(

id INT NOT NULL PRIMARY KEY,

name VARCHAR(30) NOT NULL,

email VARCHAR(50),

contact_no VARCHAR(15));

INSERT INTO `customers` (`id`, `name`, `email`, `contact_no`) VALUES

('001', 'Andrey Ex', 'info@andreyex.ru', '+8 000 00 00 000'),

('002', 'Maxs Destroyer', 'Max***@yandex.com', '+8 000 00 00 000'),

('003', 'Alex Annihilator', 'Alex***@yandex.com', '+8 000 00 00 000');

SELECT * FROM customers;

SELECT

id, SUBSTRING_INDEX(name,' ',1) AS `First Name`, email, SUBSTRING_INDEX(contact_no,'+88',-1) AS Phone

FROM customers;

Неактивное шифрование предотвращает доступ злоумышленника к зашифрованным данным, хранящимся на диске, даже если у него есть доступ к системе. Базы данных с открытым исходным кодом MySQL и MariaDB теперь поддерживают функцию шифрования в состоянии покоя, которая отвечает требованиям нового законодательства ЕС о защите данных. Шифрование MySQL в состоянии покоя немного отличается от MariaDB, поскольку MySQL обеспечивает шифрование только для таблиц InnoDB. В то время как MariaDB также предоставляет возможность шифровать файлы, такие как журналы повторного выполнения, журналы медленных операций, журналы аудита, журналы ошибок и т. д. Однако оба они не могут зашифровать данные в ОЗУ и защитить их от вредоносного корня.

В этой статье мы научимся настраивать шифрование на уровне базы данных для MariaDB.

Начиная

Для шифрования данных в состоянии покоя требуется плагин шифрования вместе с управлением ключами. Плагин шифрования отвечает за управление ключом шифрования, а также за шифрование/дешифрование данных.

MariaDB предоставляет три решения для управления ключами шифрования, поэтому то, как ваши базы данных управляют ключом шифрования, зависит от используемого вами решения. В этом руководстве будет продемонстрировано шифрование на уровне базы данных с помощью решения MariaDB File Key Management. Однако этот плагин не поддерживает функцию ротации ключей.

Если вы используете сервер LAMP, файлы для добавления этого плагина находятся в каталоге «/opt/lamp». В противном случае изменения вносятся в папку «/etc/mysql/conf.d».

Создание ключей шифрования

Перед шифрованием базы данных с помощью плагина управления ключами файлов нам необходимо создать файлы, содержащие ключи шифрования. Мы создадим файл с двумя частями информации. Это ключ шифрования в шестнадцатеричном формате вместе с 32-битным идентификатором ключа.

Мы создадим новую папку «keys» в каталоге « /etc/mysql/» и воспользуемся утилитой OpenSSL для случайной генерации трех шестнадцатеричных строк и перенаправления вывода в новый файл в папке ключей. Введите следующие команды:

ubuntu@ubuntu:~$ sudo mkdir /etc/mysql/keys

ubuntu@ubuntu:~$ echo -n "1;"$openssl rand hex 32 > /etc/mysql/keys/enc_keys"

ubuntu@ubuntu:~$ echo -n "2;"$openssl rand hex 32 > /etc/mysql/keys/enc_keys"

ubuntu@ubuntu:~$ echo -n "3;"$openssl rand hex 32 > /etc/mysql/keys/enc_keys"

Где 1,2,3 — ключевые идентификаторы; мы включаем их, чтобы создать ссылку на ключи шифрования, используя переменную innodb_default_encryption_key_id в MariaDB. Выходной файл будет выглядеть так:

1;01495ba35e1c9602e14e40bd6de41bb8

2;3cffa4a5d288e90108394dbf639664f8

3;9953297ed1a58ae837486318840f5f1d

ubuntu@ubuntu:~$ openssl rand -hex 192> /etc/mysql/keys/enc_paswd.key

ubuntu@ubuntu:~$ openssl enc -aes-256-cbc -md sha1 -pass file:/etc/mysql/keys/enc_paswd.key -in /etc/mysql/keys/enc_key.txt -out /etc/mysql/keys/enc_key.enc && sudo rm /etc/mysql/keys/enc_key.txt

[sqld]



#File Key Management Plugin

plugin_load_add=file_key_management

file_key_management = ON file_key_management_encryption_algorithm=aes_cbc file_key_management_filename = /etc/mysql/keys/enc_keys.enc

file_key_management_filekey = /etc/mysql/keys/enc_paswd.key



# InnoDB/XtraDB Encryption Setup

innodb_default_encryption_key_id = 1

innodb_encrypt_tables = ON

innodb_encrypt_log = ON

innodb_encryption_threads = 4



# Aria Encryption Setup

aria_encrypt_tables = ON



# Temp & Log Encryption

encrypt-tmp-disk-tables = 1

encrypt-tmp-files = 1

encrypt_binlog = ON

sudo chown -R mysql:root /etc/mysql/keys

sudo chmod 500 /etc/mysql/keys/

sudo chown mysql:root /etc/mysql/keys/enc_paswd.key /etc/mysql/keys/enc_key.enc



sudo chmod 600 /etc/mysql/keys/enc_paswd.key /etc/mysql/keys/enc_key.enc

sudo service mysql restart

Таблица базы данных может хранить различные типы данных, и иногда нам нужно преобразовать данные на уровне строк в данные на уровне столбцов. Эту проблему можно решить с помощью функции PIVOT(). Эта функция используется для поворота строк таблицы в значения столбцов. Но эта функция поддерживается очень немногими серверами баз данных, такими как Oracle или SQL Server. Если вы хотите выполнить ту же задачу в таблице базы данных MySQL, вам необходимо написать запрос SELECT, используя оператор CASE для поворота строк в столбцы. В статье показано, как выполнять задачу функции PIVOT() в связанных таблицах базы данных MySQL.

Предпосылка:

Вам необходимо создать базу данных и несколько связанных таблиц, в которых строки одной таблицы будут преобразованы в столбцы, такие как функция PIVOT(). Выполните следующие операторы SQL, чтобы создать базу данных с именем unidb и создать три таблицы с именами ‘students’, ‘courses’ и ‘result’. студенты и таблицы результатов будут связаны отношением «один ко многим», а таблицы курсов и результатов будут связаны здесь отношением «один ко многим». Запрос CREATE таблицы результатов содержит два ограничения внешнего ключа для полей, std_id и course_id.

CREATE DATABASE unidb;

USE unidb;



CREATE TABLE students (

id INT PRIMARY KEY,

name varchar(50) NOT NULL,

department VARCHAR(15) NOT NULL);



CREATE TABLE courses (

course_id VARCHAR(20) PRIMARY KEY,

name varchar(50) NOT NULL,

credit SMALLINT NOT NULL);



CREATE TABLE result(

std_id INT NOT NULL,

course_id VARCHAR(20) NOT NULL,

mark_type VARCHAR(20) NOT NULL,

marks SMALLINT NOT NULL,

FOREIGN KEY (std_id) REFERENCES students(id),

FOREIGN KEY (course_id) REFERENCES courses(course_id),

PRIMARY KEY (std_id, course_id, mark_type));

Вставьте некоторые записи в студентов, курсы и таблицы результатов. Значения должны быть вставлены в таблицы в соответствии с ограничениями, установленными во время создания таблицы.

INSERT INTO students VALUES

( '1937463', 'AndreyEx', 'CSE'),

( '1937464', 'Destroyer', 'CSE'),

( '1937465', 'Alex', 'CSE'),





INSERT INTO courses VALUES

( 'CSE-401', 'Объектно-ориентированное программирование', 3),

( 'CSE-403', 'Структура данных', 2),

( 'CSE-407', 'Программирование Unix', 2);



INSERT INTO result VALUES

( '1937463', 'CSE-401','Внутренний экзамен' ,15),

( '1937463', 'CSE-401','Промежуточный экзамен' ,20),

( '1937463', 'CSE-401','Итоговый экзамен', 35),

SELECT * FROM result;

SELECT result.std_id, result.course_id,

MAX(CASE WHEN result.mark_type = "Внутренний экзамен" THEN result.marks END) "Внутренний экзамен",

MAX(CASE WHEN result.mark_type = "Промежуточный экзамен" THEN result.marks END) "Промежуточный экзамен",

MAX(CASE WHEN result.mark_type = "Итоговый экзамен" THEN result.marks END) "Итоговый экзамен"

FROM result

GROUP BY result.std_id, result.course_id

ORDER BY result.std_id, result.course_id ASC;

SELECT result.std_id,result.course_id,

MAX(CASE WHEN result.mark_type = "Внутренний экзамен" THEN result.marks END) "Внутренний экзамен",

MAX(CASE WHEN result.mark_type = "Промежуточный экзамен" THEN result.marks END) "Промежуточный экзамен",

MAX(CASE WHEN result.mark_type = "Итоговый экзамен" THEN result.marks END) "Итоговый экзамен",

SUM( result.marks) as Total

FROM result

GROUP BY result.std_id, result.course_id

ORDER BY result.std_id, result.course_id ASC;

SELECT students.name as `Student Name` , courses.name as `Course Name`,

MAX(CASE WHEN result.mark_type = "Внутренний экзамен" THEN result.marks END) "Вн",

MAX(CASE WHEN result.mark_type = "Промежуточный экзамен" THEN result.marks END) "Пр",

MAX(CASE WHEN result.mark_type = "Итоговый экзамен" THEN result.marks END) "Финал",

SUM( result.marks) as Total

FROM students, courses, result

WHERE result.std_id = students.id and result.course_id= courses.course_id

GROUP BY result.std_id, result.course_id

ORDER BY result.std_id, result.course_id ASC;