ืฆื•ืจื™ืงืงืจื™ื’ืŸ ื“ืึทื˜ืŸ ืคื•ืŸ XtraDB ื˜ื™ืฉืŸ ืึธืŸ ืึท ืกื˜ืจื•ืงื˜ื•ืจ ื˜ืขืงืข ื ื™ืฆืŸ ื‘ื™ื™ื˜-ื‘ื™ื™-ื‘ื™ื˜ืข ืึทื ืึทืœื™ืกื™ืก ืคื•ืŸ ื“ื™ ibd ื˜ืขืงืข

ืฆื•ืจื™ืงืงืจื™ื’ืŸ ื“ืึทื˜ืŸ ืคื•ืŸ XtraDB ื˜ื™ืฉืŸ ืึธืŸ ืึท ืกื˜ืจื•ืงื˜ื•ืจ ื˜ืขืงืข ื ื™ืฆืŸ ื‘ื™ื™ื˜-ื‘ื™ื™-ื‘ื™ื˜ืข ืึทื ืึทืœื™ืกื™ืก ืคื•ืŸ ื“ื™ ibd ื˜ืขืงืข

ืคึผืจืขื”ื™ืกื˜ืึธืจื™

ืขืก ืื™ื– ืึทื–ื•ื™ ื’ืขืฉืขืŸ ืึทื– ื“ืขืจ ืกืขืจื•ื•ืขืจ ืื™ื– ืึทื˜ืึทืงื™ืจื˜ ื’ืขื•ื•ืืจืŸ ื“ื•ืจืš ืึท ืจืึทื ืกืึธืžื•ื•ืึทืจืข ื•ื•ื™ืจื•ืก, ื•ื•ืึธืก, ื“ื•ืจืš ืึท "ืžืึทื–ืœื“ื™ืง ืฆื•ืคืึทืœ," ื˜ื™ื™ืœ ืœื™ื ืงืก ื“ื™ .ื™ื‘ื“ ื˜ืขืงืขืก (ืจื•ื™ ื“ืึทื˜ืŸ ื˜ืขืงืขืก ืคื•ืŸ ื™ื ืึธื“ื‘ ื˜ื™ืฉืŸ) ืึทื ื˜ืึทื˜ืฉื˜, ืึธื‘ืขืจ ืื™ืŸ ื“ืขืจ ื–ืขืœื‘ื™ืงืขืจ ืฆื™ื™ื˜ ื’ืึธืจ ื™ื ืงืจื™ืคึผื˜ื™ื“ ื“ื™ .fpm ื˜ืขืงืขืก ( ืกื˜ืจื•ืงื˜ื•ืจ ื˜ืขืงืขืก). ืื™ืŸ ื“ืขื ืคืึทืœ, .idb ืงืขืŸ ื–ื™ื™ืŸ ืฆืขื˜ื™ื™ืœื˜ ืื™ืŸ:

  • ืื•ื ื˜ืขืจื˜ืขื ื™ืง ืฆื• ืจืขืกื˜ืขืจื™ื™ืฉืึทืŸ ื“ื•ืจืš ื ืึธืจืžืึทืœ ืžื›ืฉื™ืจื™ื ืื•ืŸ ืคื™ืจืขืจ. ืคึฟืึทืจ ืึทื–ืึท ืงืึทืกืขืก, ืขืก ืื™ื– ืึท ื•ื™ืกื’ืขืฆื™ื™ื›ื ื˜ ื•ื•ืขืจืŸ;
  • ื˜ื™ื™ืœ ื™ื ืงืจื™ืคึผื˜ื™ื“ ื˜ื™ืฉืŸ. ืžืขืจืกื˜ื ืก ื“ืึธืก ื–ืขื ืขืŸ ื’ืจื•ื™ืก ื˜ื™ืฉืŸ, ืคึฟืึทืจ ื•ื•ืึธืก (ื•ื•ื™ ืื™ืš ืคึฟืึทืจืฉื˜ื™ื™ืŸ) ื“ื™ ืึทื˜ืึทืงืขืจื– ื”ืึธื‘ืŸ ื ื™ืฉื˜ ื’ืขื ื•ื’ ื‘ืึทืจืึทืŸ ืคึฟืึทืจ ืคื•ืœ ืขื ืงืจื™ืคึผืฉืึทืŸ;
  • ื ื•, ื’ืึธืจ ื™ื ืงืจื™ืคึผื˜ื™ื“ ื˜ื™ืฉืŸ ื•ื•ืึธืก ืงืขื ืขืŸ ื ื™ื˜ ื–ื™ื™ืŸ ื’ืขื–ื•ื ื˜.

ืขืก ืื™ื– ืžืขื’ืœืขืš ืฆื• ื‘ืึทืฉืœื™ืกืŸ ื•ื•ืึธืก ืึธืคึผืฆื™ืข ื“ื™ ื˜ื™ืฉืŸ ื’ืขื”ืขืจืŸ ืฆื• ื“ื•ืจืš ืคืฉื•ื˜ ืขืคืŸ ืขืก ืื™ืŸ ืงื™ื™ืŸ ื˜ืขืงืกื˜ ืจืขื“ืึทืงื˜ืึธืจ ืื•ื ื˜ืขืจ ื“ืขืจ ื’ืขื•ื•ืืœื˜ ืงืึธื“ื™ืจื•ื ื’ (ืื™ืŸ ืžื™ื™ืŸ ืคืึทืœ ืขืก ืื™ื– UTF8) ืื•ืŸ ืคืฉื•ื˜ ื–ืขืŸ ื“ื™ ื˜ืขืงืข ืคึฟืึทืจ ื“ื™ ื‘ื™ื™ึทื–ื™ื™ึทืŸ ืคื•ืŸ ื˜ืขืงืกื˜ ืคืขืœื“ืขืจ, ืœืžืฉืœ:

ืฆื•ืจื™ืงืงืจื™ื’ืŸ ื“ืึทื˜ืŸ ืคื•ืŸ XtraDB ื˜ื™ืฉืŸ ืึธืŸ ืึท ืกื˜ืจื•ืงื˜ื•ืจ ื˜ืขืงืข ื ื™ืฆืŸ ื‘ื™ื™ื˜-ื‘ื™ื™-ื‘ื™ื˜ืข ืึทื ืึทืœื™ืกื™ืก ืคื•ืŸ ื“ื™ ibd ื˜ืขืงืข

ืื•ื™ืš, ืื™ืŸ ื“ื™ ืึธื ื”ื™ื™ื‘ ืคื•ืŸ ื“ืขืจ ื˜ืขืงืข ืื™ืจ ืงืขื ืขืŸ ืึธื‘ืกืขืจื•ื•ื™ืจืŸ ืึท ื’ืจื•ื™ืก ื ื•ืžืขืจ ืคื•ืŸ 0 ื‘ื™ื˜ืขืก, ืื•ืŸ ื•ื•ื™ืจื•ืกืขืก ื•ื•ืึธืก ื ื•ืฆืŸ ื“ื™ ื‘ืœืึธืง ืขื ืงืจื™ืคึผืฉืึทืŸ ืึทืœื’ืขืจื™ื“ืึทื (ื“ื™ ืžืขืจืกื˜ ืคึผืจืึธืกื˜) ื™ื•ื–ืฉืึทื•ื•ืึทืœื™ ื•ื•ื™ืจืงืŸ ื–ื™ื™ ืื•ื™ืš.
ืฆื•ืจื™ืงืงืจื™ื’ืŸ ื“ืึทื˜ืŸ ืคื•ืŸ XtraDB ื˜ื™ืฉืŸ ืึธืŸ ืึท ืกื˜ืจื•ืงื˜ื•ืจ ื˜ืขืงืข ื ื™ืฆืŸ ื‘ื™ื™ื˜-ื‘ื™ื™-ื‘ื™ื˜ืข ืึทื ืึทืœื™ืกื™ืก ืคื•ืŸ ื“ื™ ibd ื˜ืขืงืข

ืื™ืŸ ืžื™ื™ืŸ ืคืึทืœ, ื“ื™ ืึทื˜ืึทืงืขืจื– ืœื™ื ืงืก ืึท 4-ื‘ื™ื˜ืข ืฉื˜ืจื™ืงืœ (1, 0, 0, 0) ืื™ืŸ ื“ื™ ืกื•ืฃ ืคื•ืŸ ื™ืขื“ืขืจ ื™ื ืงืจื™ืคึผื˜ื™ื“ ื˜ืขืงืข, ื•ื•ืึธืก ืกื™ืžืคึผืœืึทืคื™ื™ื“ ื“ื™ ืึทืจื‘ืขื˜. ืฆื• ื–ื•ื›ืŸ ืคึฟืึทืจ ืึทื ื™ื ืคืขืงื˜ืึทื“ ื˜ืขืงืขืก, ื“ื™ ืฉืจื™ืคื˜ ืื™ื– ื’ืขื ื•ื’:

def opened(path):
    files = os.listdir(path)
    for f in files:
        if os.path.isfile(path + f):
            yield path + f

for full_path in opened("C:somepath"):
    file = open(full_path, "rb")
    last_string = ""
    for line in file:
        last_string = line
        file.close()
    if (last_string[len(last_string) -4:len(last_string)]) != (1, 0, 0, 0):
        print(full_path)

ืื–ื•ื™, ืขืก ืคืืจืงืขืจื˜ ืื•ื™ืก ืฆื• ื’ืขืคึฟื™ื ืขืŸ ื˜ืขืงืขืก ื‘ื™ืœืึธื ื’ื™ื ื’ ืฆื• ื“ืขืจ ืขืจืฉื˜ืขืจ ื˜ื™ืคึผ. ื“ื™ ืจื’ืข ื™ื ื•ื•ืึทืœื•ื•ื– ืึท ืคึผืœืึทืฅ ืคื•ืŸ ืžืึทื ื•ืึทืœ ืึทืจื‘ืขื˜, ืึธื‘ืขืจ ื•ื•ืึธืก ืื™ื– ื’ืขืคื•ื ืขืŸ ืื™ื– ืฉื•ื™ืŸ ื’ืขื ื•ื’. ืึทืœืฅ ื•ื•ืึธืœื˜ ื–ื™ื™ืŸ ื’ื•ื˜, ืึธื‘ืขืจ ืื™ืจ ื“ืึทืจืคึฟืŸ ืฆื• ื•ื•ื™ืกืŸ ืœืขื’ืึทืžืจืข ื’ืขื ื•ื™ ืกื˜ืจื•ืงื˜ื•ืจ ืื•ืŸ (ืคืึทืจืฉื˜ื™ื™ื˜ ื–ื™ืš) ืึท ืคืึทืœ ืื™ื– ืื•ื™ืคื’ืขืฉื˜ืื ืขืŸ ืึทื– ืื™ืš ื’ืขื”ืื˜ ืฆื• ืึทืจื‘ืขื˜ืŸ ืžื™ื˜ ืึท ืึธืคื˜ ื˜ืฉืึทื ื’ื™ื ื’ ื˜ื™ืฉ. ืงื™ื™ื ืขืจ ื”ืึธื˜ ื ื™ืฉื˜ ื’ืขื“ืขื ืงื˜ ืฆื™ ื“ืขืจ ืคืขืœื“ ื˜ื™ืคึผ ืื™ื– ื’ืขื‘ื™ื˜ืŸ ืึธื“ืขืจ ืึท ื ื™ื™ึทืข ื–ื™ื™ึทืœ ืื™ื– ืฆื•ื’ืขื’ืขื‘ืŸ.

ื•ื•ื™ืœื“ืก ืกื™ื˜ื™ ื”ืื˜ ืœื™ื™ื“ืขืจ ื ื™ืฉื˜ ื’ืขืงืขื ื˜ ื”ืขืœืคืŸ ืžื™ื˜ ืื–ื ืคืืœ, ื“ืขืจืคืืจ ื•ื•ืขืจื˜ ื’ืขืฉืจื™ื‘ืŸ ื“ืขืจ ืืจื˜ื™ืงืœ.

ื‘ืึทืงื•ืžืขืŸ ืฆื• ื“ืขื ืคื•ื ื˜

ืขืก ืื™ื– ืึท ืกื˜ืจื•ืงื˜ื•ืจ ืคื•ืŸ ืึท ื˜ื™ืฉ ืคื•ืŸ 3 ื—ื“ืฉื™ื ืฆื•ืจื™ืง ื•ื•ืึธืก ืื™ื– ื ื™ืฉื˜ ืฆื•ื ื•ื™ืคืคืึทืœืŸ ืžื™ื˜ ื“ื™ ืงืจืึทื ื˜ (ืขืคืฉืขืจ ืื™ื™ืŸ ืคืขืœื“, ืื•ืŸ ืขืคืฉืขืจ ืžืขืจ). ื˜ื™ืฉ ืกื˜ืจื•ืงื˜ื•ืจ:

CREATE TABLE `table_1` (
    `id` INT (11),
    `date` DATETIME ,
    `description` TEXT ,
    `id_point` INT (11),
    `id_user` INT (11),
    `date_start` DATETIME ,
    `date_finish` DATETIME ,
    `photo` INT (1),
    `id_client` INT (11),
    `status` INT (1),
    `lead__time` TIME ,
    `sendstatus` TINYINT (4)
); 

ืื™ืŸ ื“ืขื ืคืึทืœ, ืื™ืจ ื“ืึทืจืคึฟืŸ ืฆื• ืขืงืกื˜ืจืึทืงื˜:

  • id_point ื™ื ื˜ (11);
  • id_user ื™ื ื˜ (11);
  • date_start DATETIME;
  • date_finish DATETIME.

ืคึฟืึทืจ ืึธืคึผื–ื•ืš, ืึท ื‘ื™ื˜ืข-ื‘ื™ื™-ื‘ื™ื˜ืข ืึทื ืึทืœื™ืกื™ืก ืคื•ืŸ ื“ื™ .ื™ื‘ื“ ื˜ืขืงืข ืื™ื– ื’ืขื ื™ืฆื˜, ื ืื›ื’ืขื’ืื ื’ืขืŸ ื“ื•ืจืš ืงืึทื ื•ื•ืขืจื˜ื™ื ื’ ื–ื™ื™ ืื™ืŸ ืึท ืžืขืจ ืœื™ื™ื ืขื•ื•ื“ื™ืง ืคืึธืจืขื. ื–ื™ื ื˜ ืฆื• ื’ืขืคึฟื™ื ืขืŸ ื•ื•ืึธืก ืžื™ืจ ื“ืึทืจืคึฟืŸ, ืžื™ืจ ื ืึธืจ ื“ืึทืจืคึฟืŸ ืฆื• ืึทื ืึทืœื™ื™ื– ื“ืึทื˜ืŸ ื˜ื™ื™ืคึผืก ืึทื–ืึท ื•ื•ื™ int ืื•ืŸ datatime, ื“ืขืจ ืึทืจื˜ื™ืงืœ ื•ื•ืขื˜ ื‘ืึทืฉืจื™ื™ึทื‘ืŸ ื‘ืœื•ื™ื– ื–ื™ื™, ืึธื‘ืขืจ ืžืืœ ืžื™ืจ ื•ื•ืขืœืŸ ืื•ื™ืš ืึธืคึผืฉื™ืงืŸ ืฆื• ืื ื“ืขืจืข ื“ืึทื˜ืŸ ื˜ื™ื™ืคึผืก, ื•ื•ืึธืก ืงืขื ืขืŸ ื”ืขืœืคึฟืŸ ืื™ืŸ ืื ื“ืขืจืข ืขื ืœืขืš ื™ื ืกืึทื“ืึทื ืฅ.

ืคึผืจืึธื‘ืœืขื 1: ืคืขืœื“ืขืจ ืžื™ื˜ ื˜ื™ื™ืคึผืก DATETIME ืื•ืŸ TEXT ื”ืึธื‘ืŸ NULL ื•ื•ืึทืœื•ืขืก, ืื•ืŸ ื–ื™ื™ ื–ืขื ืขืŸ ืคืฉื•ื˜ ืกืงื™ืคึผื˜ ืื™ืŸ ื“ืขืจ ื˜ืขืงืข, ื•ื•ื™ื™ึทืœ ืคื•ืŸ ื“ืขื, ืขืก ืื™ื– ื ื™ื˜ ืžืขื’ืœืขืš ืฆื• ื‘ืึทืฉืœื™ืกืŸ ื“ื™ ืกื˜ืจื•ืงื˜ื•ืจ ืฆื• ื•ืžืงืขืจืŸ ืื™ืŸ ืžื™ื™ืŸ ืคืึทืœ. ืื™ืŸ ื“ื™ ื ื™ื™ึทืข ืฉืคืืœื˜ืŸ, ื“ื™ ืคืขืœื™ืงื™ื™ึทื˜ ื•ื•ืขืจื˜ ืื™ื– ื ืึทืœ, ืื•ืŸ ื˜ื™ื™ืœ ืคื•ืŸ ื“ื™ ื˜ืจืึทื ืกืึทืงื˜ื™ืึธืŸ ืงืขืŸ ื–ื™ื™ืŸ ืคืึทืจืคืึทืœืŸ ืจืขื›ื˜ ืฆื• ื“ืขืจ ื‘ืึทืฉื˜ืขื˜ื™ืงืŸ innodb_flush_log_at_trx_commit = 0, ืึทื–ื•ื™ ื ืึธืš ืฆื™ื™ื˜ ื•ื•ืึธืœื˜ ื–ื™ื™ืŸ ืคืืจื‘ืจืื›ื˜ ืฆื• ื‘ืึทืฉืœื™ืกืŸ ื“ื™ ืกื˜ืจื•ืงื˜ื•ืจ.

ืคึผืจืึธื‘ืœืขื 2: ืขืก ื–ืึธืœ ื–ื™ื™ืŸ ื’ืขื ื•ืžืขืŸ ืื™ืŸ ื—ืฉื‘ื•ืŸ ืึทื– ืจืึธื•ื– ืื•ื™ืกื’ืขืžืขืงื˜ ื“ื•ืจืš DELETE ื•ื•ืขื˜ ืึทืœืข ื–ื™ื™ืŸ ืื™ืŸ ื“ื™ ibd ื˜ืขืงืข, ืึธื‘ืขืจ ืžื™ื˜ ALTER TABLE ื–ื™ื™ืขืจ ืกื˜ืจื•ืงื˜ื•ืจ ื•ื•ืขื˜ ื ื™ืฉื˜ ื–ื™ื™ืŸ ื“ืขืจื”ื™ื™ึทื ื˜ื™ืงื˜. ื•ื•ื™ ืึท ืจืขื–ื•ืœื˜ืึทื˜, ื“ื™ ื“ืึทื˜ืŸ ืกื˜ืจื•ืงื˜ื•ืจ ืงืขื ืขืŸ ื‘ื™ื™ึทื˜ืŸ ืคื•ืŸ ื“ื™ ืึธื ื”ื™ื™ื‘ ืคื•ืŸ ื“ื™ ื˜ืขืงืข ืฆื• ื“ื™ ืกื•ืฃ. ืื•ื™ื‘ ืื™ืจ ืึธืคื˜ ื ื•ืฆืŸ ืึธืคึผื˜ื™ืžื™ื–ืข ื˜ื™ืฉ, ืขืก ืื™ื– ืึทื ืœื™ื™ืงืœื™ ืฆื• ื˜ืจืขืคืŸ ืึทื–ืึท ืึท ืคึผืจืึธื‘ืœืขื.

ืฆื•ืงื•ืงื  ื–ื™ืš, ื“ื™ DBMS ื•ื•ืขืจืกื™ืข ืึทืคืขืงืฅ ื“ื™ ื•ื•ืขื’ ื“ืึทื˜ืŸ ื–ืขื ืขืŸ ืกื˜ืึธืจื“, ืื•ืŸ ื“ืขื ื‘ื™ื™ึทืฉืคึผื™ืœ ืงืขืŸ ื ื™ืฉื˜ ืึทืจื‘ืขื˜ืŸ ืคึฟืึทืจ ืื ื“ืขืจืข ื”ื•ื™ืคึผื˜ ื•ื•ืขืจืกื™ืขืก. ืื™ืŸ ืžื™ื™ืŸ ืคืึทืœ, ื“ื™ Windows ื•ื•ืขืจืกื™ืข ืคื•ืŸ โ€‹โ€‹โ€‹โ€‹mariadb 10.1.24 ืื™ื– ื’ืขื ื™ืฆื˜. ืื•ื™ืš, ื›ืึธื˜ืฉ ืื™ืŸ mariadb ืื™ืจ ืึทืจื‘ืขื˜ ืžื™ื˜ InnoDB ื˜ื™ืฉืŸ, ืื™ืŸ ืคืึทืงื˜ ื–ื™ื™ ื–ืขื ืขืŸ XtraDB, ื•ื•ืึธืก ื™ืงืกืงืœื•ื“ื– ื“ื™ ืึธื ื•ื•ืขื ื“ืœืขืš ืคื•ืŸ ื“ืขื ืื•ืคึฟืŸ ืžื™ื˜ InnoDB mysql.

ื˜ืขืงืข ืึทื ืึทืœื™ืกื™ืก

ืื™ืŸ ืคึผื™ื˜ื”ืึธืŸ, ื“ืึทื˜ืŸ ื˜ื™ืคึผ ื‘ื™ื˜ืขืก () ื“ื™ืกืคึผืœื™ื™ื– ืื•ื ื™ืงืึธื“ ื“ืึทื˜ืŸ ืื™ืŸ ืคึผืœืึทืฅ ืคื•ืŸ ืึท ืจืขื’ื•ืœืขืจ ื’ืึทื ื’ ืคื•ืŸ ื ื•ืžืขืจืŸ. ื›ืึธื˜ืฉ ืื™ืจ ืงืขื ืขืŸ ื–ืขืŸ ื“ื™ ื˜ืขืงืข ืื™ืŸ ื“ืขื ืคืึธืจืขื, ืคึฟืึทืจ ืงืึทื ื•ื•ื™ื ื™ืึทื ืก ืื™ืจ ืงืขื ืขืŸ ื‘ื™ื™ึทื˜ืŸ ื“ื™ ื‘ื™ื˜ืขืก ืื™ืŸ ื ื•ืžืขืจื™ืง ืคืึธืจืขื ื“ื•ืจืš ืงืึทื ื•ื•ืขืจื˜ื™ื ื’ ื“ื™ ื‘ื™ื™ื˜ ืžืขื ื’ืข ืื™ืŸ ืึท ืจืขื’ื•ืœืขืจ ืžืขื ื’ืข (ืจืฉื™ืžื” (example_byte_array)). ืื™ืŸ ืงื™ื™ืŸ ืคืึทืœ, ื‘ื™ื™ื“ืข ืžืขื˜ื”ืึธื“ืก ื–ืขื ืขืŸ ืคึผืึทืกื™ืง ืคึฟืึทืจ ืึทื ืึทืœื™ืกื™ืก.

ื ืึธืš ืงื•ืงืŸ ื“ื•ืจืš ืขื˜ืœืขื›ืข ibd ื˜ืขืงืขืก, ืื™ืจ ืงืขื ืขืŸ ื’ืขืคึฟื™ื ืขืŸ ื“ื™ ืคืืœื’ืขื ื“ืข:

ืฆื•ืจื™ืงืงืจื™ื’ืŸ ื“ืึทื˜ืŸ ืคื•ืŸ XtraDB ื˜ื™ืฉืŸ ืึธืŸ ืึท ืกื˜ืจื•ืงื˜ื•ืจ ื˜ืขืงืข ื ื™ืฆืŸ ื‘ื™ื™ื˜-ื‘ื™ื™-ื‘ื™ื˜ืข ืึทื ืึทืœื™ืกื™ืก ืคื•ืŸ ื“ื™ ibd ื˜ืขืงืข

ื“ืขืจืฆื•, ืื•ื™ื‘ ืื™ืจ ื˜ื™ื™ืœืŸ ื“ื™ ื˜ืขืงืข ื“ื•ืจืš ื“ื™ ื˜ืขืจืžื™ื ืขืŸ, ืื™ืจ ื•ื•ืขื˜ ื‘ืึทืงื•ืžืขืŸ ืžืขืจืกื˜ื ืก ืืคื™ืœื• ื‘ืœืึทืงืก ืคื•ืŸ ื“ืึทื˜ืŸ. ืžื™ืจ ื•ื•ืขืœืŸ ื ื•ืฆืŸ ื™ื ืคื™ืžื•ื ื•ื•ื™ ืึท ื“ื™ื•ื•ื™ื–ืขืจ.

table = table.split("infimum".encode())

ืึท ื˜ืฉื™ืงืึทื•ื•ืข ืึธื‘ืกืขืจื•ื•ืึทืฆื™ืข: ืคึฟืึทืจ ื˜ื™ืฉืŸ ืžื™ื˜ ืึท ืงืœื™ื™ืŸ ืกื•ืžืข ืคื•ืŸ โ€‹โ€‹ื“ืึทื˜ืŸ, ืฆื•ื•ื™ืฉืŸ ื™ื ืคื™ืžืึท ืื•ืŸ ื”ืขื›ืกื˜ ืขืก ืื™ื– ืึท ื˜ื™ื™ึทื˜ืœ ืฆื• ื“ื™ ื ื•ืžืขืจ ืคื•ืŸ ืจืึธื•ื– ืื™ืŸ ื“ื™ ื‘ืœืึธืง.

ืฆื•ืจื™ืงืงืจื™ื’ืŸ ื“ืึทื˜ืŸ ืคื•ืŸ XtraDB ื˜ื™ืฉืŸ ืึธืŸ ืึท ืกื˜ืจื•ืงื˜ื•ืจ ื˜ืขืงืข ื ื™ืฆืŸ ื‘ื™ื™ื˜-ื‘ื™ื™-ื‘ื™ื˜ืข ืึทื ืึทืœื™ืกื™ืก ืคื•ืŸ ื“ื™ ibd ื˜ืขืงืข - ืคึผืจื•ื‘ื™ืจืŸ ื˜ื™ืฉ ืžื™ื˜ 1 ืจื•ื“ืขืจืŸ

ืฆื•ืจื™ืงืงืจื™ื’ืŸ ื“ืึทื˜ืŸ ืคื•ืŸ XtraDB ื˜ื™ืฉืŸ ืึธืŸ ืึท ืกื˜ืจื•ืงื˜ื•ืจ ื˜ืขืงืข ื ื™ืฆืŸ ื‘ื™ื™ื˜-ื‘ื™ื™-ื‘ื™ื˜ืข ืึทื ืึทืœื™ืกื™ืก ืคื•ืŸ ื“ื™ ibd ื˜ืขืงืข - ืคึผืจื•ื‘ื™ืจืŸ ื˜ื™ืฉ ืžื™ื˜ 2 ืจืึธื•ื–

ื“ื™ ืจื•ื“ืขืจืŸ ืžืขื ื’ืข ื˜ื™ืฉ [0] ืงืขื ืขืŸ ื–ื™ื™ืŸ ืกืงื™ืคึผื˜. ื ืึธืš ื“ื•ืจื›ืงื•ืงืŸ, ืื™ืš ืื™ื– ื ืึธืš ื ื™ื˜ ื’ืขืงืขื ื˜ ืฆื• ื’ืขืคึฟื™ื ืขืŸ ื“ื™ ืจื•ื™ ื˜ื™ืฉ ื“ืึทื˜ืŸ. ืจื•ื‘ึฟ ืžืกืชึผืžื, ื“ืขื ื‘ืœืึธืง ืื™ื– ื’ืขื ื™ืฆื˜ ืฆื• ืงืจืึธื ื™ื ื“ืขืงืกื™ื– ืื•ืŸ ืฉืœื™ืกืœืขืŸ.
ืกื˜ืึทืจื˜ื™ื ื’ ืžื™ื˜ ื˜ื™ืฉ [1] ืื•ืŸ ืื™ื‘ืขืจื–ืขืฆืŸ ืขืก ืื™ืŸ ืึท ื ื•ืžืขืจื™ืง ืžืขื ื’ืข, ืื™ืจ ืงืขื ืขืŸ ืฉื•ื™ืŸ ื‘ืึทืžืขืจืงืŸ ืขื˜ืœืขื›ืข ืคึผืึทื˜ืขืจื ื–, ื ื™ื™ืžืœื™:

ืฆื•ืจื™ืงืงืจื™ื’ืŸ ื“ืึทื˜ืŸ ืคื•ืŸ XtraDB ื˜ื™ืฉืŸ ืึธืŸ ืึท ืกื˜ืจื•ืงื˜ื•ืจ ื˜ืขืงืข ื ื™ืฆืŸ ื‘ื™ื™ื˜-ื‘ื™ื™-ื‘ื™ื˜ืข ืึทื ืึทืœื™ืกื™ืก ืคื•ืŸ ื“ื™ ibd ื˜ืขืงืข

ื“ืึธืก ื–ืขื ืขืŸ ื™ื ื˜ ื•ื•ืึทืœื•ืขืก ืกื˜ืึธืจื“ ืื™ืŸ ืึท ืฉื˜ืจื™ืงืœ. ื“ืขืจ ืขืจืฉื˜ืขืจ ื‘ื™ื™ื˜ ื™ื ื“ื™ืงื™ื™ืฅ ืฆื™ ื“ื™ ื ื•ืžืขืจ ืื™ื– positive ืึธื“ืขืจ ื ืขื’ืึทื˜ื™ื•ื•. ืื™ืŸ ืžื™ื™ืŸ ืคืึทืœ, ืึทืœืข ื ื•ืžืขืจืŸ ื–ืขื ืขืŸ positive. ืคื•ืŸ ื“ื™ ืจื•ืขืŸ 3 ื‘ื™ื˜ืขืก, ืื™ืจ ืงืขื ืขืŸ ื‘ืึทืฉื˜ื™ืžืขืŸ ื“ื™ ื ื•ืžืขืจ ืžื™ื˜ ื“ื™ ืคืืœื’ืขื ื“ืข ืคื•ื ืงืฆื™ืข. ืฉืจื™ืคื˜:

def find_int(val: str):  # example '128, 1, 2, 3'
    val = [int(v) for v in  val.split(", ")]
    result_int = val[1]*256**2 + val[2]*256*1 + val[3]
    return result_int

ืคึฟืึทืจ ื‘ื™ื™ึทืฉืคึผื™ืœ, 128, 0, 0, 1 = 1ืึธื“ืขืจ 128, 0, 75, 108 = 19308.
ื“ืขืจ ื˜ื™ืฉ ื”ืื˜ ืึท ืขืจืฉื˜ื™ืง ืฉืœื™ืกืœ ืžื™ื˜ ืึทื•ื˜ืึธ ื™ื ืงืจืึทืžืึทื ื˜, ืื•ืŸ ืขืก ืงืขื ืขืŸ ืื•ื™ืš ื–ื™ื™ืŸ ื’ืขืคึฟื•ื ืขืŸ ื“ืึธ

ืฆื•ืจื™ืงืงืจื™ื’ืŸ ื“ืึทื˜ืŸ ืคื•ืŸ XtraDB ื˜ื™ืฉืŸ ืึธืŸ ืึท ืกื˜ืจื•ืงื˜ื•ืจ ื˜ืขืงืข ื ื™ืฆืŸ ื‘ื™ื™ื˜-ื‘ื™ื™-ื‘ื™ื˜ืข ืึทื ืึทืœื™ืกื™ืก ืคื•ืŸ ื“ื™ ibd ื˜ืขืงืข

ื ืึธืš ืงืึทืžืคึผืขืจื“ ื“ื™ ื“ืึทื˜ืŸ ืคื•ืŸ ื“ื™ ืคึผืจืึธื‘ืข ื˜ื™ืฉืŸ, ืขืก ืื™ื– ื’ืขื•ื•ืขืŸ ื’ื™ืœื•ื™ ืึทื– ื“ื™ DATETIME ื›ื™ื™ืคืขืฅ ื‘ืืฉื˜ื™ื™ื˜ ืคื•ืŸ 5 ื‘ื™ื˜ืขืก ืื•ืŸ ืื ื’ืขื”ื•ื™ื‘ืŸ ืžื™ื˜ 153 (ืจื•ื‘ึฟ ืžืกืชึผืžื ื™ื ื“ืึทืงื™ื™ื˜ื™ื ื’ ื™ืขืจืœืขืš ื™ื ื˜ืขืจื•ื•ืึทืœื–). ื–ื™ื ื˜ ื“ื™ DATTIME ืงื™ื™ื˜ ืื™ื– '1000-01-01' ืฆื• '9999-12-31', ืื™ืš ื˜ืจืึทื›ื˜ืŸ ื“ื™ ื ื•ืžืขืจ ืคื•ืŸ ื‘ื™ื˜ืขืก ืงืขืŸ ื‘ื™ื™ึทื˜ืŸ, ืึธื‘ืขืจ ืื™ืŸ ืžื™ื™ืŸ ืคืึทืœ, ื“ื™ ื“ืึทื˜ืŸ ืคืึทืœืŸ ืื™ืŸ ื“ื™ ืฆื™ื™ื˜ ืคื•ืŸ 2016 ืฆื• 2019, ืึทื–ื•ื™ ืžื™ืจ ื•ื•ืขืœืŸ ื™ื‘ืขืจื ืขืžืขืŸ ืึทื– 5 ื‘ื™ื˜ืขืก ื’ืขื ื•ื’.

ืฆื• ื‘ืึทืฉื˜ื™ืžืขืŸ ื“ื™ ืฆื™ื™ื˜ ืึธืŸ ืกืขืงื•ื ื“ืขืก, ื“ื™ ืคืืœื’ืขื ื“ืข ืคืึทื ื’ืงืฉืึทื ื– ื–ืขื ืขืŸ ื’ืขืฉืจื™ื‘ืŸ. ืฉืจื™ืคื˜:

day_ = lambda x: x % 64 // 2  # {x,x,X,x,x }

def hour_(x1, x2):  # {x,x,X1,X2,x}
    if x1 % 2 == 0:
        return x2 // 16
    elif x1 % 2 == 1:
        return x2 // 16 + 16
    else:
        raise ValueError

min_ = lambda x1, x2: (x1 % 16) * 4 + (x2 // 64)  # {x,x,x,X1,X2}

ืขืก ืื™ื– ื ื™ื˜ ืžืขื’ืœืขืš ืฆื• ืฉืจื™ื™ึทื‘ืŸ ืึท ืคืึทื ื’ืงืฉืึทื ืึทืœ ืคื•ื ืงืฆื™ืข ืคึฟืึทืจ ื“ื™ ื™ืึธืจ ืื•ืŸ ื—ื•ื“ืฉ, ืึทื–ื•ื™ ืื™ืš ื”ืื˜ ืฆื• ื›ืึทืง ืขืก. ืฉืจื™ืคื˜:

ym_list = {'2016, 1': '153, 152, 64', '2016, 2': '153, 152, 128', 
           '2016, 3': '153, 152, 192', '2016, 4': '153, 153, 0',
           '2016, 5': '153, 153, 64', '2016, 6': '153, 153, 128', 
           '2016, 7': '153, 153, 192', '2016, 8': '153, 154, 0', 
           '2016, 9': '153, 154, 64', '2016, 10': '153, 154, 128', 
           '2016, 11': '153, 154, 192', '2016, 12': '153, 155, 0',
           '2017, 1': '153, 155, 128', '2017, 2': '153, 155, 192', 
           '2017, 3': '153, 156, 0', '2017, 4': '153, 156, 64',
           '2017, 5': '153, 156, 128', '2017, 6': '153, 156, 192',
           '2017, 7': '153, 157, 0', '2017, 8': '153, 157, 64',
           '2017, 9': '153, 157, 128', '2017, 10': '153, 157, 192', 
           '2017, 11': '153, 158, 0', '2017, 12': '153, 158, 64', 
           '2018, 1': '153, 158, 192', '2018, 2': '153, 159, 0',
           '2018, 3': '153, 159, 64', '2018, 4': '153, 159, 128', 
           '2018, 5': '153, 159, 192', '2018, 6': '153, 160, 0',
           '2018, 7': '153, 160, 64', '2018, 8': '153, 160, 128',
           '2018, 9': '153, 160, 192', '2018, 10': '153, 161, 0', 
           '2018, 11': '153, 161, 64', '2018, 12': '153, 161, 128',
           '2019, 1': '153, 162, 0', '2019, 2': '153, 162, 64', 
           '2019, 3': '153, 162, 128', '2019, 4': '153, 162, 192', 
           '2019, 5': '153, 163, 0', '2019, 6': '153, 163, 64',
           '2019, 7': '153, 163, 128', '2019, 8': '153, 163, 192',
           '2019, 9': '153, 164, 0', '2019, 10': '153, 164, 64', 
           '2019, 11': '153, 164, 128', '2019, 12': '153, 164, 192',
           '2020, 1': '153, 165, 64', '2020, 2': '153, 165, 128',
           '2020, 3': '153, 165, 192','2020, 4': '153, 166, 0', 
           '2020, 5': '153, 166, 64', '2020, 6': '153, 1, 128',
           '2020, 7': '153, 166, 192', '2020, 8': '153, 167, 0', 
           '2020, 9': '153, 167, 64','2020, 10': '153, 167, 128',
           '2020, 11': '153, 167, 192', '2020, 12': '153, 168, 0'}

def year_month(x1, x2):  # {x,X,X,x,x }

    for key, value in ym_list.items():
        key = [int(k) for k in key.replace("'", "").split(", ")]
        value = [int(v) for v in value.split(", ")]
        if x1 == value[1] and x2 // 64 == value[2] // 64:
            return key
    return 0, 0

ืื™ืš ื‘ื™ืŸ ื–ื™ื›ืขืจ ืึทื– ืื•ื™ื‘ ืื™ืจ ืคืึทืจื‘ืจืขื ื’ืขืŸ ืึท ืกื•ืžืข ืคื•ืŸ โ€‹โ€‹โ€‹โ€‹ืฆื™ื™ื˜, ื“ืขื ืžื™ืกืึทื ื“ืขืจืกื˜ืึทื ื“ื™ื ื’ ืงืขื ืขืŸ ื–ื™ื™ืŸ ืงืขืจืขืงื˜ืึทื“.
ื•ื•ื™ื™ึทื˜ืขืจ, ืึท ืคึฟื•ื ืงืฆื™ืข ื•ื•ืึธืก ืงืขืจื˜ ืึท ื“ืึทื˜ืขื˜ื™ืžืข ื›ื™ื™ืคืขืฅ ืคึฟื•ืŸ ืึท ืฉื˜ืจื™ืงืœ. ืฉืจื™ืคื˜:

def find_data_time(val:str):
    val = [int(v) for v in val.split(", ")]
    day = day_(val[2])
    hour = hour_(val[2], val[3])
    minutes = min_(val[3], val[4])
    year, month = year_month(val[1], val[2])
    return datetime(year, month, day, hour, minutes)

ื’ืขืจืื˜ืŸ ืฆื• ื“ืขื˜ืขืงื˜ ืึธืคื˜ ืจื™ืคึผื™ื˜ื™ื“ ื•ื•ืึทืœื•ืขืก ืคื•ืŸ int, int, datetime, datetime ืฆื•ืจื™ืงืงืจื™ื’ืŸ ื“ืึทื˜ืŸ ืคื•ืŸ XtraDB ื˜ื™ืฉืŸ ืึธืŸ ืึท ืกื˜ืจื•ืงื˜ื•ืจ ื˜ืขืงืข ื ื™ืฆืŸ ื‘ื™ื™ื˜-ื‘ื™ื™-ื‘ื™ื˜ืข ืึทื ืึทืœื™ืกื™ืก ืคื•ืŸ ื“ื™ ibd ื˜ืขืงืข, ืขืก ืงื•ืงื˜ ื•ื•ื™ ื“ืึธืก ืื™ื– ื•ื•ืึธืก ืื™ืจ ื“ืึทืจืคึฟืŸ. ื“ืขืจืฆื•, ืึทื–ืึท ืึท ืกื™ืงื•ื•ืึทื ืก ืื™ื– ื ื™ืฉื˜ ืจื™ืคึผื™ื˜ื™ื“ ืฆื•ื•ื™ื™ ืžืึธืœ ืคึผืขืจ ืฉื•ืจื”.

ื ื™ืฆืŸ ืึท ืจืขื’ื•ืœืขืจ ืื•ื™ืกื“ืจื•ืง, ืžื™ืจ ื’ืขืคึฟื™ื ืขืŸ ื“ื™ ื ื™ื™ื˜ื™ืง ื“ืึทื˜ืŸ:

fined = re.findall(r'128, d*, d*, d*, 128, d*, d*, d*, 153, 1[6,5,4,3]d, d*, d*, d*, 153, 1[6,5,4,3]d, d*, d*, d*', int_array)

ื‘ื™ื˜ืข ื˜ืึธืŸ ืึทื– ื•ื•ืขืŸ ืื™ืจ ื–ื•ื›ื˜ ืžื™ื˜ ื“ืขื ืื•ื™ืกื“ืจื•ืง, ืขืก ื•ื•ืขื˜ ื ื™ื˜ ื–ื™ื™ืŸ ืžืขื’ืœืขืš ืฆื• ื‘ืึทืฉื˜ื™ืžืขืŸ NULL ื•ื•ืึทืœื•ืขืก ืื™ืŸ ื“ื™ ืคืืจืœืื ื’ื˜ ืคืขืœื“ืขืจ, ืึธื‘ืขืจ ืื™ืŸ ืžื™ื™ืŸ ืคืึทืœ ื“ืึธืก ืื™ื– ื ื™ืฉื˜ ืงืจื™ื˜ื™ืฉ. ื“ืขืจื ืึธืš ืžื™ืจ ื’ื™ื™ืŸ ื“ื•ืจืš ื•ื•ืึธืก ืžื™ืจ ื’ืขืคึฟื•ื ืขืŸ ืื™ืŸ ืึท ืฉืœื™ื™ืฃ. ืฉืจื™ืคื˜:

result = []
for val in fined:
    pre_result = []
    bd_int  = re.findall(r"128, d*, d*, d*", val)
    bd_date= re.findall(r"(153, 1[6,5,4,3]d, d*, d*, d*)", val)
    for it in bd_int:
        pre_result.append(find_int(bd_int[it]))
    for bd in bd_date:
        pre_result.append(find_data_time(bd))
    result.append(pre_result)

ืึทืงื˜ื•ืึทืœืœื™, ื“ืึธืก ืื™ื– ืึทืœืข, ื“ื™ ื“ืึทื˜ืŸ ืคึฟื•ืŸ ื“ืขืจ ืจืขื–ื•ืœื˜ืึทื˜ ืžืขื ื’ืข ื–ืขื ืขืŸ ื“ื™ ื“ืึทื˜ืŸ ื•ื•ืึธืก ืžื™ืจ ื“ืึทืจืคึฟืŸ. ###ืคึผืก.###
ืื™ืš ืคึฟืึทืจืฉื˜ื™ื™ืŸ ืึทื– ื“ืขืจ ืื•ืคึฟืŸ ืื™ื– ื ื™ืฉื˜ ืคึผืึทืกื™ืง ืคึฟืึทืจ ืึทืœืขืžืขืŸ, ืึธื‘ืขืจ ื“ืขืจ ื”ื•ื™ืคึผื˜ ืฆื™ืœ ืคื•ืŸ ื“ืขื ืึทืจื˜ื™ืงืœ ืื™ื– ืฆื• ืคึผื™ื ื˜ืœืขืš ืงืึทืžืฃ ืืœื ื•ื•ื™ ืกืึธืœื•ื•ืข ืึทืœืข ื“ื™ื™ืŸ ืคึผืจืึธื‘ืœืขืžืก. ืื™ืš ื˜ืจืึทื›ื˜ืŸ ื“ื™ ืžืขืจืกื˜ ืจื™ื›ื˜ื™ืง ืœื™ื™ื–ื•ื ื’ ื•ื•ืึธืœื˜ ื–ื™ื™ืŸ ืฆื• ืึธื ื”ื™ื™ื‘ืŸ ืœืขืจื ืขืŸ ื“ื™ ืžืงื•ืจ ืงืึธื“ ื–ื™ืš mariadb, ืึธื‘ืขืจ ืจืขื›ื˜ ืฆื• ืœื™ืžื™ื˜ืขื“ ืฆื™ื™ื˜, ื“ื™ ืงืจืึทื ื˜ ืื•ืคึฟืŸ ืกื™ืžื“ ืฆื• ื–ื™ื™ืŸ ื“ื™ ืคืึทืกื˜ืึทืกื˜.

ืื™ืŸ ืขื˜ืœืขื›ืข ืงืึทืกืขืก, ื ืึธืš ืึทื ืึทืœื™ื™ื–ื™ื ื’ ื“ื™ ื˜ืขืงืข, ืื™ืจ ื•ื•ืขื˜ ืงืขื ืขืŸ ืฆื• ื‘ืึทืฉืœื™ืกืŸ ื“ื™ ื“ืขืจื ืขื ื˜ืขืจื  ืกื˜ืจื•ืงื˜ื•ืจ ืื•ืŸ ื•ืžืงืขืจืŸ ืขืก ืžื™ื˜ ืื™ื™ื ืขืจ ืคื•ืŸ ื“ื™ ื ืึธืจืžืึทืœ ืžืขื˜ื”ืึธื“ืก ืคื•ืŸ ื“ื™ ืœื™ื ืงืก ืื•ื™ื‘ืŸ. ื“ืึธืก ื•ื•ืขื˜ ื–ื™ื™ืŸ ืคื™ืœ ืžืขืจ ืจื™ื›ื˜ื™ืง ืื•ืŸ ืคืึทืจืฉืึทืคืŸ ื•ื•ื™ื™ื ื™ืงืขืจืข ืคืจืื‘ืœืขืžืขืŸ.

ืžืงื•ืจ: www.habr.com

ืœื™ื™ื’ืŸ ืึท ื‘ืึทืžืขืจืงื•ื ื’