Eugen Polanski and Roberto Firmino gave Hoffenheim the lead after the first half ended goalless.
Three goals in eight minutes then saw Mainz turn the game around with Eric Maxim Choupo-Moting, Benjamin Saller and Shinji Okazaki all on target. Okazaki struck again in stoppage time to seal Mainz's win and take them up to fifth in the table.
Hoffenheim had the chance to take the lead in the third minute when Kevin Volland was fouled inside the penalty area by Nikolce Noveski.
Sejad Salihovic, who had only missed two penalties in 27 attempts, stepped up to the spot but scooped the ball over the crossbar.
It did not seem to matter too much when Polanski sent in an unstoppable volley to put Hoffenheim in front early in the second half.
When Roberto Firmino added a second with a neat lobbed effort from the edge of the penalty area, it seemed like one of the league's top goalscorers were going to take the points.
However, Hoffenheim do have the leakiest defence in the division, and it was breached three times in the space of eight minutes.
Choupo-Moting was first to beat Koen Casteels to give the visitors hope before a bizarre second brought them level.
Niklas Sule tried to clear the ball but instead struck it straight against Saller, who was looking the other way as the ball looped over Casteels and in.
Hoffenheim were still trying to come to terms with that goal when Okazaki met Choupo-Moting's cross to head Mainz in front as Gisdol looked on with a forlorn expression.
The Japanese striker then completed the victory with the last kick of the game, lifting Mainz into a Europa League qualifying berth, and only three points behind a place in the Champions League next season.